|
|
Posted by Jerry Stuckle on 02/07/07 16:25
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/en/string-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.
jstucklex@attglobal.net
==================
Navigation:
[Reply to this message]
|