I know that PDO's prepare() method checks datatypes and quotes content
as needed (e.g. strings get quotes, ints don't). But according to what
people say about it, it prevents SQL injection. If this is true, then
what, besides proper value quoting, does it do to protect SQL statements
from being SQL injections? Or is proper quoting all that's necessary?
When one quotes inputs to a SQL query, they are merely attempting to escape input in a manner that prevents misinterpretation of the input data as part of the procedural code of the query. There are several considerations that have to be taken into account (e.g., character set of input, character set of SQL statement, etc.) One still has to be very cautious when taking this approach, as a misconfigured character set or unique syntax format could expose the script to SQL injections.
In contrast, prepared statements are (typically) first created as templates that are sent to the database to be 1) checked and 2) preprocessed and 3) saved to be run in the future (or, in some compiled languages, the preprocessing can occur in the language itself.) The placeholders are then clearly marked as user data and cannot be confused with the SQL code already prepared by the DB. Prepared statements ensure that the inputs cannot be interpreted as SQL code because the SQL code has already been generated and just awaits the input parameters.
Adam
