5

Could anyone give me examples of how to use parameterized queries with MySQL/PHP please?

shin
  • 31,901
  • 69
  • 184
  • 271

2 Answers2

8

A parameterized query is essentially a query which abstracts away all the input. This has several good side effects, like making all input harmless (ie. no harmful injections are possible) and making it faster when used repeatedly, since it is pre-parsed and compiled, so the engine knows how to apply the input given. An example in pure mysql is:

PREPARE qry FROM "INSERT INTO tbl VALUES (?)";

The statement is now compiled and cached, and can be executed repeatedly without needing to recompile and interpret it:

SET @var = "some input";
EXECUTE qry USING @var;
SET @var = "some other input";
EXECUTE qry USING @var;

When used in PHP, it's usually like this (shortened):

$stmt = prepare('INSERT INTO tbl VALUES(?)');
execute($stmt, array("some input"));
execute($stmt, array("some other input"));
execute($stmt, array("some more input"));
Tor Valamo
  • 33,261
  • 11
  • 73
  • 81
  • I don't fully understand how this stops SQL injection. I'm not doubting that it does, but could you explain it further? – Kyle Noland May 19 '10 at 04:01
  • 3
    Because the query is pre-compiled before any of the parameters are put into it. And so even if one parameter was `"; DELETE FROM stuff;` or something like that, it would just be inserted into wherever the compiled statement says, as a complete string. It doesn't pass through the parser, because the main statement has already been parsed. You can compare it to `eval()` like: `eval("new Class($data);")`. Consider data being `);delete_all_my_files(`. Precompiled statements basically means that you avoid the "eval" problem. – Tor Valamo May 19 '10 at 10:34
4
PREPARE stmt_name FROM "SELECT name FROM Country WHERE code = ?";

SET @test_parm = "FIN";

EXECUTE stmt_name USING @test_parm;

Source: MySQL Dev: Prepared Statements

Bryan
  • 78
  • 6
Daniel Vassallo
  • 337,827
  • 72
  • 505
  • 443