Correct usage of $mysqli->real_escape_string in PHP

Question

When I call $mysqli->real_escape_string($str), I get back a safe string that I can use in a query. It has all special symbols that can lead to SQL Injection Attack escaped.

My question is - what is the correct way to use it in a query later? Do I single quote or double quote this safe string?

For example,

$safe_title = $mysqli->real_escape_string($_POST['safe_title']);

Now do I do this:

$r = $mysqli->query("SELECT * FROM articles WHERE title='$safe_title'");

Or this:

$r = $mysqli->query('SELECT * FROM articles WHERE title="'.$safe_title.'"');

Sometimes I do one, sometimes I do the other, but this one time when I did one of these and typed a bunch of garbage in input, I got an SQL query error somehow. I realized I'm doing it wrong and all my code is probably vulnerable. That's why I'm asking this question here.

Don't use real_escape_string. Better read about prepared statements php.net/manual/en/mysqli.prepare.php — Jordi Kroon
– Jordi Kroon, Commented Nov 15, 2015 at 22:11
@dragon - they're not - look carefully - one uses single quotes - the other uses double quotes! — bodacydo
– bodacydo, Commented Nov 15, 2015 at 22:11
@JordiKroon Thanks I never heard of that. Checking it out. Can you quickly comment why that is better? Like high level overview. — bodacydo
– bodacydo, Commented Nov 15, 2015 at 22:12
as far as mysql is concerned the passed string(querry) is identical — user557846
– user557846, Commented Nov 15, 2015 at 22:12

Michael Martone · Accepted Answer · 2015-11-16 03:23:55Z

1

You would do this: $r = $mysqli->query("SELECT * FROM articles WHERE title='$safe_title'");

But as @JordiKroon pointed out prepared statements are preferred.

$stmt = $mysqli->prepare("SELECT * FROM articles WHERE title=?");
$stmt->bind_param("s", $safe_title);
$stmt->execute();

$result = $stmt->get_result();
while ($row = $result->fetch_array(MYSQLI_NUM))
{
    foreach ($row as $r)
    {
        print "$r ";
    }
    print "\n";
}

answered Nov 16, 2015 at 3:23

Michael Martone

1345 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Correct usage of $mysqli->real_escape_string in PHP

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related