mysqli_real_escape_string, should I use it?

Question

I want to eliminate sql injection, should I use mysqli_real_escape_string() or is it clear in mysqli? For example

$nick = mysqli_real_escape_string($_POST['nick'])

score 23 · Accepted Answer · edited May 23 '17 at 12:24

23

You should use prepared statements and pass string data as a parameter but you should not escape it.

This example is taken from the documentation:

/* 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();
}

Note that the example does not call mysqli_real_escape_string. You would only need to use mysqli_real_escape_string if you were embedding the string directly in the query, but I would advise you to never do this. Always use parameters whenever possible.

Related

How can I prevent SQL injection in PHP?

edited May 23 '17 at 12:24

Community

1
1

answered Dec 23 '12 at 14:52

Mark Byers

811,555
193
1,581
1,452

3

I don't understand prepared statements absolutely, but thanks – dontHaveName Dec 23 '12 at 15:13
4

The first thing you should do tomorrow is understand prepared statements, before you can open your presents. – Mark Byers Dec 23 '12 at 15:16
yes, I have to..but If I should use it in each situation with user inputs code will be so long, and just with mysqli_real_escape_string it is so simplier – dontHaveName Dec 23 '12 at 15:26
@dontHaveName: But the risk of leaving an SQL injection is much higher of you don't use parameters. You only have to make one mistake in one query and your entire database could be compromised. If you are willing to take that risk to save a few keystrokes, I guess that's your choice. – Mark Byers Dec 23 '12 at 15:28
Thank you so much. Now I understand it :). By the way are there any other "hacks" that could endanger my web? – dontHaveName Dec 23 '12 at 18:25
@dontHaveName: Yes, lots. Read about [cross-site scripting](http://en.wikipedia.org/wiki/Cross-site_scripting). – Mark Byers Dec 23 '12 at 19:07

Your Common Sense · Answer 2 · 2012-12-23T15:03:05.353

Yes, you can use mysqli_real_escape_string to format strings, if you're going to implement your own query processor, using placeholders or some sort of query builder.

If you're planning to use bare API functions in your code (which is obviously wrong practice but extremely popularized by local folks) - better go for the prepared statements.

Anyway, you can't "eliminate sql injection" using this function alone. mysql(i)_real_escape_string functions do not prevent injections and shouldn't be used for whatever protection.

mysqli_real_escape_string, should I use it?

2 Answers2

Linked