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'])

Answer 1

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.

Answer 2

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?