Difference between real_escape_string and prepare()?

Question

prepare() seems a bit tedious and unnecessary for a majority of my code. If I send a string in a SQL command, why can't I just sanitize it with real_escape_string? What's the difference? That's what I've been doing all the time and it worked well against SQL injections... Thanks.

Answer 1

Escaping is just as effective at SQL injection defense as using query parameters.

Both methods are also less effective if you fail to do them consistently.

Both methods are useful only for protecting individual values in SQL expressions. They don't support other dynamic parts of the query. For example, if you want to ORDER BY a user-specified column. Neither query parameters nor escaping functions handle that.

So basically, it is a matter of style and personal preference.

I prefer query parameters because I think this:

$sql = "INSERT INTO mytable (columna, columnb, columnc) VALUES (?, ?, ?)";
$stmt = $pdo->prepare($sql);
$stmt->execute([$a, $b, $c]);

Is more clear than this:

$sql = "INSERT INTO mytable (columna, columnb, columnc) VALUES ('".mysqli_real_escape_string($conn, $a)."', '".mysqli_real_escape_string($conn, $b)."', '".mysqli_real_escape_string($conn, $c)."')";
mysqli_query($conn, $sql);

You can't seriously be saying that fiddling with all those open-quotes/close-quotes and . string concatenation is easier than using prepare() with query parameters.

---

Re your comments about a hypothetical query() function with parameters.

First of all, it's not necessary. Using prepare() and execute() together is a small price to pay for writing secure code, and by insisting on doing it with a single function, you just sound lazy. I suppose you don't check the return value of functions that return false on error, either?

For what it's worth, it'd be easy to write a wrapper function to do both, because PHP supports varargs implicitly.

function myquery() {
  global $pdo;
  $params = func_get_args();
  $sql = array_shift($params);
  $stmt = $pdo->prepare($sql);
  $stmt->execute($params);
  return $stmt; // so we can fetch(), etc.
}