add a note
User Contributed Notes
mysql_escape_string
homer400 at yahoo dot NOSPAM dot com
04-Sep-2003 11:10
Make a class that encapsulates all db access that includes string escaping.
Currently I am using this type of approach:
<?
$query =
GetDataQuery("INSERT INTO #articles (CategoryID, UserID, CreatedOn,
UpdatedOn, Title, Description, ArticleText) VALUES
(?,?,NOW(),NOW(),?,?,?)");
$query->setParams($CategoryID,
$UserID, $Article[Title], $Article[Description],
$ArticleArray[UnparsedArticle]);
$query->Execute();
?>
The
great thing about it is that I can change my table prefix very easily, all
my parameters are escaped, the class doesn't rely on Default connections
handles, and mysql_select_db is used on every call. If you are worried
about doubled slashes saved in your db, you can call
mysql_real_escape_string(stripslashes($string), $this->linkDatabase) in
your execute.
mathieum at sports dot fr
04-Jul-2003 06:02
The php mysql_db_query function allow only one querry at a time, thus it is
not possible to execute more than one query by passing ";" in
some form field.
rdsteed_at_bellsouth_dot_net
13-Jun-2003 05:50
From the MYSQL manual:
The string in from is encoded to an escaped
SQL string, taking into account the current character set of the
connection. The result is placed in to and a terminating null byte is
appended. Characters encoded are NUL (ASCII 0), `\n', `\r', `\', `'',
`"', and Control-Z (see section 6.1.1 Literals: How to Write Strings
and Numbers). (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.)
mike at vbmysql dot com
10-Apr-2003 04:44
As for hackers not being able to drop tables because of the one statement
at a time execution, that is a MySQL limitation, not a limitation of PHP.
Be warned; this is being fixed very soon, so be sure to add
mysql_escape_string before you upgrade to a MySQL 4.x databse.
Morat
08-Apr-2003 08:41
My approach to the slashes issue is to have a function which is called at
the beginning of ever page which checks whether magic_quotes_gpc is enabled
and if so calls stripslashes on all the contents of $_GET, $_POST and
$_COOKIE. It also calls htmlspecialchars for each value, but this might not
be to everyone's taste.
I then do all my database access through a
class that encapsulates connecting to and querying the database, so my db
access code is centralised. The Db->query($queryString, $database)
function of that class always calls mysql_escape_string on the query just
before it sends it to the database.
With this approach I always know
that data I use in my code is unescaped, but will be properly escaped
before being sent to mysql.
sp4m_only at hotmail dot com
02-Apr-2003 09:07
I found out another very curious thing about both AddSlashes() and
mysql_escape_string(). It seems that for mysql, the string '##' has some
special meaning. Someone told me it might be the beginning of a comment
line in the SQL statement. In any case, there is only ONE #-char inserted
into the database, instead of two. The two escape functions DO NOT escape
this two-char-sequence. So if you want to insert an image in the database
and be SURE the image is not f#ck#d up, then do the escaping
yourself:
$filedata = str_replace('##', '\#\#',
mysql_escape_string($filedata));
$query = "insert into IMAGES
(IMAGE) values ($filedata)";
$result =
mysql_query($query);
I hope this will save you guys the ours that I
spent by searching for the bug in my own code...
wmethlie at anotherreason dot com
02-Apr-2003 05:48
keep in mind that if the magic_quotes_xxx option is set to true, then all
values containing single-quotes in a GET/POST/COOKIE query-string from a
posting form will be automatically escaped before the engine page even
loads.
thus if you use this function on already escaped values,
you will most likely end up with escape characters in the database entry as
literals - rendering searches for that entry useless unless you escape the
escaped characters in the search query! not very elegant...
p2pwrox at ndc dot co dot za
01-Apr-2003 02:06
Have any of you bothered to read the MySQL manual?
mysql_escape_string()
is used to "create a legal SQL string that you can use in a SQL
statement". It's primary use is to allow binary BLOB data to co-exist
within an ASCII SQL statement without confusing the SQL parser. For this
reason tokens such as " and ' are escaped so the parser doesn't think
you're ending the blob string prematurely. NUL is escaped because mysql is
written in C and uses C strings which use NUL as a terminator. Similar
explanations apply for the other escaped chars.
Protecting an SQL
string from abuse is NOT the primary reason for this function. It's just a
bonus!
phil at philkern dot de
28-Dec-2002 08:15
Since PHP 4.3.0 it seems that mysql_escape_string() is
deprecated.
Probably you should use mysql_real_escape_string() instead
of it.
brouwertje at hotmail dot com
26-Dec-2002 04:58
This function can also be used to add binary data like an image into a
table. Have fun.
saboteur at kolumbus dot fi
25-Dec-2002 09:10
You might want to consider using htmlspecialchars() with ENT_QUOTES set
instead of this function. It doesn't escape quotes but instead will turn
both single and double quotes into their harmless HTML entity counterparts,
which I have found to be more useful and convenient in my case.
php at zwily dot com
12-Dec-2002 01:47
Even if mysql_db_query executed more than 1 statement per call, it would be
seriously retarded to have your php scripts connecting as a user that has
permissions to drop databases. You should always use accounts with limited
permissions for web usage.
skennedy tpno org
31-Aug-2002 02:42
About magic_quotes_gpc and data scrubbing: It is usually the best practice
to have a common function called at the beginning of your scripts that
"normalizes" the data to what you expect.
For me, that's
getting rid of magic quotes effects on my incoming data if it's turned on,
or do nothing to if it's turned off. This may seem the long way around,
but my scripts work regardless of what the setting on the server are, or if
they change.
adamNO at SPAMjoygasmic dot com
23-Aug-2002 02:50
I've found that when trying to store binary data in a BLOB field, that you
must do a mysql_escape_string. I'm not sure why, but with
addslashes/stripslashes, the integrity of the data gets messed with, and
the binary data comes out of the database different than when it went in
david AT davidquintana DOT com
04-Aug-2002 05:11
If magic_quotes_gpc is set to "On" in php.ini and you use
mysql_escape_string, you will have extra '\' inserted, for example after
an apostrophy. In essance you are adding escapes twice.
Example:
mysql_escape_string("let's") equals "let\\\'s" when
magic_quotes_gpc is on.
mysql_escape_string("let's")
equals "let\'s" when magic_quotes_gpc is off.
08-Feb-2002 10:38
if you use mysql_result(0,1)
and 0,1 has a quote in the text it won't
escape the ' if you put it directly into a
sql query
example:
wrong
$sql = "INSERT INTO tblsubdocuments (text) VALUES ('".
mysql_result($subdocuments,$a,1))."' )";
example:
good
$sql = "INSERT INTO tblsubdocuments (text) VALUES ('".
mysql_escape_string(mysql_result($subdocuments,$a,1))."')";
if
you dont do this your F#*&%%%ked
mitja at doticni dot net
24-Oct-2001 02:55
If you're wondering what's the difference between mysql_escape_string() and
AddSlashes(), I found this from looking at the source code of MySQL 3.23.32
and PHP 4.0.6:
- mysql_escape_string calls MySQL's library
function of the same name, which prepends slashes to the following
characters: NUL (\x00), \n, \r, \, ', " and \x1a.
-
AddSlashes escapes NUL, ', " and \.
While mysql_escape_string
seems safer, my experience shows that escaping strings with AddSlashes
(which is also done automatically if magic_quotes_gpc is on) is sufficient,
so it seems you can pick whichever you wish.
php at filecast dot org
04-Oct-2001 07:11
Actually, a hacker couldn't drop tables like that -- the PHP mysql_query
only supports one command per line. Of course, you should still escape
everything coming from the outside world because maliciously modifying the
parameters of the intended command is still possible.
jeroen at php dot net
03-Oct-2001 06:33
Usage:
<?php
$name = mysql_escape_string( $name
);
$query = "SELECT * FROM adresses WHERE name='$name' AND
private='N'";
mysql_query($query);
?>
Without
mysql_escape_string a user could set name to "' OR 1=1 OR
''='"
effectively leading to the query:
SELECT * FROM
adresses WHERE name='' OR 1=1 OR ''='' AND private='N'
which will
give all adresses, including private ones.
Don't say people won't
find out, very ofter query errors are displayed to the user, and also this
is such a common mistake that hackers will simply GUESS things like this.
idiot at users dot sourceforge dot net
21-Aug-2001 06:31
The point of using mysql_escape_string() is so that malicious users can't
supply an evil string that causes mysql to do evil things. Imagine if
somebody typed in their username to your website as something like:
MrUser"; DROP DATABASE mysql
mysql_escape_string() defeats
this attack.
mysql_error
