SQL Statement and the "?" character...

flyingbono · #1 (**permalink**) 07-21-2007, 03:08 PM

Hi,

I'm trying to run the folowing query, without success. Evertime the "?" in the query is replaced (quoted) by the "'" character and a get a wrong record value in database :

PHP Code:


			
$sql = "UPDATE table SET col = 'Are you fine?' WHERE 1";

$db -> query( $sql );

Col contains : Are you fine '

Any idea ???

SpotSec · #2 (**permalink**) 07-22-2007, 12:25 AM

you do notice that your using single quotes that aren't escaped?

flyingbono · #3 (**permalink**) 07-22-2007, 10:04 AM

Quote:

Originally Posted by SpotSec

you do notice that your using single quotes that aren't escaped?

No, I say that I dont found any option to configure the PDO:MySQL -> prepare( $sql ) to DO NOT PARSE my query.

Anyway, the PDO library parse the query and interprets the quotation mark character as a bind parameter. Also, its not everytime the case, you may have database contents containing the "?" character !

Try to execute the folowing code :

PHP Code:


			
$params = array(

    'host'           => '127.0.0.1',

    'username'   => 'webuser',

    'password'   => 'xxxxxxxx',

    'dbname'     => 'test');

$db = Zend_Db::factory('Pdo_Mysql', $params);



$db -> query("UPDATE table SET col = 'Are you fine ?' WHERE 1");

How to do to have the correct string (Are you fine ?) in database ? The quotation mark is systematically interpreted and replaced with a single quote !

Thanks in advance for your help. I hope my english is no so poor !

letssurf · #4 (**permalink**) 07-25-2007, 07:57 AM

That's becuase you should be escaping your values. e.g.
$db->quoteInto('UPDATE table SET col = ? WHERE 1', 'Are you fine ?'); or
$db->query('UPDATE table SET col = '.$db->quote('Are you fine ?').' WHERE 1');
This prevents sql injection attacks, also ensures that any special chars are escaped for use with the database you are currently using, plus allows the sql produced to work in different db servers (e.g. you currrent sql wouldn't work in postgresql)

flyingbono · #5 (**permalink**) 07-25-2007, 08:39 AM

Thanks for your help, letssurf.
You're right, next time, I will read the manual with more attention !

So many works to make applications secured...

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode