Escaping ' in SQL 1

[jimoblak]

I've got a problematic query that is driving me nuts and it is too late in the day for me to think straight...

UPDATE table SET Comments = '$newcomment' WHERE DateID = '$today'

The query fails when there is an apostrophe or single quote in the $newcomment variable being passed through PHP.

example: 
$newcomment = "I'm unsuccessful"; //Fails 
$newcomment = "I am successful"; //Works

Is there a way to push this query through without messing with the contents of the $newcomment variable?


- - picklefish - -
Why is everyone in this forum responding to me as picklefish?

 

[swampBoogie]

Use ' as escape.

 

[jimoblak]

I'm lost with your reply. That is the subject of this thread. How do I escape it in the SQL so that it gets stored in the database in its original form?

- - picklefish - -
Why is everyone in this forum responding to me as picklefish?

 

[danomac]

You'll find this useful:

http://ca2.php.net/manual/en/function.mysql-escape-string.php

D

 

[swampBoogie]

The escape character in SQL strings is '.

insert into t values('mc''s bodega')

inserts the value

mc's bodega

in the one column in the table t.

 

[KarveR]

or \'

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[swampBoogie]

You should avoid using \ as an escape character as it is a Mysql extension and does not work in other DBMS.

 

[KarveR]

The MySQL functions mysql_escape_string() and mysql_real_eascape_string() automatically escape special characters with
The PHP functions are also IDENTICAL to the MySQL functions, and do exactly the same.

The point I believe you are trying to make, swampBoogie is that \ issued at command level is a command used by the mysql interpreter.

When \ is included within quoted text, and precedes a "special" character, it is taken in context and used as an escape character.

PHP::
$string='this \' will be escaped and entered into the table correctly';

mysql
update sometable set field='this is ok isn\'t it' where ...

if however in mysql you issue:
mysql> \?
you will get :
List of all MySQL commands:
(Commands must appear first on line and end with ';')

help (\h) Display this help.
? (\?) Synonym for `help'.
clear (\c) Clear command.
connect (\r) Reconnect to the server. Optional arguments are db and host.
edit (\e) Edit command with $EDITOR.
ego (\G) Send command to mysql server, display result vertically.
exit (\q) Exit mysql. Same as quit.
go (\g) Send command to mysql server.
nopager (\n) Disable pager, print to stdout.
notee (\t) Don't write into outfile.
pager (\P) Set PAGER [to_pager]. Print the query results via PAGER.
print (\p) Print current command.
prompt (\R) Change your mysql prompt.
quit (\q) Quit mysql.
rehash (\#) Rebuild completion hash.
source (\.) Execute a SQL script file. Takes a file name as an argument.
status (\s) Get status information from the server.
system (\!) Execute a system shell command.
tee (\T) Set outfile [to_outfile]. Append everything into given outfile.
use (\u) Use another database. Takes database name as argument.

Connection id: 2 (Can be used with mysqladmin kill)
which is what swampBoogie is intimating at.



______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[jimoblak]

Please excuse my absence in the discussion... I've been sweating bullets over this oddity.

The thing that confused me was that I had previously been able to make the SQL 'UPDATE' on another PHP page where the variable had quotes within its string. Now I am worried what bug allowed me to get away with that.

To address the problem that started this thread I simply added

$newcomment=addslashes($newcomment);

...in the PHP script before I applied the SQL UPDATE and now I'm fine.

- - picklefish - -
Why is everyone in this forum responding to me as picklefish?

 

[KarveR]

Jimoblak, this is possibly due to PHP's get_magic_quotes_gpc() or get_magic_quotes(), if set to on, you don't need to take any further action with your strings, otherwise you would need either addslashes(), mysql_escape_string() or mysql_real_escape_string().

other alternatives are available.

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[tviman]

I'm having a similar problem with variables where an apostrophe is part of the variable content, such as Joe's. I'm using a PERL script to add the data to the file - does this mean I have to check the contents of each variable and strip out apostrophe's, and other special characters?

There's always a better way. The fun is trying to find it!