security difference between (double) $user_input a

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.

Answer 2

Thank you for the great question.

As a matter of fact, you could use both methods at once.

Most people do confuse the idea of a prepared statement in general with [very limited] implementation offered by major DBMS. While the latter can be questioned, the former is indeed the only way.

Take a look at the example. Let's run your query using safeMysql:

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

It performs the very string formatting like your code does, but does it internally. Why? Because it doesn't matter how it's implemented internally (by means of native prepared statement or manual formatting), but it is essential to use a prepared statement to assembly your query either way.

There are some essential points about prepared statements, overlooked by most people:

1. it makes formatting being always complete (Although in your example you're doing the right thing and make complete formatting, it is still very easy to slip into incomplete one, like this:

   $colname = "`$colname`";
   

2. your formatting being always the right one. it won't let you do something like

   $colname = "`" .mysql_real_escape_string($colname) ."`";
   

   which would be useless and lead you to injection

3. it will make formatting obligatory. With assembling a query your current way it is very easy to overlook a variable or two.

4. it will do proper formatting as close to the query execution as possible. That's the point of great importance, as
   • it will not spoil your source variable (what if query failed and you want to echo it back?)
   • it won't let you to move formatting code somewhere away from the query, which may lead to fatal consequences.
   • after all, it will make your code dramatically shorter, without all that boring manual formatting!

That's the real benefits of a prepared statements, which guarantee the safety and thus made them so overly popular. While that thing with server-side preparation, although being quite smart, is just a particular implementation.

Also, taking the idea of a prepared statement as a guide, one can create a placeholder for the everything that may be added into query (an identifier or an array for example), making it real safe and convenient to use.

Keeping all the things in mind one have to implement the very idea of a prepared statement in their DB access library, to make the code safe and short.

Just a couple of examples from safeMysql:

$name = $db->getOne('SELECT name FROM table WHERE id = ?i',$_GET['id']);
$data = $db->getInd('id','SELECT * FROM ?n WHERE id IN ?a','table', array(1,2));
$data = $db->getAll("SELECT * FROM ?n WHERE mod=?s LIMIT ?i",$table,$mod,$limit);

$ids  = $db->getCol("SELECT id FROM tags WHERE tagname = ?s",$tag);
$data = $db->getAll("SELECT * FROM table WHERE category IN (?a)",$ids);

$data = array('offers_in' => $in, 'offers_out' => $out);
$sql  = "INSERT INTO stats SET pid=?i,dt=CURDATE(),?u ON DUPLICATE KEY UPDATE ?u";
$db->query($sql,$pid,$data,$data);

Just try the same with conventional mysql(i) and see the amount of code it takes you.

You may note that with usable prepared statements you have to mark them with type, because there are more types than just simple string, and it's the only reliable way to tell a driver how to format your variable.

Answer 3

I believe they are equally secure from security point of view, but using prepare does not only make your SQL secure, but also make you FEEL secure. You cannot trust yourself to manually escape and convert to proper type all the time. If you write 10,000 different SQL queries, you will tend to forget to escape one or two.

So in conclusion, prepare is a better habit to fight against SQL injection. Putting PHP variable directly to the SQL query make me feel uneasy when sleeping at night.