MySQL: Searching number column with string parameter

Question

Let's say we have a record in table 'orders' with id=1. This query:

SELECT * FROM 'orders' WHERE id = 'abc1'

won't return mentioned record. But this query:

SELECT * FROM 'orders' WHERE id = '1abc'

will return the record with id=1. This is because when MySQL converts string to number 'abc1' becomes 0, but '1abc' becomes 1. Is there a nice way to make MySQL search strictly for records with id from query, i.e. not return the record with id=1 in both mentioned cases?

You could make the id as a varchar but then it will not serve for the actual purpose of an ID.. — auicsc
– auicsc, Commented Apr 22, 2013 at 17:59
Where does 'abc1' or '1abc' come from and why aren't the search parameters validated and sanitized? What purpose would a query like SELECT * FROM 'orders' WHERE id = '123IReallyDoNotCareIfThisIsARealNumberOrNot' serve? — ZZ-bb
– ZZ-bb, Commented Apr 22, 2013 at 18:17

RelevantUsername · Accepted Answer · 2013-04-22 18:01:35Z

6

What about using :

SELECT * FROM 'orders' WHERE id LIKE '1abc' COLLATE utf8_bin

or even

SELECT * FROM 'orders' WHERE STRCMP(id, '1abc') = 0;

answered Apr 22, 2013 at 18:01

RelevantUsername

1,3408 silver badges14 bronze badges

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

2 Comments

Gordon Linoff Over a year ago

+1 . . . The advantage of the first method is that it will use an index on id.

Wrikken Over a year ago

Superior to mine indeed, that first one, voting for this due to key usage, deleting mine ;)

Bassem Shahin · Accepted Answer · 2018-12-08 10:40:46Z

0

I handled it with PHP before submitting the SQL query

$idTest = '1abc';
if (is_numeric($id){
    $query = "SELECT * FROM 'orders' WHERE id = '$idTest'"
}

This will prevent submitting queries if the $idTest has a string

answered Dec 8, 2018 at 10:40

Bassem Shahin

7067 silver badges14 bronze badges

Collectives™ on Stack Overflow

MySQL: Searching number column with string parameter

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related