MySQL query using (int)

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
mikebr
Forum Contributor
Posts: 243
Joined: Sat Sep 28, 2002 7:05 am

MySQL query using (int)

Post by mikebr »

Hi, I recently came across a suggestion to insert (int) in a MySQL query before the id when the $sid is expected to be an integer but was wondering what the expected norm as far as security and reliability is concearned. I used to just check that an id variable actually had a value and that the value was numeric (although yes it should be an integer), so I guess my question is would this be considered better practice to use (int) as shown or is it considered better to check the id is an integer before it gets to this stage:

Code: Select all

<?php
$qry = ("SELECT * FROM `service` WHERE `sid`=" . (int) $sid);
$rslt = @mysql_query($qry) or die (mysql_error());
?>
I guess the advantage with this is it the value is a string it would be then converted to an integer but I have not had any issues with an id being passed as a string 'that I know of anyway', MySQL seems to take over.

Thanks in advance.
User avatar
andyhoneycutt
Forum Contributor
Posts: 468
Joined: Wed Aug 27, 2008 10:02 am
Location: Idaho Falls

Re: MySQL query using (int)

Post by andyhoneycutt »

Your query that you are building in php simply gets passed as a string to mysql. mysql will do it's own interpreting from there. forcing a typecast on the value could conceivably result in wonky behavior:

Code: Select all

echo (int) "Abc", "\n"; // 0
echo (int) "1", "\n"; // 1
echo (int) "1Abalksdf", "\n"; // 1
echo (int) "01C1", "\n"; // 1
echo (int) 'A', "\n"; // 0
echo (int) 1.123141231, "\n"; // 1
I would say you're pretty safe doing the typecast unless you have '0' as an ID. The way php type converts a string to a number (very simply put) is by reading the string from left to right, and going until it finds a character other than a number. This results in "01C1" coming out to 1. 01 == 1.

It's excellent practice to validate your data types before shoving them at the database, to make a long story short.

-Andy
User avatar
Syntac
Forum Contributor
Posts: 327
Joined: Sun Sep 14, 2008 7:59 pm

Re: MySQL query using (int)

Post by Syntac »

If $sid is supposed to be an int anyway, typecasting is a very good idea.
mikebr
Forum Contributor
Posts: 243
Joined: Sat Sep 28, 2002 7:05 am

Re: MySQL query using (int)

Post by mikebr »

Thanks, I guess I will still do some checking before it gets to the query stage and then use the (int) on expected integers for auto increment fields.
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: MySQL query using (int)

Post by josh »

I would always use type casting or mysql_real_escape_string at query time, in case the code is moved and you forget to check if its an int. If you put the typecast inline you dont have to worry abuot if it was escaped earlier
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: MySQL query using (int)

Post by VladSun »

I don't like how PHP typecasting to numeric is done:

Code: Select all

echo (int)'123a'; 
echo (int)'a123';
I use a simple function that will throw an exception in case typecasting is invalid:

Code: Select all

function atoi($n)
{
    if ((string)((int)$n) != (string)$n)
        throw new Exception('Parsing integer failed.');
    
    return (int)$n;
}
There are 10 types of people in this world, those who understand binary and those who don't
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: MySQL query using (int)

Post by josh »

So if someone gets a string in an input you're throwing an exception? Most of the time PHP can recover gracefully (int)"52test" === 52; (int)"test" === 0 // better then throwing an exception IMO
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: MySQL query using (int)

Post by VladSun »

jshpro2 wrote:So if someone gets a string in an input you're throwing an exception? Most of the time PHP can recover gracefully (int)"52test" === 52; (int)"test" === 0 // better then throwing an exception IMO
How do you differ a real zero value and a parsing error value (zero too)?
I don't like "gracefully processed" (lazy ;) ) stuff - HTML vs. XHTML for example.
There are 10 types of people in this world, those who understand binary and those who don't
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: MySQL query using (int)

Post by josh »

? You check for a 0 value and redirect the user to the page where they can make a proper selection
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: MySQL query using (int)

Post by VladSun »

jshpro2 wrote:? You check for a 0 value and redirect the user to the page where they can make a proper selection
? And what if a value of 0 is a valid value - e.g. id=0 exists in the DB ?

PS: In fact, if intval() had returned false instead of zero on failure, I would have used it.
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: MySQL query using (int)

Post by VladSun »

jshpro2 wrote:? You check for a 0 value and redirect the user to the page where they can make a proper selection
Also, what's the real difference between checking for a zero value and catching an exception?
There are 10 types of people in this world, those who understand binary and those who don't
Post Reply