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?articl e=tricks-sept-2001&kind=tr&id =12886&open=1&a nc=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($st ring,"\0"), and mysql_real_esca pe_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($st ring,"\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_esca pe_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
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?articl e=tricks-sept-2001&kind=tr&id =12886&open=1&a nc=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($st ring,"\0"), and mysql_real_esca pe_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($st ring,"\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_esca pe_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
Comment