[RESOLVED] Testing the mysqli_real_escape_string() function?

[Pikachu2000]

This is probably a silly question, but I searched the forum for an answer to this, but didn't find anything that really answers it. How can I make certain mysqli_real_escape_string() is doing what it is supposed to be doing? What string could I put in the $_GET['test'] var below to see a different result from the first print_r() to the second print_r() ?

I've always just trusted that it worked as it was supposed to, and I'm thinking that may not be such a good idea.

$_GET['test'] = "Some String";
print_r($_GET['test']);
mysqli_real_escape_string($dbc, $_GET['test']);
print_r($_GET['test']);

[knowj]

It does basic SQL Injection protection however trying to cover all bases using mysqli_real_escape_string is a nightmare.

try something along the lines of this

$name = 'fred';
if ($stmt = $mysqli->prepare("SELECT `firstName`, `lastName` FROM `members` WHERE `firstName`=?"))
{
    //bind name to query as string (first parameter 's' means string)
    $stmt->bind_param("s", $name);

/* execute query */
$stmt->execute();

//bind the results to variables
$stmt->bind_result($firstName, $lastName);

//loop out the results
while ($stmt->fetch())
    {
         echo $firstName.' '.$lastName.'<br />'."\n";
    }
}

I always used to use escape data until I found prepared statements. It makes life so much easier and always covers your a**. Just need to cover XXS and bots then.

More info here:
http://php.net/manual/en/mysqli.prepare.php

Any questions just ask.

[Pikachu2000]

knowj;10942900 wrote:

It does basic SQL Injection protection however trying to cover all bases using mysqli_real_escape_string is a nightmare.

. . .

Any questions just ask.

OK, thanks for the response. Frankly, I've never used prepared statements. I'm not a PHP professional, but I use it for my own websites. I know there are a lot of things I can learn to make it easier as my sites get more complicated. Soooooo, now my question is: what does that block of code do, or more accurately, what other functions does it replace?

[knowj]

It simply gets everyone with the first name "fred" from the database and echos out the first name and last name.

you specify each field from the database you want to retrieve from the database (as you should do for performance) and any places you would usually have a variable within the SQL query you put a question mark.

Then on $stmt->bind_results(1, 2, 2);

1: would be a string consisting of data types int (i), string (s), double (d), blob (b usually a file)
2: for each ? you would put your variable here.

e.g.

$name = (string) 'fred';
$age = (int) 10;
$stmt->bind_param('si', $name, $age);

Applied to the previous query if you added "AND age=?" you would get everyone who is age 10 and called fred. It works in the order so the first ? is the first variable within the bind_param.

I used the object oriented style personally as I find it more effective and easier. However if you click the link on the previous post there is a procedural style mysqli.

You would use prepared statements for pretty much all SQL queries.

Make a test file and work with it on a small scale. The SQL isn't any different apart from ? instead of '$var' (you don't need to quote out the ?). Trial and error.

[sneakyimp]

knowj's point is that the prepared statements take care of escaping things properly for you. I can see that it looks a bit like shorthand for all the elaborate escaping you would otherwise have to do. Imagine you are inserting 20 strings into varchar fields and must manually write the sql statement? sounds like a mess. Beyond that, I have always wondered why people like prepared statements so much.

As for [man]mysql_real_escape_string[/man], the documentation says:

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.

Basically, it alters newline chars, carriage returns, quotes, and a couple of weird characters. I think \x00 is the null char or ASCII zero and i have no idea what \x1a is. I think it may be EOF or something.

[bradgrafelman]

www.asciitable.com

There is no "EOF" character, but hex code 1A is "SUB (substitute)".

@: The function [man]mysqli_real_escape_string/man doesn't alter the string you pass as a paramter by reference - it returns the new value. Thus, this code:

mysqli_real_escape_string($dbc, $_GET['test']); 

essentially does nothing. This, however:

$test = mysqli_real_escape_string($dbc, $_GET['test']); 

stores the modified string in the $test variable, for use in a SQL query.

[Pikachu2000]

bradgrafelman;10942949 wrote:

www.asciitable.com

There is no "EOF" character, but hex code 1A is "SUB (substitute)".

@: The function [man]mysqli_real_escape_string/man doesn't alter the string you pass as a paramter by reference - it returns the new value. Thus, this code:

mysqli_real_escape_string($dbc, $_GET['test']); 

essentially does nothing. This, however:

$test = mysqli_real_escape_string($dbc, $_GET['test']); 

stores the modified string in the $test variable, for use in a SQL query.

Right, I understand that, Brad. What I was really wondering was if there's a string I can feed it to actually see the difference. In other words, if I were to do, for example:

$some_value = \\x00\\r\\n0111010;
$new_value = mysqli_real_escape_string($dbc, $some_value);
echo( $new_value );

// OR         //

$some_value = "This is a text string with x00 \\n\\r";
$new_value = mysqli_real_escape_string($dbc, $some_value);
echo( $new_value );

What should I expect to see as a result?

Also, as an aside, am I correct in assuming there is no reason to run a password that is going to be hashed through the mysqli_real_escape_string() function before inserting it in the DB?

[Pikachu2000]

bradgrafelman;10942949 wrote:

www.asciitable.com

There is no "EOF" character, but hex code 1A is "SUB (substitute)".

@: The function [man]mysqli_real_escape_string/man doesn't alter the string you pass as a paramter by reference - it returns the new value. Thus, this code:

mysqli_real_escape_string($dbc, $_GET['test']); 

essentially does nothing. This, however:

$test = mysqli_real_escape_string($dbc, $_GET['test']); 

stores the modified string in the $test variable, for use in a SQL query.

Right, I understand that, Brad. What I was really wondering was if there's a string I can feed it to actually see the difference. In other words, if I were to do, for example:

$some_value = "\x00\r\n0111010;" // <---- Remove the quotes, I couldn't get the backslashes to display without them ... 
$new_value = mysqli_real_escape_string($dbc, $some_value);
echo( $new_value );

// OR         //

$some_value = "This is a text string with x00 \n\r";
$new_value = mysqli_real_escape_string($dbc, $some_value);
echo( $new_value );

What should I expect to see as a result?

Also, as an aside, am I correct in assuming if a value is going to be hashed, there is no reason to through the mysqli_real_escape_string() function before inserting it in the DB?

[bradgrafelman]

Pikachu2000;10943061 wrote:

Right, I understand that, Brad. What I was really wondering was if there's a string I can feed it to actually see the difference.

Easiest example:

$string = "O'Connor";

Pikachu2000;10943061 wrote:

What should I expect to see as a result?

O\'Connor

Pikachu2000;10943061 wrote:

Also, as an aside, am I correct in assuming there is no reason to run a password that is going to be hashed through the mysqli_real_escape_string() function before inserting it in the DB?

Well that depends on where you do the hashing. If you do it in the query itself, e.g.:

PASSWORD('$_POST[password]')

then no, you still need to sanitize it since it's going directly into the query string. If, on the other hand, you're doing it in PHP, e.g.:

$password = sha1($_POST['password']);

then that would depend on the output of the hashing algorithm. MD5/SHA1/etc. only produce alphanumeric hashes, so in these cases yes, there is no need to try and sanitize the hashed output since there can't possibly be any problematic characters.

[sneakyimp]

The problem with the examples you've chosen is that when you write them in code, you type them in escaped form. In other words, to specify a newline character, you type the escaped form in your code. When you output the contents of such a string, you see the newline character manifested as a break in some text that results in the latter part of the text appearing on a new line.

When you use mysql_real_escape_string on "\n", then it gives you a backslash and an "n" as output rather than the (invisible) newline character. For example, this code

 echo "here is one line\nhere is another line";

should output this:

here is one line
here is another line

where as this code:

 echo mysql_real_escape_string("here is one line\nhere is another line");

should output this:

here is one line\nhere is another line

Try this:

$str = ' "Here is a string with quotes in it" ';
echo (mysql_real_escape_string($str));

[Pikachu2000]

bradgrafelman;10943064 wrote:

Easiest example:

$string = "O'Connor";

O\'Connor

Oh, duh! As soon as I saw that, it made perfect sense. Thanks.

Well that depends on where you do the hashing. If you do it in the query itself, e.g.:

PASSWORD('$_POST[password]')

then no, you still need to sanitize it since it's going directly into the query string. If, on the other hand, you're doing it in PHP, e.g.:

$password = sha1($_POST['password']);

then that would depend on the output of the hashing algorithm. MD5/SHA1/etc. only produce alphanumeric hashes, so in these cases yes, there is no need to try and sanitize the hashed output since there can't possibly be any problematic characters.

I should have been more clear, I'll be using the php hash('sha256', $string) function with a salt. Thanks for the answer; it explained what I needed to know, but sorry to make you type out all that . . .

[bradgrafelman]

Pikachu2000;10943066 wrote:

but sorry to make you type out all that . . .

Don't worry about it.. I type fast. 😉

[NogDog]

It probably is unnecessary to escape the hashed password. However, it does not hurt anything, and could prevent damage should something unexpected happen in your code. So, from a defensive coding standpoint, it's probably not a bad idea to escape it anyway.

[sneakyimp]

My general rule of thumb has been that if it's going into a varchar or text or char field then it needs to be escaped. if it's going into a numeric field then it needs to be cast as the appropriate numeric type.

The basic idea is that to be secure, you want to avoid a situation where user input can put in whatever sql they want. For instance, if you put user input directly into a query, this query:

$sql = "SELECT * FROM sometable WHERE email='" . $_GET['input'] . "'";

Can easily be turned into this by setting the $_GET['input'] to the appropriate string:

SELECT * FROM sometable WHERE email='' UNION SELECT * FROM secret_password_table WHERE username='root'

Maybe that's a poor example of sql injection but i think you get the idea. You have to validate/screen/escape user input before you put it into a query. It helps to be on your toes about it.

Escaping is also important so all the irish O'Malleys and O'Connors don't break your sql when they enter their last names.

[laserlight]

sneakyimp wrote:

knowj's point is that the prepared statements take care of escaping things properly for you. I can see that it looks a bit like shorthand for all the elaborate escaping you would otherwise have to do. Imagine you are inserting 20 strings into varchar fields and must manually write the sql statement? sounds like a mess. Beyond that, I have always wondered why people like prepared statements so much.

There is the possibility of reusing prepared statements, and in fact that is probably what the mechanism was designed for in the first place. But with respect to security, the benefit of using prepared statements is that the data is separated from the SQL statement. Unless the prepared statement mechanism is merely emulated, one cannot inject into the SQL statement, because one can only tamper with the data provided to the statement, which cannot change the statement itself.

sneakyimp wrote:

Escaping is also important so all the irish O'Malleys and O'Connors don't break your sql when they enter their last names.

Or poor little Robert'); DROP TABLE Students;--.

[Pikachu2000]

Thanks, everyone for the answers. That gives me plenty of things to consider, and figure out how to implement what, and where. Much appreciated.