What is the correct way to escape a sql statement?

Question

I read lot of things about the common sql injection so got interested on how to fix them. Before I just used to use addslashes() thinking (badly) that it would fit. Then I discovered that mysql(i)_real_escape_string() is way more useful and trustful than addslashes(). Since then I use mysqli_real_escape_string() but recently I got into something I haven't really understood. I had some problem about sending datas to mysql and character set. So, once again, I searched, and many users say that SET NAMES UTF8 is the way to make everything goes the correct way. But then I read that using that query makes mysqli_real_escape_string() not working.

So after all I got a little bit confused.

What's the correct way to escape a sql statement?

What are the possible exploits using SET NAMES UTF8?

Is mysqli_set_charset() the correct way to make the connection communicate in a specified charset?

Using mysqli_sey_charset() are the mysql's internal variables cchanged in the process?

Thanks

Please edit your post and include example of SQL before and after being processed by 'mysqli_real_escape_string()'. — , Aug 08 '12 at 11:42

score 2 · Answer 1 · answered Aug 08 '12 at 11:42

2

You can use mysqli prepared statements to avoid SQL injection - without the need to worry about character set encoding.

An example from the link:

<?php
$mysqli = new mysqli("localhost", "my_user", "my_password", "world");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

$city = "Amersfoort";

/* create a prepared statement */
if ($stmt = $mysqli->prepare("SELECT District FROM City WHERE Name=?")) {

    /* bind parameters for markers */
    $stmt->bind_param("s", $city);

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

    /* bind result variables */
    $stmt->bind_result($district);

    /* fetch value */
    $stmt->fetch();

    printf("%s is in district %s\n", $city, $district);

    /* close statement */
    $stmt->close();
}

/* close connection */
$mysqli->close();
?>

answered Aug 08 '12 at 11:42

Fluffeh

33,228
16
67
80

Can't use this method. And I also would like to understand how thing works. – Kei Aug 09 '12 at 14:57
@kei Why can't you use this method? – Fluffeh Aug 09 '12 at 14:59
As I said I would like to understand how things work, so my questions are precise. Then, in the project we decided to use standard methods, so I can't just say "hey let's make everything in this way". – Kei Aug 09 '12 at 15:11
@Kei: this is actually THE standard method. Passing manually constructed queries is only supported due to backwards compatibility concerns. Modern applications should NEVER do it, and use prepared statements instead. – MarioVilas Mar 08 '13 at 22:08

score 0 · Answer 2 · answered Mar 08 '13 at 22:11

Passing manually constructed queries is only supported due to backwards compatibility concerns. Modern applications should NEVER do it, and use prepared statements instead.

Having said that, if you're dead set on using manually constructed queries the proper way to escape them is with mysql_real_escape_string(), and setting the encoding to UTF-8.

You can find more details at the OWASP site: https://www.owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet

score 0 · Answer 3 · edited May 23 '17 at 10:32

As long as you explicitly set the character set of the connection object, you should be fine:

mysql_set_charset('utf8', $db); // for mysql
$db->set_charset('utf8'); // for mysqli

The same problem with the character set applies to PDO prepared statements, so PDO is the recommended way, but it does not make you safe in this case.

There is no problem in using mysqli_real_escape_string() with a defined character set. Take care that you use UTF-8 for the connection object, as well as for the HTML page, this avoids a lot of problems.

The best explanation i found about this topic, is written from PHP author ircmacell, once for mysql_real_escape_string(), and once for PDO.

score 0 · Accepted Answer · edited May 23 '17 at 11:49

Just to correct a lot of misinformation in both question and answers.

Not "statement" but string literal. Escaping "SQL statements" is a straight way to injection. Only strings have to be escaped, while other query parts require distinct and absolutely different formatting and escaping will do not a slightest good to them.
All that escaping/encoding mess only connected to some marginal encodings like GBK. With UTF-8 you can use even addslashes()
Since 5.3 PDO is all right with setting client encoding, as long as it's set in DSN.

What's the correct way to escape a sql statement?

In PHP when submitting strings to the database should I take care of illegal characters using htmlspecialchars() or use a regular expression?

What are the possible exploits using SET NAMES UTF8?

None

Is mysqli_set_charset() the correct way to make the connection communicate in a specified charset?

Sure.
It have to be fed with the actual charset used on the HTML page.

What is the correct way to escape a sql statement?

4 Answers4