2

I wanted to know if prepared query is as safe as one that is not prepared. Below are two examples, one for SELECT and one for UPDATE. First line is the not prepared and second is the prepared query.

SELECT examples:

$userDetails = $connection->query("SELECT * FROM Users WHERE Name='$username'")->fetch();

$userDetails = $connection->prepare('SELECT * FROM Users WHERE Name=?');
$userDetails->execute(array($username));
$userDetails = $userDetails->fetch();

UPDATE examples:

$query = $connection->query("UPDATE Users SET SessionID='$sessionID' WHERE Name='$username'")->execute();

$query = $connection->prepare("UPDATE Users SET SessionID=? WHERE Name=?");
$query->execute(array($sessionID, $username));

Should I use the long way of doing it or is the one that takes only one line to do worse?

Jani
  • 47
  • 7

3 Answers3

2

From the documentation

The prepared statement execution consists of two stages: prepare and execute. At the prepare stage a statement template is sent to the database server. The server performs a syntax check and initializes server internal resources for later use.

Repeated execution

A prepared statement can be executed repeatedly. Upon every execution the current value of the bound variable is evaluated and sent to the server. The statement is not parsed again. The statement template is not transferred to the server again.

A prepared statement has its advantages of checking syntax and repeated execution. Prepared statements are especially preferred when your sql is generated dynamically using variables

you can read more in this SO post MySQLi: query VS prepare

Community
  • 1
  • 1
Veeru
  • 4,936
  • 2
  • 43
  • 61
1

No, they are not the same with respect to security.

The versions where you copy variables into the string are risking SQL injection vulnerabilities. It depends how you handle the variables. It is safe if you process them with PDO::quote() before copying them into the SQL string, but if your developers forget to do this, it's unsafe.

It's a commonly held myth that prepared statements are slower. They're not -- in fact, they can be faster, at least from the perspective of the RDBMS.

However, the extra code run in your PHP application has some overhead, so there is some performance penalty in the PHP runtime. But it's a pretty small amount of overhead. I wouldn't worry about it.

Just use prepared statements!

  • They're easier to code correctly.
  • They're as safe as quoting, if not safer.
  • They have no disadvantage for SQL execution performance.
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • I was also wondering if the long way is also slower for the server to run, but seems I got answer to that as well. Thanks! – Jani Oct 23 '14 at 08:23
0

Use the quick version for unique statement that will not be used regulary with same structure.

Use the long version in loops for example.

David Ansermot
  • 6,052
  • 8
  • 47
  • 82