PDO FIlter

kterry

Does anyone know if PDO auto filters data on the fly?

laserlight

No, it does not. You should use prepared statements and escape functions instead.

kterry

I actually meant while using prepared statements. I noticed if you use an old version of mysql, it will keep the / in front of single quotes, but later versions of mysql will remove them as the data enters the database. Which leads me to believe that PDO is escaping the data on the fly.

laserlight

kterry wrote:
I actually meant while using prepared statements. (...) Which leads me to believe that PDO is escaping the data on the fly.

Ah, then yes, with prepared statements the bound parameters/values will be correctly escaped automatically.

kterry wrote:
I noticed if you use an old version of mysql, it will keep the / in front of single quotes, but later versions of mysql will remove them as the data enters the database.

I think you mean '\' rather than '/'. In any case, when the MySQL extension is correctly used, the data in the database should not be in escaped form. It is possible that you observed the effect of escaping twice: once due to magic_quotes_gpc being turned on, then another time when using mysql_real_escape_string(). If you have magic_quotes_gpc turned on, turn it off, otherwise you would find the same effect with PDO and prepared statements unless you use stripslashes() first.

kterry

laserlight;10907135 wrote:
Ah, then yes, with prepared statements the bound parameters/values will be correctly escaped automatically.

I think you mean '\' rather than '/'. In any case, when the MySQL extension is correctly used, the data in the database should not be in escaped form. It is possible that you observed the effect of escaping twice: once due to magic_quotes_gpc being turned on, then another time when using mysql_real_escape_string(). If you have magic_quotes_gpc turned on, turn it off, otherwise you would find the same effect with PDO and prepared statements unless you use stripslashes() first.

One would think that it was escaped twice, but with mysqld versions 5.0.x, I completely rendered my filter object useless and ran raw data through a PDO prepared statement, and the \es were still there. mysqld 5.1.x does not display the same results.

Give it a go and see for yourself 🙂

kterry

kterry;10907137 wrote:
One would think that it was escaped twice, but with mysqld versions 5.0.x, I completely rendered my filter object useless and ran raw data through a PDO prepared statement, and the \es were still there. mysqld 5.1.x does not display the same results.

Give it a go and see for yourself 🙂

Come to think of it, the double escaping might be caused by magic_quotes and PDO prepared statements escaping automatically. Though it's still odd that a newer versions of mysql actually remove the \

laserlight

kterry wrote:
One would think that it was escaped twice, but with mysqld versions 5.0.x, I completely rendered my filter object useless and ran raw data through a PDO prepared statement, and the \es were still there.

Provide the smallest and simplest runnable script that demonstrates the problem.

kterry

laserlight;10907139 wrote:
Provide the smallest and simplest runnable script that demonstrates the problem.

There actually isn't a problem. Using an updated version of mysql will remove slashes. However give me sometime to get other things done and I will cook up an example of what I mean. You will need 5.0.x and 5.1.x versions of mysqld installed to test. All you really need to do is send raw data through a PDO prepared statement to both databases and look at the data stored.

laserlight

kterry wrote:
There actually isn't a problem. Using an updated version of mysql will remove slashes.

Yeah, no problem for you anymore, but you did point out that there was a problem previously, and it might be relevant information for someone else 🙂

kterry wrote:
All you really need to do is send raw data through a PDO prepared statement to both databases and look at the data stored.

Yes. I would like an example that demonstrates the problem for you though, since it makes it more certain that we are looking at the same thing. There are, after all, many factors involved, not only in the version of MySQL, but possibly also in the version of PHP and hence the PDO driver for MySQL, along with differences in configuration.

kterry

laserlight;10907144 wrote:
Yeah, no problem for you anymore, but you did point out that there was a problem previously, and it might be relevant information for someone else 🙂

Yes. I would like an example that demonstrates the problem for you though, since it makes it more certain that we are looking at the same thing. There are, after all, many factors involved, not only in the version of MySQL, but possibly also in the version of PHP and hence the PDO driver for MySQL, along with differences in configuration.

I will provide an example.

kterry

kterry;10907145 wrote:
I will provide an example.

Alright, I installed debian on a virtual machine, then installed mysql 5.0.77 and could not replicate it. I did some searching around on the internet and found someone who had the exact same problem. http://www.criticalsecurity.net/index.php?showtopic=29768

Below is code example.

<?php

try{
	$db = new PDO('mysql:host=localhost;dbname=test;', 'USERNAME', 'PASSWORD');

$sql = "INSERT INTO est (test) VALUES (:test)";

$statement = $db->prepare($sql);
$test = "raw data with ' and more ' and stuff";
$statement->bindParam(':test', $test);
$statement->execute();
} catch(PDOException $e){
	echo $e->getMessage();
}

Here is the schema for the table.

CREATE TABLE `test` (
  `test` text character set latin1 NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Maybe someone running mysqld earlier than 5.0.77 can give this a run and see if it's just patched in that version.

Thanks!

bradgrafelman

I would also suggest adding this to the PHP code:

echo "magic_quotes_gpc is: " (get_magic_quotes_gpc() ? "enabled" : "disabled");

to further prove laserlight's theory.

EDIT: Didn't pay attention to the test code; if the data is hard-coded into the test code, and the raw data in MySQL is reported as being \', then yes, there is a problem somewhere. If it occurs only with incoming data, then the above test would apply.