Are prepared statements necessary FOR ME?

Question

I always check/limit/cleanup the user variables I use in database queries

Like so:

$pageid = preg_replace('/[^a-z0-9_]+/i', '', $urlpagequery); // urlpagequery comes from a GET var

$sql = 'SELECT something FROM sometable WHERE pageid = "'.$pageid.'" LIMIT 1';

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

if ($stmt && $stmt->num_rows > 0) {

    $row = $stmt->fetch_assoc();

    // do something with the database content

}

I don't see how using prepared statements or further escaping improves anything in that scenario? Injection seems impossible here, no?

I have tried messing with prepared statements.. and I kind of see the point, even though it takes much more time and thinking (sssiissisis etc.) to code even just half-simple queries.

But as I always cleanup the user input before DB interaction, it seems unnecessary

Can you enlighten me?

Answer 1

The question would be how you defined "improve" in this context. In this situation I would say that it makes no difference to the functionality of the code.

So what is the difference to you? You say that this is easier and faster for you to write. That might be the case but is only a matter of training. Once you're used to prepared statements, you will write them just as fast.

The difference to other programmers? The moment you share this code, it will be difficult for the other person to fully understand as prepared statements are kind of standard (or in a perfect world would be). So by using something else it makes it in fact harder to understand for others.

---

Talking more about this little piece of code makes no sense, as in fact it doesn't matter, it's only one very simple statement. But imagine you write a larger script, which will be easier to read and modify in the future?

$id = //validate int
$name = //validate string
$sometext = //validate string with special rules

$sql = 'SELECT .. FROM foo WHERE foo.id = '.$id.' AND name="'.$name.'" AND sometext LIKE "%'.$sometext.'%"';

You will always need to ask yourself: Did I properly validate all the variables I am using? Did I make a mistake?

Whereas when you use code like this

$sql = $db->prepare('SELECT .. FROM foo WHERE foo.id = :id AND name=":name" AND sometext LIKE "%:sometext%"');
$sql->bind(array(
    ':id' => $id,
    ':name' => $name,
    ':sometext' => $sometext,
));

No need to worry if you done everything right because PHP will take care of this for you.

Of course this isn't a complex query as well, but having multiple variables should demonstrate my point.

---

So my final answer is: If you are the perfect programmer who never forgets or makes mistakes and work alone, do as you like. But if you're not, I would suggest using standards as they exist for a reason. It is not that you cannot properly validate all variables, but that you should not need to.

Answer 2

You will be better off using prepared statement consistently.

Regular expressions are only a partial solution, but not as convenient or as versatile. If your variables don't fit a pattern that can be filtered with a regular expression, then you can't use them.

All the "ssisiisisis" stuff is an artifact of Mysqli, which IMHO is needlessly confusing.

I use PDO instead:

$sql = 'SELECT something FROM sometable WHERE pageid = ? LIMIT 1';
$stmt = $conn->prepare($sql);
$stmt->execute(array($pageid));

See? No need for regexp filtering. No need for quoting or breaking up the string with . between the concatenated parts.

It's easy in PDO to pass an array of variables, then you don't have to do tedious variable-binding code.

PDO also supports named parameters, which can be handy if you have an associative array of values:

$params = array("pageid"=>123, "user"=>"Bill");
$sql = 'SELECT something FROM sometable WHERE pageid = :pageid AND user = :user LIMIT 1';
$stmt = $conn->prepare($sql);
$stmt->execute($params);

If you enable PDO exceptions, you don't need to test whether the query succeeds. You'll know if it fails because the exception is thrown (FWIW, you can enable exceptions in Mysqli too).

You don't need to test for num_rows(), just put the fetching in a while loop. If there are no rows to fetch, then the loop stops immediately. If there's just one row, then it loops one iteration.

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {

    // do something with the database content

}

Prepared statements are easier and more flexible than filtering and string-concatenation, and in some cases they are faster than plain query() calls.

Answer 3

Prepared statements can sometimes be faster. But from the way you ask the question I would assume that you are in no need of them.

So how much extra performance can you get by using prepared statements ? Results can vary. In certain cases I’ve seen 5x+ performance improvements when really large amounts of data needed to be retrieved from localhost – data conversion can really take most of the time in this case. It could also reduce performance in certain cases because if you execute query only once extra round trip to the server will be required, or because query cache does not work.

Brought to you faster by http://www.mysqlperformanceblog.com/

I don't see how using prepared statements or further escaping improves anything in that scenario?

You're right it doesn't.

P.S. I down voted your question because there seems little research made before you asked.