mysql compare integer with strings and return true

Question

is there any operator equivalent to === in mysql which checks value as well the datatype.

actually i have a u_id field in a MYSQL database which is int type

now from login page where user enter user id and during logicheck i write these code

$loginCode = mysql_real_escape_string($_POST[userCode]);
$sql=SELECT * FROM tbl_user WHERE u_id=".$loginCode ;
$result=$db->query($sql);

this will return a row

now if i enter 2.0 then it also return a row(that i don't want) i want that when 2.0 is written ..mysql does not return any row .

gettype($loginCode) return string.... its strange how a integer type(u_id) field can compare with string type in mysql please suggest me solution or any better way

Answer 1

You can try convert value from php dynamicly

$loginCode = mysql_real_escape_string($_POST[userCode]);
$sql="SELECT * FROM tbl_user WHERE u_id=".(int)$loginCode ;
$result=$db->query($sql);

Answer 2

you can cast your data and compare them as string :

"SELECT * FROM tbl_user WHERE CAST(u_id as nvarchar(3))=".$loginCode

Answer 3

All SQL databases will implicitly convert a float to an int.

You could ensure that your login code contains nothing but numbers with a regular expresison. For example:

WHERE u_id = <LoginCode> AND <LoginCode> RLIKE '^[0-9]+$'