php - how safe are PDO prepared statements

Question

Started using PDO prepared statements not too long ago, and, as i understand, it does all the escaping/security for you.

for example, assuming $_POST['title'] is a form field.

$title = $_POST['title'];
$query = "insert into blog(userID, title) values (?, ?)"
$st = $sql->prepare($query);
$st->bindParam(1, $_SESSION['user']['userID'], PDO::PARAM_INT);
$st->bindParam(2, $title);
$st->execute();

Is this really safe? Do i have to do anything else? what else do i have to take into consideration?

Thanks.

Answer 1

Strictly speaking, there's actually no escaping needed, because the parameter value is never interpolated into the query string.

The way query parameters work is that the query is sent to the database server when you called prepare(), and parameter values are sent later, when you called execute(). So they are kept separate from the textual form of the query. There's never an opportunity for SQL injection (provided PDO::ATTR_EMULATE_PREPARES is false).

So yes, query parameters help you to avoid that form of security vulnerability.

Are they 100% proof against any security vulnerability? No, of course not. As you may know, a query parameter only takes the place of a single literal value in an SQL expression. You can't make a single parameter substitute for a list of values, for example:

SELECT * FROM blog WHERE userid IN ( ? );

You can't use a parameter to make table names or column names dynamic:

SELECT * FROM blog ORDER BY ?;

You can't use a parameter for any other type of SQL syntax:

SELECT EXTRACT( ? FROM datetime_column) AS variable_datetime_element FROM blog;

So there are quite a few cases where you have to manipulate the query as a string, prior to the prepare() call. In these cases, you still need to write code carefully to avoid SQL injection.