CODEWITHSUNDEEP
phpmysql
domino
domino

Reputation: 7345

quotes around int values in mysql queries

I've gotten a habit of filtering the user submitted variable through my int function which makes sure it's a number (if not returns 0) and not quoting the variable in mysql queries.

Is that bad practice? I think I decided to do this for performance reasons. Plus I've always thought that numbers shouldn't be put in quotes.

Example:

if($perpage != $user['perpage']){
if($perpage == 50 || $perpage == 100 || $perpage == 200 ){
$DB->query("UPDATE users SET perpage=$perpage WHERE id=$user[id]", __FILE__, __LINE__);
}
}

Upvotes: 7

Views: 4003

Answers (2)

Your Common Sense
Your Common Sense

Reputation: 157839

aha! an interesting case here!

  1. You are right in general. It is always better to treat numbers as numbers, not strings

    • it makes your code more sane and consistent
    • an strict_mode setting in mysql, which won't allow you do disguise a number as a string, if turned on.

  2. But your implementation in fact allows an injection! Let's leave it for your homework to find it :)

Here is a reference for you, explaining this injection: http://php.net/language.types.type-juggling

so, I'd make your code like this

$perpage = intval($perpage);
if($perpage != $user['perpage'] && in_array($perpage,array(50,100,200) { 
  $DB->query("UPDATE users SET perpage=$perpage WHERE id=$user[id]"); 
}

Upvotes: 5

Matt Farmer
Matt Farmer

Reputation: 709

As long as the values are properly checked through the use of PHP's intval method before using them, I don't see an issue with it. You could do yourself some favors in the future by doing that if you ever have to interact with a DB that thinks quotes around int values are a syntax error. (I believe MS SQL server does that.)

Upvotes: 2

Related Questions