Should I use bindParam(), bindValue(), or execute() for PDO prepared statements

Question

I read this question (Should I use bindValue() or execute(array()) to avoid SQL injection?) and found that both execute( array() ) and bindParam() both prevent SQL injections when working with PDO and prepared statements.

However I also recall reading somewhere that execute() automatically treats every variable as a string, regardless if it's an integer. My question is surely this is a security issue for anything but string variables?

For example, if I was to get the id of a user where it equals 1 via a prepared query, however the parameters passed through execute will be treated as a string not an integer.

Obviously this script will have no method of user input, and will only support user input such as updating a profile, post, etc.

I am learning PDO so I'm trying to fully understand how to write secure PDO queries (or even better, functions) that are preventing/protecting against SQL injection and other security flaws.

Thanks

Answer 1

Security wise, there is not a single problem with sending parameters into execute(). So, your concern is superfluous. Given strings are treated safely AND we are sending all our parameters as strings, we can logically conclude that all parameters are treated safely (note that I don't blame your question. It's always better to ask than sorry. I am just making things straight).

The only possible issue with treating all parameters as strings is on the database server side. Yet most database servers allow loose typing in SQL, means any data literal will be recognized and treated properly, despite being sent as a string. There are only few edge cases when it won't work. Here is an excerpt from my PDO tutorial on the matter:

However, sometimes it's better to set the data type explicitly. Possible cases are:

• LIMIT clause in emulation mode or any other SQL clause that just cannot accept a string operand.
• complex queries with non-trivial query plan that can be affected by a wrong operand type
• peculiar column types, like BIGINT or BOOLEAN that require an operand of exact type to be bound (note that in order to bind a BIGINT value with PDO::PARAM_INT you need a mysqlnd-based installation).

As a conclusion, given you already turned emulation off, you can use the execute() method virtually all the time.