473,692 Members | 1,947 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

PHP, mysql, and escaping characters

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

Feb 7 '07 #1
3 5383
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?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
Taras,

Just use mysql_real_esca pe_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*******@attgl obal.net
=============== ===
Feb 7 '07 #2
Jerry Stuckle wrote:
Just use mysql_real_esca pe_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_esca pe:
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!
Feb 7 '07 #3
Just use mysql_real_esca pe_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_esca pe_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_chara cter_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.
jstuck...@attgl obal.net
=============== ===

Feb 8 '07 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
17350
by: Martin Lucas-Smith | last post by:
Can anyone point me to a regular expression in PHP which could be used to check that a proposed (My)SQL database/table/column name is valid, i.e. shouldn't result in an SQL error when created? The user of my (hopefully to be opensourced) program has the ability to create database/table/column names on the fly. I'm aware of obvious characters such as ., , things like >, etc., which won't work, but haven't been able to source a...
1
1976
by: leegold2 | last post by:
// This statement below inserting one field works: // mysql_query("INSERT INTO page (page_url) VALUES (\"$url_field\")"); But I wanted to insert into two fields so I was trying all sorts of escaping. See below...there must be an easier way?! I also cite the syntax error - Thanks very much. mysql_query("INSERT INTO page (page_url, title) VALUES ( \"$url_insert . "\", "." \"$title_field "." "\")");
0
1959
by: Lisa | last post by:
I need to apply the HTML formatting tags and the French accented characters in a XML document. The XML is generated from a database that has HTML tags and French accented characters in the records. I have specified <xsl:output method="html"/> and encoding="iso-8859-1". When I apply the xsl:value-of and set the disable-output-escaping to "yes", the HTML formatting tags are displayed correctly, but the French accented characters are...
8
5271
by: Bill Eldridge | last post by:
I'm trying to grab a document off the Web and toss it into a MySQL database, but I keep running into the various encoding problems with Unicode (that aren't a problem for me with GB2312, BIG 5, etc.) What I'd like is something as simple as: CREATE TABLE junk (junklet VARCHAR(2500) CHARACTER SET UTF8)); import MySQLdb, re,urllib
4
1562
by: Archibald | last post by:
I want to improve security of a multiplayer online game written in php and mysql. Because I'm new to this stuff I would appreciate some tips. If you have time look here http://web.rulex.net/archi/Medieval_Lords/ and check what are the main problems (please don't hack it more than needed because there is a test game taking place there, just to say what is wrong without crashing it). I think it will need: 1) create a system of email...
4
5260
by: Ewok | last post by:
let me just say. it's not by choice but im dealing with a .net web app (top down approach with VB and a MySQL database) sigh..... Anyhow, I've just about got all the kinks worked out but I am having trouble preserving data as it gets entered into the database. Primarily, quotes and special characters. Spcifically, I noticed it stripped out some double quotes and a "Registered" symbol &reg; (not the ascii but the actual character"
9
2111
by: Harold Crump | last post by:
Greetings, I have a fairly vanilla PHP web application that stores and retrieves data in a MySQL database. Users will be adding a lot of special characters such as single and double quotes, accented French characters, etc. I want to eliminate any potential for XSS or SQL injection attacks. My question - is it enough to pass all user input through the
11
11089
by: macca | last post by:
Hi, What should I be using for general MySQL database access? I've been using the traditional mysql extension for ages, but I'm trying to update my style to a more OOP paradigm. I've used PDO briefly but I've not used the mysqli extension yet. I've read a bit about it though, seems good and more OOP orientated (for the most part). But PDO seems more generic and transferable.
7
4112
by: wannymahoots | last post by:
optparse seems to be escaping control characters that I pass as arguments on the command line. Is this a bug? Am I missing something? Can this be prevented, or worked around? This behaviour doesn't occur with non-control characters. For example, if this program (called test.py): from optparse import OptionParser parser = OptionParser() parser.add_option("-d", dest="delimiter", action="store")
0
8603
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, weíll explore What is ONU, What Is Router, ONU & Routerís main usage, and What is the difference between ONU and Router. Letís take a closer look ! Part I. Meaning of...
0
8538
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9083
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
8960
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
8800
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
4323
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
2972
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2237
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
1957
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.