2

I have just learned that in order to avoid SQL injections it's better to use the prepare/execute duo:

$pdo = new PDO ('mysql:host=something;port=something dbname=something','name','pswd');
$sql = "SELECT name FROM users WHERE email = :em AND password = :pw";
$stmt = $pdo -> prepare($sql);
$stmt -> execute (array());

instead of the using:

$stmt = $pdo -> query($sql);

the question is:

In what situations does anybody use query instead, since we have a pretty quick way to secure ourselves from SQL injections, does anybody use the query method anyway? And if so, why?

Dharman
  • 30,962
  • 25
  • 85
  • 135
DrFaraday
  • 87
  • 1
  • 13
  • 1
    Tip: 3 backticks for code formatting. – tadman Feb 26 '21 at 17:04
  • 1
    Note that in PDO the placeholders should *not* have quotes. `:em` not `':em'`. – tadman Feb 26 '21 at 17:05
  • 1
    If you can select by `password` then you're not hashing your passwords correctly. You should use [`password_hash`](http://php.net/manual/en/function.password-hash.php). – tadman Feb 26 '21 at 17:06
  • There exist scenarios where database commands don't accept parameters (for example, in T-SQL a `DROP TABLE` statement cannot be parameterized; the table name must be a literal). If you need to dynamically generate statements like that, you'll still need `query` (or more likely `exec`) and `quote` (or customized escaping, even). – Jeroen Mostert Feb 26 '21 at 17:10
  • Please note that it is not the `prepare` that protects you. It's the use of bind variables. You can still do a `prepare` and `query` on a statement that you built from untrusted input, like `select * from users where password='$password'` and still be open to SQL injection. You don't want to have any code (and that SQL statement is code) that is built from data that comes from the outside. – Andy Lester Feb 26 '21 at 17:30

2 Answers2

1

Virtually never; you will know if you are in a situation you cannot use it.

Note that even if you are generating queries on the fly, you should still generate the string with the placeholders and the set of arguments independently of each other.

Acorn
  • 24,970
  • 5
  • 40
  • 69
0

I would use query method when it was safe to use a query (it was not the target of sql injection attacks), for instance: SELECT name FROM users

Oscar Soler
  • 49
  • 1
  • 4