What's the use of mysqli_real_escape_string in case of ' / ' and ' \ ' and ' ' '?

Question

Whats the use of mysqli_real_escape_string if the user inputs

/

\

'

in the input field ?

Are they really harmful ?

And if they are , what's the harm they do ?

`Characters encoded are NUL (ASCII 0), \n, \r, \, ', ", and Control-Z` — bitWorking, Jul 17 '13 at 17:20
i actually want to learn more about the escape_characters and the harm they do...just like this question http://stackoverflow.com/questions/11653059/when-to-use-mysqli-real-escape-string?rq=1 — user2591915, Jul 17 '13 at 17:21
Suppose the user types `hello \n world`, that would be translated into a new line. What `->real_escape_string` would do is make it into `hello \\n world`. However, if you're preparing, you shouldn't need to worry about this. — Dave Chen, Jul 17 '13 at 17:22
You need to read [The Great Escapism](http://kunststube.net/escapism) — deceze, Jul 17 '13 at 17:34

score 1 · Answer 1 · answered Jul 17 '13 at 17:32

The risk of the ' character is that a literal quote character in input can prematurely close a quoted string in an SQL expression. For example:

UPDATE Users SET password = '$pass' WHERE userid = $id

This assumes that $input is safe within a string. If I pass an URL like this:

http://example.com/setpassword.php?id=1234&pass=xyzzy', admin='1

I can trick your code into doing this:

UPDATE Users SET password = 'xyzzy' AND admin='1' WHERE userid = 1234

Giving myself admin privileges.

The purpose of functions like mysqli_real_escape_string() is to prevent that, by ensuring literal quote characters are not used to terminate a string in which the input is copied.

So the worst I could do is set my password to a long, strange string:

UPDATE Users SET password = 'xyzzy\' AND admin=1' WHERE userid = 1234

It's often more convenient to bind input variables into a query using parameter placeholders, instead of copying them directly into the string.

<?php

$stmt = $mysqli->prepare("UPDATE Users SET password = ? WHERE userid = ?");
$stmt->bind_param("si", $pass, $id);
$stmt->execute();

That way you don't have to worry about escaping, or imbalanced quotes. It makes your code look nicer, easier to read, and easier to debug and maintain.

The only drawback is that parameters take the place of a scalar value only. You can't use them to insert dynamic table names, column names, expressions, SQL keywords, etc. Only where you would normally put a string literal, a date literal, or a numeric literal in your SQL code.

wedstrom · Answer 2 · 2013-07-17T17:31:14.550

$sql = "SELECT * FROM myTable WHERE col1 = '" . mysqli_real_escape_string($userInput) . "'";

Given your user input, $sql now equals

"SELECT * FROM myTable WHERE col1 = '\/\\n\\\\n\'\'"

and is safe to run, with this or any user input.

If you had not used the escape function, the output would be

SELECT * FROM myTable WHERE col1 = '/

\

''

And will create an error if queried. Even worse, when an attacker tries a sql injection such as:

' OR 1 = 1 OR col1 = '

Making your sql:

SELECT * FROM myTable WHERE col1 = '' OR 1 = 1 OR col1 = ''

See how the user added some fields to the search? Your sql call can be now be injected with anything the attacker wants, including drop,password bypass,data compromise etc.

What's the use of mysqli_real_escape_string in case of ' / ' and ' \ ' and ' ' '?

2 Answers2