MySQL Forums

Hallo all,


I have created the following procedure:

BEGIN

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
END;

SELECT logid FROM ADMINLOG WHERE LOGID=INLOG;

END



This procedure has only one argument, as an input argument:

IN INLOG smallint


Now, I call the procedure like this:
call aaa(3;delete from adminlog;);


And I get the following response from the server:


ERROR 1064 : 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 '' at line 1
Query OK, 0 rows affected
ERROR 1064 : 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 ')' at line 1



As you can see from string output "Query OK, 0 rows affected", although the sql server detected an error, the query has been successfuly executed!If the table had entries, then all the entries would have been deleted.There is no way for the procedure to check the contents of this variable, since this error happens before mysql service gives execution to the code inside the procedure, thus preventing writing code inside the procedure that will detect the invalid characters in INLOG variable.

My questions is:

a) how can we prevent this?Is there any workaround using MYSQL global system variables?

b) Why MYSQL allows that?INLOG is a smallint type, so I assume that mysql should not even pass this invalid type inside the execution code of the procedure.

The server version is MYSQL v5.1.40 - community server and the operating system is windows XP SP2 Professional.

I am really stuck here, because I am developing an application and I want also the procedure code to check for sql injection in its parameters.


Best Regards,
Nikos Tsapman