getting ' character into a mysql database

[nat21985]

Hello im having trouble getting ' character into a mysql database.
Basically im using a DOM parser to parse an XML document and the put what it reads in into a mysql database. Everything works fine until the java reads in anything with a ' in it.
For example the words can't and don't etc.
Can anyone help with this?
I would greatly appreciate it if you could.
Thanks
Natalie

[coopster]

Welcome to WebmasterWorld, Natalie.

Most relational database management systems (RDBMS) use single quotation or double quotation marks surrounding certain column type values in order to manipulate that column's value, particularly string values. For example, in order to create a new row in your table for a person with the last name of

Johnson

you might see something like this:

INSERT INTO mytable (lastname) VALUE ('Johnson');

Great, works fine. But what if the last name was

O'Reilly

? Well, that's when you need to escape the column's value with whatever the RDBMS escape character is. With MySQL you will often see the use of the backslash as it's escape character. So, you could do something like:

INSERT INTO mytable (lastname) VALUE ('O\'Reilly');

Most programming languages have some sort of function to make this process easier for you. For example, PHP uses a function called mysql_real_escape_string() [php.net].

MySQL Resource:
[dev.mysql.com...]

[wheelie34]

I had the same problem this week! try addslashes() to the variable that causes the problem

[txbakers]

You can also use the double, single quote to escape the single quote as follows:

insert into table (name) values ('o''reilly')

I do this with an ASP/reg exp function:

var sally = String(Request("name")).replace(/'/g,"''")