MySQL query enclosing numbers in strings

Question

Suppose I have a query

SELECT * FROM table WHERE x > y;

where "y" is a user-input value. I'd like to enclose y in quotes. So far as I've tested, the query works properly when the quotes are added. Is the behaviour defined? Is it known to result in an efficiency drop?

Note that the query is an example fabricated for simplicity. Also, this is not an attempt to deal with SQL injection.

Is the behaviour defined, and is it known to result in an efficiency drop? — wyatt
– wyatt, Commented Dec 7, 2011 at 6:27
Enclosing values in quotes isn't what protects against injection. — outis
– outis, Commented Dec 7, 2011 at 6:28
As I mentioned, I'm not trying to protect against injection. It was just a simple way to frame the question. — wyatt
– wyatt, Commented Dec 7, 2011 at 6:33

Mithun Sasidharan · Accepted Answer · 2011-12-07 06:27:15Z

3

If the field that you are trying to compare with is a numeric field ie int, then all the possible combinations must work in mysql

select * from users where id = 20;

select * from users where id = "20";

select * from users where id = '20';

Refer the MySQL DOC for more depth

answered Dec 7, 2011 at 6:27

Mithun Sasidharan

21k10 gold badges38 silver badges53 bronze badges

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

Comments

outis · Accepted Answer · 2011-12-07 06:27:08Z

0

The conversion behavior of MySQL when comparing different types is well-defined. When a string and a number are compared, both are converted to floats. It's outlined in the manual, § 11.2.

answered Dec 7, 2011 at 6:27

outis

77.7k23 gold badges155 silver badges228 bronze badges

Comments

ajreal · Accepted Answer · 2011-12-07 06:24:22Z

-1

IMO, you can try :-

cast( "-10" as signed )

This is casting in mysql (the function name is cast, obvious?).

answered Dec 7, 2011 at 6:24

ajreal

47.4k11 gold badges97 silver badges119 bronze badges

7 Comments

ajreal Over a year ago

-2 is fine, but I really eager to know what's wrong with the above?

outis Over a year ago

There are a couple of drive-by downvotes for this question. If I had to guess, it's because this answer doesn't address the questions. Specifically, Wyatt was asking whether the behavior he was seeing was specified anywhere, rather than how he could ensure a value was of a certain type. Or perhaps the cast isn't necessary, since comparing a number with a string will result in a floating point comparison.

ajreal Over a year ago

well, if the down-voters is really reading the docs, he will be surprise the quoting a variable is no diff from casting, and the proper casting to the column type is the measurement for this question. sorry, for being arrogant, this is how i see the natural of casting does.

outis Over a year ago

I don't see it as arrogant. Not sure what you mean by "measurement for this question".

ajreal Over a year ago

define in this docs (dev.mysql.com/doc/refman/5.1/en/type-conversion.html), numerous mention casting is the proper solution instead of quote the string (of course in that doc, there are more examples and indepth explanation)

|

Collectives™ on Stack Overflow

MySQL query enclosing numbers in strings

3 Answers 3

Comments

Comments

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related