mysql: compare integers with strings and return true

Question

Is there any operator equivalent to === in MySQL which checks value as well the datatype?

I have a u_id field in a database which is int datatype

now from login page where user enter user id and during logicheck I write below code

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

This returns a row

now when enter 2.0 then it also return the row which I don't want, what I need is when 2.0 is written, query does not return any row.

gettype($loginCode) return string....

it's strange how a integer type(u_id) field can compare with string type in mysql? Please suggest me solution or any better way

remi bourgarel · Accepted Answer · 2010-03-03 13:55:48Z

3

you can cast your data and compare them as string :

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

answered Mar 3, 2010 at 13:55

remi bourgarel

9,4494 gold badges46 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Andomar Over a year ago

+1 Nice, that would force MySQL to compare strings. For a large table, this could hurt performance, since an index on u_id could not be used. But tbl_user is unlikely to be very large :)

xkeshav Over a year ago

it returns following error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'n varchar(3))=2 LIMIT 0, 30' at line 1

Andomar Over a year ago

Appearantly you can't cast to varchar. Try trim(cast(u_id as nchar(12))) instead

xkeshav Over a year ago

SELECT * FROM tbl_user WHERE TRIM(CAST(u_id AS nchar(3)))=2.0 Return still return same result

Andomar · Accepted Answer · 2010-03-03 13:55:49Z

2

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]+$'

answered Mar 3, 2010 at 13:55

Andomar

239k55 gold badges387 silver badges412 bronze badges

2 Comments

xkeshav Over a year ago

there in not LoginCode, its $loginCode how do i add php varaiable inside <>

Andomar Over a year ago

$sql="SELECT * FROM tbl_user WHERE u_id=".$loginCode." AND '".$loginCode."' RLIKE '^[0-9]+$'";

Collectives™ on Stack Overflow

mysql: compare integers with strings and return true

2 Answers 2

4 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related