（double）$ user_input和bind_param（'d'，$ user_input）之间的安全性差异

Question

Lets say I were to perform a prepared statement like this:

$qry->prepare('UPDATE table_name SET column1 = ? string_column = ? WHERE column3 = ? AND column4 = ?');
$qry->bind_param('sbid', $string, $blob, $int, $double);

$int = 'non int value'; /* gives 0 in the database */
$blob = 'some string';
$string = 'another string';
$double = $double;

$qry->execute();
$qry->close();

Let's just say I only wanted to perform the query once, I just used the prepared statement in the name of security. From what I've been reading - its more overhead to use prepared queries only once, and that amounts to compromising performance for the security benefits. That being said - what would be the performance/security difference in doing the same query one time like this.

$int = (int) $int;
$blob = "'" .mysql_real_escape_string($blob) ."'";
$string = "'" .mysql_real_escape_string($blob) ."'";    
$double = (double) $double;

$db->query("UPDATE SET column1 = $int, column2 = $blob WHERE column3 = $string AND column4 = $double ");

PS. I am not interested on how Prepared statements improve the performance but about the security and speed difference for a single query.

Answer 1

There is quite a lot to that. Some random points

• Single use prepared statements do impose a (more than theoretical) performance penalty, which is higher, if a lot of connections exist to the MySQL server. (Think: Context switches)
• But you should not run a DB server so close to its limits, that this makes the difference.
• But you not always have the choice (Think: shared hosting)

or:

• There are some (or even many) cases, where prepared statements do not offer a security benefit - there is a lot of business logic, where no user-generated data is involved (Think: Jointables, that only carry IDs) or where the user-generated data has to be validated beforehand for other reasons (Think: Price calculations, memcached lookups, ...)
• But selecting one of many styles for each single query results in unmaintainable code.
• But it is sometimes unavoidable (Think: There is no prepared query support for the IN ( ) construct)

often overlooked:

• Prepared queries sometimes make it harder to be RDBMS-agnostic
• But prepared queries offer the best know protection against SQL injection.

My favorite:

• it is common advice to simply always use prepared queries
• But the majority of living things on this planet would advise you to eat feces or rotting organic substance.

So the choice of style often has to be made on a case-by-case basis. We have adopted the way of encapsulating all DB access including parameter management in a standardized library, that is simply require()ed, so you can drop-in replace with prepared queries, escaping or whatever you want and your RDBMS supports.