3

From experience and also having been told constantly the benefits of using prepared statements and binding my parameters, I have constantly used those two techniques in my code, however I would like to understand exactly the purpose of each of those two techiques:

From my understanding of prepared statements:

$sql = "SELECT * FROM myTable WHERE id = ".$id;
$stmt = $conn->prepare($sql);
$stmt->execute();

The previous code should create a sort of a buffer in the database with the query I proposed. Now FROM MY UNDERSTANDING (and I could be very wrong), the previous code is insecure, because the string $sql could be anything depending on what $id actually is, and if $id = 1; DROP TABLE myTable;--, I would be inserting a malicious query even though I have a prepared statement.

FROM MY UNDERSTANDING this is where binding my parameters com in. If I do the following instead:

$sql = "SELECT * FROM myTable WHERE id = :id";
$stmt = $conn->prepare($sql);
$stmt->bindParam(':id', $id);
$stmt->execute();

The database should know exactly all the parts of the sql statement before hand: SELECT these columns: * FROM myTable and WHERE id = "a variable that was input by the user", and if "a variable that was input by the user" != a variable, the query fails.

I have been told by some my understanding is correct, and by others that it is false, could someone please let me know if I am wrong, correct, or missing something? And elaborate as much as you want, all feedback is greatly appreciated!

Webeng
  • 7,050
  • 4
  • 31
  • 59
  • The first piece of code protects you from nothing as you are still passing `$id` which could come from `$_POST['id']` directly into the query – RiggsFolly May 09 '16 at 19:13
  • @RiggsFolly so my assumption would be correct that prepared statements need `bindParam()` to be secure? – Webeng May 09 '16 at 19:14
  • You must use either `?,?` or your second mechanism – RiggsFolly May 09 '16 at 19:14
  • Well you can also use `$stmt->execute( [':id' => $id] );` but that basically does the bind invisibly – RiggsFolly May 09 '16 at 19:17
  • @Webeng yes, prepared statements need bound params to be secure, and in most cases to even be useful. In your first example, you are preparing a query with something to the effect of "WHERE id = 5"; so it may help performance when running that exact query, but is of no benefit for the same query but `WHERE id = 4`. Preparing allows the server to parse the query once, rather than every time it is executed. – Uueerdo May 09 '16 at 19:18
  • 1
    Maybe the bit about preparing statements you are also missing is that if you want to load data in a loop you can `->prepare()` once and `bind; execute()` many times. Thus compiling, and optimizing a query only once but utilizing it 100 times – RiggsFolly May 09 '16 at 19:20
  • A prepared statement is a bit like creating a temporary Stored Proceedure. Once compiled cleanly, it can be used many times by just presenting a new set of variables to the stored proceedure. By temporary I mean it exists only for the lifetime of the connection i.e. the lifetime of one execution of your PHP script – RiggsFolly May 09 '16 at 19:23
  • One minor point: In your second example, if `$id` was `"1; DROP TABLE myTable;--"`, your query would not _fail_; it would execute successfully and return zero rows (unless myTable happened to have a row with an id of `"1; DROP TABLE myTable;--"`). – Don't Panic May 09 '16 at 19:28
  • @RiggsFolly great point about optimizing the query and re-binding it for optimization. – Webeng May 09 '16 at 19:34

1 Answers1

6

You're correct that the first case is insecure. It's important to understand though, that preparing a statement only has value if you are using variable data, and/or executing the same query repeatedly. If you are executing plain statements with no variables, you could simply do this:

$sql = "SELECT * from myTable WHERE this_column IS NOT NULL";
$result = $conn->query($sql);

And end up with a PDOStatement object to work with, just like when you use PDO::exec().

For your second case, again, you're largely correct. What's happening is the variable passed to the database is escaped and quoted (unless you specify otherwise with the third argument to PDOStatement::bindParam(), it's sent as a string which is fine for most cases.) So, the query won't "fail" if bad data is sent. It behaves exactly as if you had passed a valid number that didn't exist as an ID in the database. There are, of course, some edge cases where you are still vulnerable even with a correctly prepared statement.

Also, to make life easier, you can use prepared statements like this, to do implicit binding:

$sql = "SELECT * FROM myTable WHERE id = :id";
$stmt = $conn->prepare($sql);
$stmt->execute([":id"=>$id]);

Or even like this, with un-named parameters:

$sql = "SELECT * FROM myTable WHERE id = ?";
$stmt = $conn->prepare($sql);
$stmt->execute([$id]);

Naturally, most of this has been explained in the comments while I was typing up the answer!

miken32
  • 42,008
  • 16
  • 111
  • 154