current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

I have this situation for example:

id       value
 1       data_1
 2       20
 3       data_3
 4       15
 5       data_4
 6       data_6

and the following stored procedure:

DELIMITER $$

CREATE PROCEDURE `test_2`.`test_procedure` (val int(9))
BEGIN

select * from `test` where `value` = val;

END

if I call the procedure like that

 call test_procedure(20);

I have the following result:

id     value
 2     20

Everything is ok until now.

But the thing I cannot understand is that when I call the procedure like that:

call test_procedure("abc");

I have the following result:

id       value
 1       data_1
 3       data_3
 5       data_4
 6       data_6

Is this a normal behavior of the MySQL database?

If I declare the variable “val” as integer, doesn’t this prevent MySQL injection?

I expect to have a warning or something that tells me that the input value is not an integer and the procedure to stop, not to reveal all the values in the tables that are not numbers.

share|improve this question

2 Answers 2

up vote 0 down vote accepted

As for the strange results, it may be due to MySQL casting the val parameter and the value column to integer:

SELECT CAST("abc" AS SIGNED); -- returns 0
SELECT CAST("data_1" AS SIGNED); -- also returns 0

So, if you treat "abc" and "data_1" as integers in MySQL, they're equal. Just a theory but it seems to fit.

If zero happens to be an invalid value for test.value, you could throw an exception in your procedure if val is zero. If not, you'll need some other sort of workaround.

BTW, I agree with tadman that you don't have an injection threat here.

share|improve this answer
    
Isn’t this situation almost equal with the following syntax: “’abc’ or 1=1” where this syntax always evaluates to true and the result will be all the data from the table? In my case the result will be all the data from the table apart the numbers, and will give the information to the persons that don’t have to see them. Maybe it is not an injection but in my opinion is a serious problem of security. –  Catalin Apr 26 '13 at 18:15
    
You're right - it's similar to injection in that it'll give a user more than they should see. I've got an idea for a solution; I'll add it to my answer shortly. –  Ed Gibbs Apr 26 '13 at 18:30
    
Tadman is right. It evaluate with 0. Also this query (I test it) give the same result: “select * from test where value = 0;” I think the solution will be to check if the variable “val” is different from 0 and if it is to go ahead with the sql query, else to stop. –  Catalin Apr 26 '13 at 18:42
    
@Catalin - sorry about the delay posting an answer; something came up. Yes, if 0 is an invalid value for val then you can just have the procedure check for zero and throw an exception or return no results. Are you good to go like that? –  Ed Gibbs Apr 26 '13 at 19:19
    
For sure I will check if the value is not equal with 0 if there is no other workaround and I’m “glad” that I discover that, but I want to understand why it is happening. It is a bug or it is something that is known and has to be avoided? –  Catalin Apr 26 '13 at 19:32
up vote 1 down vote

SQL injection bugs usually come about by allowing arbitrary user data to be inserted into a query string. In this case, val refers to a value, not an arbitrary string. If you were using CONCAT to compose the query, you could be in serious trouble. In this case it looks like you're okay.

As you've written it, even if val was, somehow, '; DROP DATABASE db; -- then it would be compared on a string basis, not as an actual inline string. This would be no worse than having that kind of thing in a column and performing a comparison on it.

What you're seeing is probably the conversion of an arbitrary string to 0 internally, so your "abc" matches against anything else that evaluates to 0.

share|improve this answer
    
It is true. It evaluate with 0. I don’t understand why the where clause “where value = 0” gives all the records that are not numbers. It shouldn’t return any result? None of the data stored in value column are equal to 0. –  Catalin Apr 26 '13 at 19:19
    
It might end up being WHERE CONVERT(value, SIGNED)=CONVERT(val, SIGNED) which is WHERE 0=0 since alphabetic characters are converted to 0. –  tadman Apr 26 '13 at 19:22

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.