Single Quotes in MySQL

Anon

When trying to insert data from a html form into a MySQL database using PHP any Single Quotes get read by MySQl and MySQL returns an error. Is the a way around this so that users posting to data into text field can use single quotes?

Anon

Use addslashes() (on the way in) and stripslashes() (on the way out) on the data.

rs232

But I thought addslashes would change ' to be \' won't MySQL still see ' and try to process it as the end of that field and then expect a , and another ' to designateanother field.

Ken wrote:

Use addslashes() (on the way in) and stripslashes() (on the way out) on the data.

Anon

Steven

Thanks for bringing this up. I was actually coming here to ask the same question.

I am a non-programmer who is enjoying the challange of getting php and mysql to work together. I ran into this problem and temporarily solved it by (with a programmer friend's help)

$title = ereg_replace("'", "''", $title);
echo "$title ";

This adds another apostrophe and fools the database into allowing the data.

My problem now is that when I go into the database to edit a record it again disallows the entry when I attempt to save the record and I must manually insert an extra apostrophe for each instance of "'" Ycchh. I was hoping some old hands here might have some good ideas about a more elegant and effective workaround.

Many thanks for any assistance.

Anon

Yes Tom is right. addslashes() and friends are NOT the right ways to manipulate the quote character in SQL. Please stop propagating the erroneous use of this Proprietary extension.

The proper ANSI SQL way to have quotes in SQL is to escape ' with ''. E.g.:

insert into t values ('a''b''''c''d')

.. would put the string a'b''c'd into the database.

Anon

you can also simply use the gpc_magicquotes configuration arribute instead of converting the form-data manually.
mysql usually accepts Strings like
'12\'\' Mon'.
if you need ansi-sql quotes (' -> '') you can set the gpc_magicquotes_sybase attribute.
btw in php-standard-installation, the gpb_magicquotes attrib is set.

rs232

Gerald wrote:

Yes Tom is right. addslashes() and friends are NOT the right ways to manipulate the quote character in SQL. Please stop propagating the erroneous use of this Proprietary extension.

The proper ANSI SQL way to have quotes in SQL is to escape ' with ''. E.g.:

insert into t values ('a''b''''c''d')

.. would put the string a'b''c'd into the database.

I don't want my users to have to do escape their quotes them self. Should I use ereg_replace ("'","/"'/"", $myformfield);

for each field in the form before inserting in into the database.

BTW my webserver seems to have magic_quotes_gpc turned off. I developed most of the site on a Linux box and apperently had it turned on by default because I didn't hav e this problem when I developed the site.