PHP, mysql, and escaping characters

[Taras_96]

Hi everyone,

I'm having a bit of trouble understanding the purpose of escaping
nulls, and the use of addcslashes.

Firstly, the manual states that:

"Strictly speaking, MySQL requires only that backslash and the quote
character used to quote the string in the query be escaped. This
function quotes the other characters to make them easier to read in
log files"

While http://dev.mysql.com/doc/refman/5.0/...ng-syntax.html shows
that NULLs must be escaped as well as quotes and backslashes (if
inserting into BLOBs), which seems in contradiction to the statement
above.

In addition to the qutoes, backslashes, and nulls, real escape string
also escapes "\n, \r, and \x1a.

Meanwhile, the article at http://www.zend.com/zend/trick/tricks-
sept-2001.php?article=tricks-sept-2001&kind=tr&id=12886&open=1&anc=0

states that:

"Inserting a large value into a BLOB column is no different than
inserting any other character data. However, the image likely includes
a few characters that have special meaning in SQL or to the MySQL
driver. The addslashes function inserts backslashes before characters
with special meanings in SQL, such as single-quotes.

I used the addcslashes function to replace NUL characters with a \0
code because MySQL treats this character as the end of a string. This
is normal behavior for the C language, but not PHP. Otherwise, loading
the image into a variable would have required more steps."

I conducted an experiment using mysql 5.1 and tried inserting some
binary strings/data into a BLOB field. I tried no escaping, escaping
using addcslashes($string,"\0"), and mysql_real_escape_string, wrote
the escaped string out to file, and then inserted the data into the
database using the escaped string in an insert query.

The original data was as follows:

00 61 00 62 00 63 00 64

This was not escaped, and used in the insert query as is. Surprisingly
(because of what I had read previously), the data was inserted without
any complaints from mysql and appeared in the DB correctly. Why has it
worked, when the SQL manual and the zend website said that NULLs MUST
be escaped??

Next I tried the addcslashes:

The string that was inserted into the query was (ie: the result of
calling addcslashes($string,"\0"))
5C 30 30 30 61 5C 30 30 30 62 5C 30 30 30 63 5C 30 30 30 64

Whereas the data inserted into the database was:
00 30 30 61 00 30 30 62 00 30 30 63 00 30 30 64

For some reason addcslashes has, for every byte in the original data,
replaced it with a backslash (x5C) followed by three spaces (x30).
This results in the mangled data appearing in the database. I'm not
quite sure what the guy from zend was doing, or what he means by "This
is normal behavior for the C language, but not PHP. Otherwise, loading
the image into a variable would have required more steps."???

Finally, I tried escaping the data with mysql_real_escape_strings

The string that was inserted into the query was:
5C 30 61 5C 30 62 5C 30 63 5C 30 64

Which is what I expected addcslashes to give..

The data was inserted into the DB correctly.

So this is working as I expected, but why even bother escaping the
nulls if, as shown by the first experiment, it doesn't seem to be
needed?

Having said this, why would you want to escape the other characters:
\n, \r, and \x1a??

Thanks

Taras

[Jerry Stuckle]

Taras_96 wrote:

Hi everyone,

I'm having a bit of trouble understanding the purpose of escaping
nulls, and the use of addcslashes.

Firstly, the manual states that:

"Strictly speaking, MySQL requires only that backslash and the quote
character used to quote the string in the query be escaped. This
function quotes the other characters to make them easier to read in
log files"

While http://dev.mysql.com/doc/refman/5.0/...ng-syntax.html shows
that NULLs must be escaped as well as quotes and backslashes (if
inserting into BLOBs), which seems in contradiction to the statement
above.

In addition to the qutoes, backslashes, and nulls, real escape string
also escapes "\n, \r, and \x1a.

Meanwhile, the article at http://www.zend.com/zend/trick/tricks-
sept-2001.php?article=tricks-sept-2001&kind=tr&id=12886&open=1&anc=0

states that:

"Inserting a large value into a BLOB column is no different than
inserting any other character data. However, the image likely includes
a few characters that have special meaning in SQL or to the MySQL
driver. The addslashes function inserts backslashes before characters
with special meanings in SQL, such as single-quotes.

I used the addcslashes function to replace NUL characters with a \0
code because MySQL treats this character as the end of a string. This
is normal behavior for the C language, but not PHP. Otherwise, loading
the image into a variable would have required more steps."

I conducted an experiment using mysql 5.1 and tried inserting some
binary strings/data into a BLOB field. I tried no escaping, escaping
using addcslashes($string,"\0"), and mysql_real_escape_string, wrote
the escaped string out to file, and then inserted the data into the
database using the escaped string in an insert query.

The original data was as follows:

00 61 00 62 00 63 00 64

This was not escaped, and used in the insert query as is. Surprisingly
(because of what I had read previously), the data was inserted without
any complaints from mysql and appeared in the DB correctly. Why has it
worked, when the SQL manual and the zend website said that NULLs MUST
be escaped??

Next I tried the addcslashes:

The string that was inserted into the query was (ie: the result of
calling addcslashes($string,"\0"))
5C 30 30 30 61 5C 30 30 30 62 5C 30 30 30 63 5C 30 30 30 64

Whereas the data inserted into the database was:
00 30 30 61 00 30 30 62 00 30 30 63 00 30 30 64

For some reason addcslashes has, for every byte in the original data,
replaced it with a backslash (x5C) followed by three spaces (x30).
This results in the mangled data appearing in the database. I'm not
quite sure what the guy from zend was doing, or what he means by "This
is normal behavior for the C language, but not PHP. Otherwise, loading
the image into a variable would have required more steps."???

Finally, I tried escaping the data with mysql_real_escape_strings

The string that was inserted into the query was:
5C 30 61 5C 30 62 5C 30 63 5C 30 64

Which is what I expected addcslashes to give..

The data was inserted into the DB correctly.

So this is working as I expected, but why even bother escaping the
nulls if, as shown by the first experiment, it doesn't seem to be
needed?

Having said this, why would you want to escape the other characters:
\n, \r, and \x1a??

Thanks

Taras

Taras,

Just use mysql_real_escape_string(). It's a mysql function which is
made to escape the necessary characters. And it has the added advantage
that it is sensitive to the character set sensitive, so if you ever use
a non-latin1 charset the chars will be handled correctly.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

[Toby A Inkster]

Jerry Stuckle wrote:

Just use mysql_real_escape_string(). It's a mysql function which is
made to escape the necessary characters. And it has the added advantage
that it is sensitive to the character set sensitive, so if you ever use
a non-latin1 charset the chars will be handled correctly.

Though make sure you're using a recent version of MySQL, as older versions
(anything earlier than 4.1.20, plus 5.0-5.0.21) included this bug in
mysql_real_escape:
http://cve.mitre.org/cgi-bin/cvename...=CVE-2006-2753

For better database security, use prepare/execute.

--
Toby A Inkster BSc (Hons) ARCS
Contact Me ~ http://tobyinkster.co.uk/contact
Geek of ~ HTML/CSS/Javascript/SQL/Perl/PHP/Python*/Apache/Linux

* = I'm getting there!

[Taras_96]

Just use mysql_real_escape_string(). It's a mysql function which is

made to escape the necessary characters. And it has the added advantage
that it is sensitive to the character set sensitive, so if you ever use
a non-latin1 charset the chars will be handled correctly.

I tried that in the experiment (and currently do it in all of my
production code). However, what I don't understand is why using no
escaping at all and mysql_real_escape_string yields the same results.

Also, I read on the manual that you need to set the character set by
using the function 24.2.3.61. mysql_set_character_set(). Is this
correct? Why doesn't SET NAMES just set the required variable as well?

>
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
[e-mail]
==================