Page 1 of 1

MySQL query using (int)

Posted: Mon Jan 05, 2009 3:20 pm
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.

Re: MySQL query using (int)

Posted: Mon Jan 05, 2009 3:35 pm
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

Re: MySQL query using (int)

Posted: Mon Jan 05, 2009 4:25 pm
by Syntac
If $sid is supposed to be an int anyway, typecasting is a very good idea.

Re: MySQL query using (int)

Posted: Mon Jan 05, 2009 5:29 pm
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.

Re: MySQL query using (int)

Posted: Tue Jan 06, 2009 1:38 am
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

Re: MySQL query using (int)

Posted: Tue Jan 06, 2009 5:13 am
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;
}

Re: MySQL query using (int)

Posted: Tue Jan 06, 2009 6:33 am
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

Re: MySQL query using (int)

Posted: Tue Jan 06, 2009 6:46 am
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.

Re: MySQL query using (int)

Posted: Tue Jan 06, 2009 6:56 pm
by josh
? You check for a 0 value and redirect the user to the page where they can make a proper selection

Re: MySQL query using (int)

Posted: Tue Jan 06, 2009 7:03 pm
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.

Re: MySQL query using (int)

Posted: Tue Jan 06, 2009 7:09 pm
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?