mysql_real_escape_string

Discussions of secure PHP coding. Security in software is important, so don't be afraid to ask. And when answering: be anal. Nitpick. No security vulnerability is too small.

Moderator: General Moderators

StumpDK
Forum Commoner
Posts: 35
Joined: Thu Feb 12, 2004 2:28 am
Location: Copenhagen, Denmark

mysql_real_escape_string

Post 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?
User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

Post 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);

?>
mickd
Forum Contributor
Posts: 397
Joined: Tue Jun 21, 2005 9:05 am
Location: Australia

Post 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.
wyred
Forum Commoner
Posts: 86
Joined: Mon Dec 20, 2004 1:59 am
Location: Singapore

Post 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?
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post 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.
User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

Post 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.
StumpDK
Forum Commoner
Posts: 35
Joined: Thu Feb 12, 2004 2:28 am
Location: Copenhagen, Denmark

Post 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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

there's nothing to escape..
StumpDK
Forum Commoner
Posts: 35
Joined: Thu Feb 12, 2004 2:28 am
Location: Copenhagen, Denmark

Post by StumpDK »

But then the user input won't be save... How can I avoid SQL injection then?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
StumpDK
Forum Commoner
Posts: 35
Joined: Thu Feb 12, 2004 2:28 am
Location: Copenhagen, Denmark

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
StumpDK
Forum Commoner
Posts: 35
Joined: Thu Feb 12, 2004 2:28 am
Location: Copenhagen, Denmark

Post by StumpDK »

So using a variable and mysql_query makes the script COMPLETELY safe from SQL injection attacks?
User avatar
Nathaniel
Forum Contributor
Posts: 396
Joined: Wed Aug 31, 2005 5:58 pm
Location: Arkansas, USA

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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?
Post Reply