About the Author

Chris Shiflett

Hi, I’m Chris: web craftsman, community leader, husband, father, and partner at Fictive Kin.


addslashes() Versus mysql_real_escape_string()

Last month, I discussed Google's XSS Vulnerability and provided an example that demonstrates it. I was hoping to highlight why character encoding consistency is important, but apparently the addslashes() versus mysql_real_escape_string() debate continues. Demonstrating Google's XSS vulnerability is pretty easy. Demonstrating an SQL injection attack that is immune to addslashes() is a bit more involved, but still pretty straightforward.

In GBK, 0xbf27 is not a valid multi-byte character, but 0xbf5c is. Interpreted as single-byte characters, 0xbf27 is 0xbf (¿) followed by 0x27 ('), and 0xbf5c is 0xbf (¿) followed by 0x5c (\).

How does this help? If I want to attempt an SQL injection attack against a MySQL database, having single quotes escaped with a backslash is a bummer. If you're using addslashes(), however, I'm in luck. All I need to do is inject something like 0xbf27, and addslashes() modifies this to become 0xbf5c27, a valid multi-byte character followed by a single quote. In other words, I can successfully inject a single quote despite your escaping. That's because 0xbf5c is interpreted as a single character, not two. Oops, there goes the backslash.

I'm going to use MySQL 5.0 and PHP's mysqli extension for this demonstration. If you want to try this yourself, make sure you're using GBK. I just changed /etc/my.cnf, but that's because I'm testing locally:

[client]
default-character-set=GBK

Create a table called users:

CREATE TABLE users (
    username VARCHAR(32) CHARACTER SET GBK,
    password VARCHAR(32) CHARACTER SET GBK,
    PRIMARY KEY (username)
);

The following script mimics a situation where only addslashes() (or magic_quotes_gpc) is used to escape the data being used in a query:

<?php
 
$mysql = array();
 
$db = mysqli_init();
$db->real_connect('localhost', 'myuser', 'mypass', 'mydb');
 
/* SQL Injection Example */
$_POST['username'] = chr(0xbf) .
                     chr(0x27) .
                     ' OR username = username /*';
$_POST['password'] = 'guess';
 
$mysql['username'] = addslashes($_POST['username']);
$mysql['password'] = addslashes($_POST['password']);
 
$sql = "SELECT *
        FROM   users
        WHERE  username = '{$mysql['username']}'
        AND    password = '{$mysql['password']}'";
 
$result = $db->query($sql);
 
if ($result->num_rows) {
    /* Success */
} else {
    /* Failure */
}
 
?>

Despite the use of addslashes(), I'm able to log in successfully without knowing a valid username or password. I can simply exploit the SQL injection vulnerability.

To avoid this type of vulnerability, use mysql_real_escape_string(), prepared statements, or any of the major database abstraction libraries.

This type of attack is possible with any character encoding where there is a valid multi-byte character that ends in 0x5c, because addslashes() can be tricked into creating a valid multi-byte character instead of escaping the single quote that follows. UTF-8 does not fit this description.

About this post

addslashes() Versus mysql_real_escape_string() was posted on Sat, 21 Jan 2006. If you liked it, follow me on Twitter or share:

73 comments

1.Andi Gutmans said:

My main advice to people is to always use prepared statements and then bind your parameters. Even if you are not planning to reuse the prepared statement, and won't get any performance benefit from doing so, it will prevent your apps from being attached using SQL injections because parameters are bound after the statement is compiled.

People just shouldn't be using anything else!

Sun, 22 Jan 2006 at 06:20:42 GMT Link


2.Christian Wenz said:

Andi, of course you are right, but not every database system enjoyes prepared statements support on PHP :-(

Sun, 22 Jan 2006 at 08:12:33 GMT Link


3.Ivo Jansch said:

Good post Chris; we can't stress enough how addslashes is *not* suitable for database escaping. In fact it's only useful for url escaping.

Sun, 22 Jan 2006 at 08:53:47 GMT Link


4.Chris Shiflett said:

I intended to mention bound parameters and give an example at the end, but I forgot. :-)

Sun, 22 Jan 2006 at 15:00:15 GMT Link


5.Ilia Alshanetsky said:

Not to downplay the vulnerability, but it is important to note that to make the exploit possible a multibyte character set needs to be used in MySQL. This is not the default configuration setting nor a common situation for most MySQL servers. So, while this exploit clearly demonstrates the need for the use of proper escaping function or better yet prepared statements, it is not something that would affect vast majority of LAMP systems.

Sun, 22 Jan 2006 at 17:03:08 GMT Link


6.Chris Shiflett said:

Ilia has written a great follow-up post:

http://ilia.ws/archives/103-mysql_r...Statements.html

He points out the additional risk associated with changing the character set via an SQL query:

SET CHARACTER SET 'GBK'

Doing so does not affect mysql_real_escape_string(), which will still use ISO-8859-1, just like addslashes().

Check out his full post for a clear explanation and example.

Sun, 22 Jan 2006 at 18:29:54 GMT Link


7.Peter Zaitsev said:

The problem with prepared statements at this point is - they are actually going to be slower if you're not going to repeat them. as they require more roundtrips to the server. Furthermore QueryCache is not going to be used which is quite helpful for many web applications.

Otherwise Prepared statements are great to use.

I wish PHP would have something like Java has - to be able to optimally use emulated prepared statements.

I know PEAR:DB has it but it is slow.

Tue, 24 Jan 2006 at 22:35:30 GMT Link


8.Christoph Thompson said:

What about PDO using prepared statements. Is that good to stop injections?

Wed, 01 Feb 2006 at 10:04:05 GMT Link


9.Chris Shiflett said:

Yes, using prepared statements (and bound parameters) offers the strongest protection.

Wed, 01 Feb 2006 at 13:10:28 GMT Link


10.GhostGambler said:

Just use mysql_set_character_set()

http://dev.mysql.com/doc/refman/5.0...racter-set.html

> This function works like the SET NAMES statement, but also sets the value of mysql->charset, and thus affects the character set used by mysql_real_escape_string()

Sat, 18 Mar 2006 at 16:03:42 GMT Link


11.GhostGambler said:

The function in php with mysqli is mysqli_set_charset()

http://www.php.net/manual/en/functi...set-charset.php

The "old" mysql does not support this function :\

Sat, 18 Mar 2006 at 16:29:13 GMT Link


12.Seek3r said:

Which character sets are vulnerable.

On a hosted site on godaddy I only see the following char sets available

latin1 big5 czech euc_kr gb2312 gbk latin1_de sjis tis620 ujis dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr greek win1250 croat cp1257 latin5

So gbk is not one of them. Are any of these others vulnerable?

Sun, 30 Apr 2006 at 02:20:44 GMT Link


13.Seek3r said:

doh!

It was in there

Sun, 30 Apr 2006 at 02:35:59 GMT Link


14.Yoyo said:

Hi,

I'm trying to mimic the example above but without success.

I'm connecting to my local MySQL Server using PHP, all that on Windows.

And I'm running the following code:

<?php

mysql_connect("localhost", "root", "");

mysql_query("SET NAMES gbk") or die(mysql_error());

$file = fopen("test.txt", "w");

fwrite($file, mysql_real_escape_string(chr(0xbf).chr(0x27)));

fclose($file);

?>

According to what I understood, this shouldn't be escaed as I'm setting the connection character set to gbk with the "SET NAMES" query, and yet, when opening my test text file where I'm printing my "escaped" string, I can see that the 0x27 char is escaped, which shouldn't be the case, should it?

Sat, 05 Aug 2006 at 17:26:51 GMT Link


15.Jim Rogers said:

Hi Chris,

some time ago I was first alerted to the threat of an SQL injection attack. Not having a lot of time on my hands to sit down and learn all about injection attacks, I came up with my own solution.

<br<br>

I convert text into its ASCII equivalent. So, for example, the text <i>someone's comments</i> is entered into the database as <i>736f6d656f6e65277320636f6d6d656e7473</i>.

<br<br>

The disadvantage is the fact that the data takes up twice as much space, but for my applications that's not really a problem.

<br<br>

The advantage is I don't have to worry about escaping characters.

<br<br>

I have a feeling this may also prevent injection attacks, but pehaps I have that wrong.

Wed, 16 Aug 2006 at 23:12:32 GMT Link


16.雨 said:

Yoyo writes:

I'm trying to mimic the example above but without success.

------------

Well - your test actualy is a good example of a _SUCCESSFUL_ injection attack.

The reason you didn't see it in your testfile is because you looked the file with wrong charset.

It is frightening to know that in the year 2006 developers still don't understand charsets.

joke: instead of fixing mysql_real_escape_string - how about a new function: mysql_working_real_escape_string or mysql_real_escape_string_with_less_bugs or mysql_real_escape_string_that_does_what_mysql_real_escape_string_was_supposed_to_do ?

Wed, 06 Sep 2006 at 05:13:39 GMT Link


17.html said:

The bug have been fixed?

Tue, 26 Sep 2006 at 08:06:51 GMT Link


18.Chris Shiflett said:

What bug?

Tue, 26 Sep 2006 at 13:48:19 GMT Link


19.Chris Shiflett said:

Perhaps you mean this:

http://dev.mysql.com/doc/refman/5.0...ews-5-0-22.html

Thu, 05 Oct 2006 at 22:58:22 GMT Link


20.Ed Finkler said:

"Andi, of course you are right, but not every database system enjoyes prepared statements support on PHP :-("

My understanding is that prepared statements are at least emulated in every PDO driver. I asked Wez Furlong whether the emulated prepared statements were "safe" from SQL injection at OSCON 2006, and he said they were. Obviously this is PHP5 only, but... perhaps another good reason to make the jump. 8)

Fri, 06 Oct 2006 at 17:16:57 GMT Link


21.Chris Shiflett said:

A few months after I posted this, it was discovered that this problem also afflicted PostgreSQL:

http://www.postgresql.org/docs/techdocs.50

Tue, 10 Oct 2006 at 15:40:11 GMT Link


22.TuxLives said:

But what about preventing script attacks such as javascript alerts? I put real_escape and it did not escape the double quotes which cause the alert to function when the page was viewed. It seems as though another validation run is needed to fend that off. Will the other validation, such as htmlentities() conflict with real_escape?

Tue, 17 Oct 2006 at 20:25:04 GMT Link


23.Chris Shiflett said:

Hi TuxLives,

It sounds like you might be lumping filtering (inspecting data to be sure it's valid and only allowing valid data) and escaping (preparing data for a different context) together.

This blog post doesn't really offer a good explanation of either, because it's simply digging into a common misconception about two escaping techniques for SQL.

In short, escaping data for the context of SQL only preserves it and helps to make sure that it doesn't interfere with the format of your query. It does not filter the data, nor does it help you to preserve data in the context of HTML, mitigating the risk of XSS.

There are other resources available on my web site that offer more information on these topics.

Hope that helps.

Tue, 17 Oct 2006 at 20:35:08 GMT Link


24.kray lapus said:

What's the equivalent of mysql_real_escape_string() function in PEAR?

Will it be ok to use PEAR DB's quoteSmart to escape output to a mysql db?

Sat, 11 Nov 2006 at 06:04:59 GMT Link


25.Bob said:

AWESOME!

Thanks for writing this out, I have changed all my scripts to use real_escape.

By the way, I should point out that I just benchmarked real_escape vs addslashes and it looks like RS is actually a little faster!

Imagine that!

Anyways, thanks again dude!

-Bob

Fri, 17 Nov 2006 at 13:41:26 GMT Link


26.nEUrOO said:

Note that you can also use escapeshellcmd for sanitizing the input values.

Thu, 14 Dec 2006 at 01:32:51 GMT Link


27.Chris Shiflett said:

The escapeshellcmd() function is for escaping shell commands, not data used in SQL queries. This discussion is about the latter.

Thu, 14 Dec 2006 at 03:36:43 GMT Link


28.Eivind Valderhaug said:

Hi, I've read several of your articles and have learned a lot from it. :-)

In this article though, I'm not sure what you mean by the following statement:

"""

If I want to attempt an SQL injection attack against a MySQL database, having single quotes escaped with a backslash is a bummer. If you're using addslashes(), however, I'm in luck.

"""

I read it as if I escape single quotes with a backslash, ie. \', then a SQL injection will fail. This is the same as what addslashes() does though, where a SQL injection will succeed in your example, so I would assume that you may have meant something like "having single quotes escaped with a single quote"? :-)

I am not an expert on this issue, but it seems to me that it's sufficient to escape single quotes with single quotes, ie. '' (2 single quotes) instead of \'.

Using your code example,

$mysql['username'] = str_replace(array('\\', '\''), array('\\\\', '\\\''), $_POST['username']);

$mysql['password'] = str_replace(array('\\', '\''), array('\\\\', '\\\''), $_POST['password']);

will make the injection attempt successful, whereas

$mysql['username'] = str_replace(array('\\', '\''), array('\\\\', '\'\''), $_POST['username']);

$mysql['password'] = str_replace(array('\\', '\''), array('\\\\', '\'\''), $_POST['password']);

will make the injection attempt fail.

Isn't the latter method (str_replace(array('\\', '\''), array('\\\\', '\'\''), $data)) always sufficient for preparing data for a mysql query where apostrophe is the enclosure character? If so, is there any reason why we should still rather be using mysql_real_escape_string()?

Tue, 19 Dec 2006 at 20:03:18 GMT Link


29.Arie said:

Nice article but the default character set for MySQL is Latin1, isn't it? Latin1 doesn't use multi-bytes characters so is it save to use addslashes() for a default MySQL install?

Thu, 28 Dec 2006 at 15:44:38 GMT Link


30.bongobongo said:

I read the comment from Eivind Valderhaug.

Is it not possible to do it the way he says?

Make sure all single-quotes are escaped.

If that is taken care of then it does not matter what kind of characters are inserted in the submitted string.

So my question is a follow up on his suggestion.

Is it that hard to escape all single-quotes submitted in a $_POST or $_GET variable?

And if that is doable, would not that be a lot easier as well as safer?

So then for MySQL we would have to find all occurences of single-quotes and replace those with backslash + single-quote.

For other databases like MS SQL, Firebird, Oracle, DB2 we could replace all occurences of single-quote with two single-quotes.

And "if doable" we did not have to use the addslashes() or mysql_real_escape_string() either.

Interested in comments on this and eventually why this is not a good idea?

Anyway... thanks for a nice article.

Tue, 09 Jan 2007 at 02:24:38 GMT Link


31.Chris Shiflett said:

As I demonstrated in this blog post, a single quote isn't always a single quote. Character encoding consistency is important.

Tue, 09 Jan 2007 at 02:40:26 GMT Link


32.bongobongo said:

I agree to that.

But many web applications are only ment to work for specific character sets.

Let say a web application is only to be usable with either ISO-8859-1 or UTF-8

Then, would it be hard to deal with the different ways to submit a single-quote for those particular charsets, and then to only escape the single-quotes e.g. using str_replace.

I'm definitely not an expert on this, but I'm still curious to know if it could be done.

Tue, 09 Jan 2007 at 12:21:20 GMT Link


33.Eivind Valderhaug said:

"As I demonstrated in this blog post, a single quote isn't always a single quote."

Whether it's part of a multi-byte sequence or not doesn't matter, I think*, as long as it's escaped using standard SQL doubling ('').

* As I wrote in my previous comment, replacing ' with '' would make the injection attempt in your blog post fail.

I have to admit that I'm not familiar with using multi-byte charsets, and I don't know if using this method of escaping will have any effect on the multi-byte character, ie. making it invalid, but it should in any case be safe from injections (as far as I can see anyway). If someone familiar with multi-byte charsets could explain if I'm right or wrong, I would be most thankful.

About mysql_real_escape_string, the following quote is taken from the manual (http://dev.mysql.com/doc/refman/5.0/en/mysql-real-escape-string.html):

"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."

On a similar note, if the MySQL mode NO_BACKSLASH_ESCAPES is set, then backslash is not considered a special character and should not be escaped.

Wed, 10 Jan 2007 at 15:25:51 GMT Link


34.Eivind Valderhaug said:

It does however seem a bit strange to me (considering that \ is an escape character in MySQL) that ¿\' should be interpreted as a multi-byte character (¿\) followed by ', while ¿'' would be interpreted as simply ¿ followed by ', and not a multi-byte character (¿') followed by ', which is how I understand the fact that the code example in my first comment would make your injection attempt fail.

I hope I got my assumptions right, and that I'm making at least some sense. ;-)

Wed, 10 Jan 2007 at 15:38:21 GMT Link


35.Chris Shiflett said:

Hi Eivind,

I think I can help. :-)

(For convenience, I'm going to put single-byte representations in parentheses. Keep in mind that this is not necessarily how these bytes are interpreted.)

0xbf (¿) is used in this example, because the attack is assuming:

1. Single quotes are being escaped with a backslash.

2. 0xbf5c27 (¿\) is a valid multi-byte character in whatever character encoding the database is using.

In other words, 0xbf (¿) isn't a magic byte that works in all cases. It's a specific one used in this particular attack, because when followed by 0x5c (\), it makes a valid multi-byte character, 0xbf5c27 (¿\).

Because 0xbf27 (¿') is not a valid multi-byte character, this particular example doesn't work if single quotes are being escaped with single quotes. Therefore, instead of 0xbf(¿), the attack would use a byte (or bytes) that can be followed by 0x27 (') to form a valid multi-byte character.

It's the exact same attack. Using single quotes to escape single quotes offers no protection. Again, a single quote isn't always a single quote, and that's really the focus of this particular post.

Hope that makes things clearer. :-)

Wed, 10 Jan 2007 at 16:07:36 GMT Link


36.Chris Shiflett said:

Arie, you're right that addslashes() can be sufficient when the default character set is Latin-1 (ISO 8859-1).

Hopefully this doesn't discourage you from using the right tool for the job. :-)

Wed, 10 Jan 2007 at 16:10:44 GMT Link


37.Eivind Valderhaug said:

Thanks Chris, that definitely made things clearer. I really should have read your blog post a bit more carefully, especially the parts that say "0xbf27 is not a valid multi-byte character" and "0xbf27 is 0xbf (¿) followed by 0x27 (')". ;-)

I actually didn't realise that 0xbf27 (¿') was not a valid multi-byte character, and now in hindsight I can't say why I assumed it was. Guess I got confused by all the hexadecimals.

Thanks again!

Thu, 11 Jan 2007 at 10:36:33 GMT Link


38.Chris Shiflett said:

Hi Eivind,

Glad that cleared things up. :-)

Thu, 11 Jan 2007 at 15:15:50 GMT Link


39.Takuan said:

Fantastic read!!

I am currently trying to see how this applies without a database.

For example, i have a file a.php that has only one line of code:

<?php

include($c . ".php");

?>

With register_globals turned ON and magic_quotes, safemode, etc.. turned OFF (for simplicity of the example)

I can specify the URL

http://localhost/a.php?c=../../../...../etc/passwd%00

and it will return the passwd file. Nothing new here.

However, when i turn magic_quotes ON, the null byte gets turned into \0 so i cant reach the passwd file. (Nothing new here either)

Warning: main(../../../../../../etc/passwd\0.php): failed to open stream: No such file or directory in /var/www/localhost/htdocs/a.php on line 2

Warning: main(): Failed opening '../../../../../../etc/passwd\0.php' for inclusion (include_path='.:') in /var/www/localhost/htdocs/a.php on line 2

So my two questions are:

1. What character encoding does apache run under?(how do i check?)

2. Is it possible to bypass addslashes(magic_quotes) with char. encoding foolery in this scenario? (if not, why?)

Tue, 23 Jan 2007 at 04:43:49 GMT Link


40.Unknown said:

Can I use, The escapeshellcmd() , to go to Heaven.

Sat, 10 Feb 2007 at 08:16:33 GMT Link


41.Matthias Hogerheijde said:

Hi Chris,

Nice blog, I really enjoyed reading this.

I would like to add some security advice to your post:

First of all:

Make 2 database users, 1 read only on the user table. Who is only allowed to check login data. Minimizes the chance a successful injection actually corrupting your data.

Second of all:

never, ever, ever use "WHERE username = '$username' AND password = '$password' " in your SQL. Just do something like

SELECT password FROM "user" WHERE username = '$username'

Of course $username has some sort of character escaping.

Check if mysql_numrows() returns 1 => username should be a unique field

check the $_POST['password'] against the encrypted password.

Then, after you are actually logged in, use the database user with UPDATE and INSERT rights.

This won't eliminate the possibility for SQL injections, but you are a safe against logins from sql injections. Howerver, after a user has logged in, you are still vulnerable.

Fri, 02 Mar 2007 at 10:50:36 GMT Link


42.Chris Shiflett said:

Thanks for the comment, Matthias.

I agree with your point about the WHERE clause, and your approach allows for other features such as throttling:

http://phpsecurity.org/code/ch07-2

Mon, 05 Mar 2007 at 04:17:48 GMT Link


43.Matthias Hogerheijde said:

Indeed, that is a nice way to slow down brute force attacks :)

The only thing i don't like about this approach is that you can find out if a username exists. If it does, you will get the timeout, if it doesn't you won't.

Secondly it shows the usage of a 'salt' which is a very good practice, but it uses the same salt for all the passwords, which is useless imho.

But these comments don't have anything to do with escaping characters :)

Thu, 08 Mar 2007 at 10:18:26 GMT Link


44.Chris Shiflett said:

Hi Matthias,

I don't think my example reveals which usernames exist, because you cannot distinguish among:

1. A login that fails because the username doesn't exist.

2. A login that fails because the password is incorrect.

3. A login that fails because fewer than 15 seconds have passed since the last failure.

The comments I use in the example are very specific, but that is only so that readers understand what the different conditions represent. It does not mean this information should be shared with the person logging in.

Using a salt protects against rainbow tables:

http://md5.rednoize.com/

I don't think this is useless, even if a static one is being used.

Thu, 08 Mar 2007 at 15:02:27 GMT Link


45.Sean O'Donnell said:

What is there to debate. RTFM =)

Fri, 06 Apr 2007 at 17:37:36 GMT Link


46.Joe G said:

Just thought i'd mention to Takuan that, in the event such as:

<?php

include($c . ".php");

?>

you are worried that someone will change the URL and include the unique file they want simple add a php file which defines all the pages allowed, and itterate the $c and if it is not equal to any of those then don't allow the include or set the $c to something like "errorpage"...

By the way Chris I found this article quite helpful. Many websites state that the addslashes() function will do the trick so i'm glad I dug a little deeper and found this. While attempting a test run of my own i've failed to hack into my own login script but I might just suggest that alongside with the mysql_real_escape_string() function you should always avoid returning information you don't need to, so for example don't get lazy and use "SELECT * FROM ..." rather use "SELECT username, id FROM ..."where you only need username and id.

Tue, 08 May 2007 at 19:26:23 GMT Link


47.Sam said:

Hi,

If I am using prepared statements, do I still need to use mysql_escape_real_string on the variables?

Wed, 06 Jun 2007 at 11:58:58 GMT Link


48.Loren Cannon said:

Chris, it's just so much easier to do salt right in the first place. If you have a randomized id /salt column, you don't need to hide any secrets.

Mon, 23 Jul 2007 at 21:25:59 GMT Link


49.Mayka said:

Hi, do you need use stripslashes with mysql_escape_real_string?

THx

Mon, 24 Sep 2007 at 16:17:18 GMT Link


50.Quinton Parker said:

I wholeheartedly agree with using Prepared Statements as far as possible BUT there is a catch...

At time of writing, MySQL isn't able to query cache SELECTs issued using prepared statements.

See 3rd note: http://dev.mysql.com/doc/refman/5.0/en/query-cache.html

In a high production environ (or any for that matter) query cache is a fantastic optimization tool in a database-driven app... personally i use sprintf() to substitute vars in select statements like this:

sprintf("SELECT stuff FROM mytable WHERE id = %d LIMIT 1",$iId);

Are there any caveats to that method? Anyone?

Sat, 29 Sep 2007 at 12:14:02 GMT Link


51.Chris Shiflett said:

Hi Quinton,

Use PDO:

http://netevil.org/blog/2006/apr/using-pdo-mysql

Sat, 29 Sep 2007 at 14:09:24 GMT Link


52.Dor said:

Hi Chris,

I read your post and others' comments, but couldn't actually understand it all.

My questions are:

1) If I use UTF-8, may I use addslashes() to prevent SQL Injection? although I know mysql_real_escape_string() is preferred.

2) My method to prevent SQL Injections is similiar to Quinton Parker's method:

sprintf("SELECT stuff FROM mytable WHERE id = %d LIMIT 1",$iId);

( I'm using mysql_real_escape_string() too. )

I don't want to use PDO. Will the above method prevent SQL Injections?

3) Am I need to secure a string (with addslashes() etc.), if it won't be inserted to the DB?

[ Sorry for the bother ]

Many thanks.

Dor.

Sat, 10 Nov 2007 at 23:03:15 GMT Link


53.dedlfix said:

@11 GhostGambler: As of PHP 5.2.3 the mysql extension now also has mysql_set_charset(). It works as mysqli_set_charset() and affects the encoding mysql-real_escape_string() will use (rather than using SET NAMES in a statement).

Fri, 04 Jan 2008 at 13:32:00 GMT Link


54.Rich K said:

Why is Zend Framework (1.0.3) then using addslashes() underneath it all? Was hoping to use the db layer, but now I don't know...

protected function _quote($value)

{

if (is_int($value) || is_float($value)) {

return $value;

}

return "'" . addcslashes($value, "\000\n\r\\'\"\032") . "'";

}

Tue, 22 Jan 2008 at 15:27:05 GMT Link


55.Rich said:

Why is Zend Framework (1.0.3) then using addslashes() underneath it all? Was hoping to use the db layer, but now I don't know...

protected function _quote($value)

{

if (is_int($value) || is_float($value)) {

return $value;

}

return "'" . addcslashes($value, "\000\n\r\\'\"\032") . "'";

}

Tue, 22 Jan 2008 at 15:27:31 GMT Link


56.ZX said:

Assume the following table:

create table person(personid int, fname varchar(32), lname varchar(32) primary key (personid));

When I use mysql_real_escape_string to escape a string with an SQL insert statement, an lname = O'Donnell is escaped to O\\\'Donnell in the string, but the value of O\'Donnell is actually stored in the database.

The problem is, when I'm searching for data, and I escape the SQL statement for searching, the actual value of O'Donnell is escaped to O\\\'Donnell. This value will never match what's in the database, which is the value of O\'Donnell.

How should I handle this scenario?

Thoughts?

Tue, 11 Mar 2008 at 22:35:24 GMT Link


57.ZX said:

Ok. I found out what the problem was. Magic quotes was active, so my input was being doubly escaped.

So, for anyone out there, here's is the scenario that I can not find a way to resolve. If the escaped string "O\'Donnell" is placed into a mySQL database using PHP code, the actual data that resides in the database will be "O'Donnell"

That is consistent behavior.

However, if someone enters the last name "O'Donnell" into my HTML form field, it will be escaped to "O\'Donnell" before PHP passes it to mySQL.

The problem is that the escaped value of "O\'Donnell" will never match the actual value of "O'Donnell" which is stored in the database.

Has anyone found a way to resolve this?

Thanks.

-Z

Tue, 18 Mar 2008 at 17:31:20 GMT Link


58.Peterle said:

For me this looks rather like GBK was designed by an idiot. Just use utf-8 and be happy! Since all mcbs in utf-8 only contain bytes > 127, searching forbidden strings (except containing "?") with all characters < 127 and standard addslashes should be safe. Invalid sequences like [begin 2-byte-char]['] seem to be handled smart by most lexers.

Mon, 07 Apr 2008 at 11:19:55 GMT Link


59.Jawaad said:

Hi ZX,

if the data in the database "O\'Donnell".

Then you should escape it the HTML form field.

like this:

// assuming db connected
 
$name = mysql_real_escape_string($_POST["name"]);
 
$sql = "SELECT `name` FROM `member` WHERE `name` LIKE %".$name."%";

it works for me..

Wed, 23 Apr 2008 at 19:19:57 GMT Link


60.Chris Shiflett said:

Other character sets that are affected:

http://kuza55.blogspot.com/2007/06/...ng-attacks.html

Tue, 16 Sep 2008 at 23:35:01 GMT Link


61.Wayne said:

Hi ZX,

When taking in data, you should always check to see if magic_quotes is enabled. If it is, use stripslashes() and then use mysql_real_escape_string(). Use the function get_magic_quotes_gpc(void) to figure out whether your hosts have magic quotes enabled.

Regards

Wayne

Tue, 07 Oct 2008 at 12:19:57 GMT Link


62.Allen G said:

I escape everything of interest that goes into a storage into html entities using str_replace().

&39; is the character code for the single quote

&34; is the character code for the double code

&92; is the character code for the backslash

It's important to make sure the & is escaped first or not at all.

I would guess that my escape method leaves me safe from this attack, but if anyone would like to

Thu, 18 Dec 2008 at 08:05:34 GMT Link


63.david croquet said:

I can't use your method to hack this testing site:

http://hackme.ntobjectives.com/sql_..._addslashes.php

could you open my mind ?

Mon, 29 Dec 2008 at 21:14:46 GMT Link


64.Mac MacLaren said:

One disadvantage of mysql_real_escape_string is that you must have already opened a MySQL connection before you call this function. This restricts the way that you write your code.

It would be more useful if this function was part of the PHP core library (say "sql_addslashes()", rather than part of the MySQL interface.

The second parameter to mysql_real_escape_string is a $link_identifier. To quote the PHP manual:

"If the link identifier is not specified, the last link opened by mysql_connect() is assumed. If no such link is found, it will try to create one as if mysql_connect() was called with no arguments. If by chance no connection is found or established, an E_WARNING level error is generated."

Wed, 04 Mar 2009 at 00:11:13 GMT Link


65.Don holio said:

Chris... Nice post but why not use a regex before using addslashes()?

if(!erg('^[[:graph:][:space:]]*$', $string){ exit();}

This would only allow for whitespace and printable characters found in the range of ASCII 33 to 126 (punctuation and alphanumerics).

i.e.

$string = $_POST['$string'];

if(!erg('^[[:graph:][:space:]]*$', $string){ exit();}

$string = addslashes($string);

or why don't you just stick with mysql's default charset setting UTF-8? Please, correct me if i'm wrong.

Sat, 07 Mar 2009 at 21:34:48 GMT Link


66.Error Code said:

Hi chris,

I liked your post a lot

i have one question because am confused between using mysql_real_escape_string() & addslashes()

i wrote that function today because i was writing a small management script. will that function help ??

<?php
 
class Safe
 
{
 
    function sql_quote($value)
 
    {
 
    if(get_magic_quotes_gpc())
 
    {
 
        $value = stripslashes($value);
 
        //$value = mysql_real_escape_string($value);
 
        $var = array(("\"","\\","/","*","'","=","-","#",";","<",">","+","%","");
 
        $value = str_replace($var,"",$value);
 
    }
 
    else
 
    {
 
        $value = addslashes($file_name);
 
        $value = stripslashes($value);
 
        $var   = array("\"","\\","/","*","'","=","-","#",";","<",">","+","%");
 
        $value = str_replace($var,"",$value);
 
    }
 
    return $value;
 
    }
 
}
 
?>

Sat, 14 Mar 2009 at 21:15:34 GMT Link


67.Andrew said:

What if I use Oracle as database, what's the equivalent of mysql_real_escape_string() function for Oracle? Or should I create my own function to escape the characters?

Anyone have tips & tricks to share?

Thu, 30 Jul 2009 at 14:07:12 GMT Link


68.Jonny said:

@andrew

Looks like a PDO driver is in the works for oracle:

http://us2.php.net/manual/en/ref.pdo-oci.php

Thu, 06 Aug 2009 at 04:41:20 GMT Link


69.liukang said:

I have problem with this example.

In my php.ini magic_quotes_gpc is off so i'm using only addslashes() in script. Table in MySQL i made with your SQL code and i added one record 'liu' 'kang' :) but anyway $db->query($sql); wan't give me this one record in result...

echo $sql:

SELECT * FROM users WHERE username = '<here is some chinesse sign>' OR username = username /*' AND password = 'guess'

What am i doing wrong?

(sorry for my poor english)

Sun, 14 Mar 2010 at 12:22:02 GMT Link


70.RyanTheGreat said:

@liukang

Remove the single quote after the comment section (/*')

That is to say, your query should look like this:

SELECT * FROM users WHERE username = '<here is some chinesse sign>' OR username = username /* AND password = 'guess'

instead of:

SELECT * FROM users WHERE username = '<here is some chinesse sign>' OR username = username /*' AND password = 'guess'

Fri, 26 Mar 2010 at 13:33:14 GMT Link


71.spaceclam said:

In order to bypass mysql_real_escape string, is it necessary for the database to be configured to use one of the multi-byte character encodings?

Tue, 21 Sep 2010 at 16:08:38 GMT Link


72.Anonymous said:

Very well-done explanation. I'm not sure you're reading comments at this point - too many - ugh!

That said, I am wondering about output going to the browser instead of the database - specifically data intended to be displayed as a value in a text input. I like to escape just double quotes for such data (using str_replace), but with such multibyte character sets, it looks like just using the following would be vulnerable in the same way as you explain in this post. Is that true?

echo '<input type="text" name="name" value="' . str_replace('"', '\"', $data) . '">';

Fri, 24 Sep 2010 at 04:26:15 GMT Link


73.Swati said:

Its nice.

MySQL_real_escape_string: break out particular characters in a string for utilize in an SQL expression/statement.

You can obtain more information of it.

http://www.phpandsql.com/how-to-use..._string-in-php/

Fri, 19 Jun 2015 at 11:38:43 GMT Link


Hello! What’s your name?

Want to comment? Please connect with Twitter to join the discussion.