Page 1 of 2
mysql_real_escape_string
Posted: Wed Oct 05, 2005 8:08 pm
by StumpDK
Well, can I consider my code free of sql injection risks if I use the mysql_real_escape_string-function?
And what does it do? Escape strings, yes, but what does that mean? I've read about the function at php.net, but I can't seem to figure it out....
And if the mysql_real_escape_string-function isn't secure, what options does I have then?
Posted: Wed Oct 05, 2005 8:25 pm
by Jenk
Use it after the connection is established (but before you use mysql_query() of course)
As the name hints, use it on Strings (it can be used on any type, but I shall come back to this)
Only use it once per variable, else you will double escape which is a boo boo.
Use it properly in a function such as below, to avoid double escaping the magic quotes escaping:
Code: Select all
<?php
function sqlClean ($string) {
if (get_magic_quotes_gpc()) {
$string = stripslashes($string);
}
return mysql_real_escape_string($string);
}
?>
Consider what the output will be and if it will require escaping, for example:
Code: Select all
<?php
//is a user input string - needs escaping!
$username = (isset($_POST['username']) ? mysql_real_escape_string($_POST['username']) : false);
//MD5 does not produce any char's that require escaping.. so no need for it here.
$password = (isset($_POST['password']) ? md5($_POST['password']) : false);
//Integers can be cleansed with intval(), floor() etc. floats with floatval()
$id = (isset($_POST['id']) ? intval($_POST['id']) : false);
?>
Posted: Wed Oct 05, 2005 8:39 pm
by mickd
it says it in the php manual
mysql_real_escape_string() calls MySQL's library function mysql_real_escape_string, which prepends backslashes to the following characters: \x00, \n, \r, \, ', " and \x1a.
it makes the code safe by putting a backslash infront of the characters above so that the scripts doesnt interpret those and instead ignores their special purpose.
Posted: Wed Oct 05, 2005 11:15 pm
by wyred
Jenk wrote:Use it after the connection is established (but before you use mysql_query() of course)
Why does it need to connect to the mysql server? Is it using the server to do the escaping?
Posted: Wed Oct 05, 2005 11:22 pm
by John Cartwright
Description
string mysql_real_escape_string ( string unescaped_string [, resource link_identifier] )
link_identifier
The MySQL connection. 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 warning is generated.
Posted: Thu Oct 06, 2005 5:48 am
by Jenk
The reason it connects to the MySQL server/db is to determine which character set will be used so it can escape the char's accordingly.
Posted: Thu Oct 06, 2005 7:44 pm
by StumpDK
Okay... Here's a simple example:
Code: Select all
function login($username){
$username = mysql_real_escape_string($username);
$query = "SELECT username, password FROM users WHERE username = '$username'";
}
If $username is something like 'bob; DELETE * FROM table;', it don't change after I've used the mysql_real_escape_string... What am I doing wrong?
Posted: Thu Oct 06, 2005 7:48 pm
by feyd
there's nothing to escape..
Posted: Thu Oct 06, 2005 8:00 pm
by StumpDK
But then the user input won't be save... How can I avoid SQL injection then?
Posted: Thu Oct 06, 2005 8:05 pm
by feyd
that isn't SQL injection. It's a valid string, there was nothing to escape that could even be close to SQL injection.
Posted: Thu Oct 06, 2005 8:12 pm
by StumpDK
But if the $username is comming from $_POST['username'], then there will be a risk, won't there?
According to
http://www.php.net/manual/en/security.d ... ection.php
Posted: Thu Oct 06, 2005 8:34 pm
by feyd
be aware of the context in which $username is being used in the SQL query. Notice how $username is surrounded by single quotes? That denotes a string. In order to attempt SQL injection, one would have to send an even number of single quotes to escape out of the string being created.
Now, if $username was not being stored as a string and you didn't place quotes around it inside the query string, you would have attempted to perform SQL injection. However mysql_query() will not perform multiple queries on its own.
Posted: Thu Oct 06, 2005 8:54 pm
by StumpDK
So using a variable and mysql_query makes the script COMPLETELY safe from SQL injection attacks?
Posted: Thu Oct 06, 2005 8:59 pm
by Nathaniel
StumpDK wrote:So using a variable and mysql_query makes the script COMPLETELY safe from SQL injection attacks?
Read what feyd wrote. Using a variable, mysql_query, and placing the variable inside single quotes like you did makes the script safe from SQL injection attacks.
Posted: Thu Oct 06, 2005 9:30 pm
by feyd
StumpDK wrote:So using a variable and mysql_query makes the script COMPLETELY safe from SQL injection attacks?
I never said nor implied that. It's not true. It's the opposite. It appears you haven't understood anything that I've said, so I'll try again.
Given the following:
Code: Select all
$username = 'bob; DELETE * FROM table;';
$sql = "SELECT * FROM table WHERE username = '$username'";
echo $sql;
the output is
Code: Select all
SELECT * FROM table WHERE username = 'bob; DELETE * FROM table;'
In this case $username does not contain any of the characters that require escaping such as \x00, \n, \r, \, ', " and \x1a as listed previously. As you may see, $username had nothing to escape.
Now, assume you have the following:
Code: Select all
$username = 'bob\' OR username <> \''; // bob' OR username <> '
$sql = "SELECT * FROM table WHERE username = '$username'";
echo $sql;
would output
Code: Select all
SELECT * FROM table WHERE username = 'bob' OR username <> ''
As you see, that's a definite injection. All variables used in a query should be escaped, for good measure if nothing else. Adding escaping to the previous code:
Code: Select all
$username = 'bob\' OR username <> \''; // bob' OR username <> '
$username = mysql_real_escape_string($username);
$sql = "SELECT * FROM table WHERE username = '$username'";
echo $sql;
now outputs
Code: Select all
SELECT * FROM table WHERE username = 'bob\' OR username <> \''
which is not injection.
Understand now?