Using 'where..in' inside store procedure with prepared statements (Safe Way)-CodePudding

Im trying to secure my store procedure to avoid SQL Injection attacks using prepared

statements. with the guide that mentioned here :

"https://dev.mysql.com/doc/refman/8.0/en/sql-prepared-statements.html"

mysql> PREPARE stmt1 FROM 'SELECT SQRT(POW(?,2)   POW(?,2)) AS hypotenuse';
mysql> SET @a = 3;
mysql> SET @b = 4;
mysql> EXECUTE stmt1 USING @a, @b;
 ------------ 
| hypotenuse |
 ------------ 
|          5 |
 ------------ 
mysql> DEALLOCATE PREPARE stmt1;

I have no problem with passing parameter one by one.

Now if i have to pass array of item to SP from java and use 'where..in' , what is the best

approach ?

I can use something like this :

SET @somestring  = '1,3,18,25';
SET @s=CONCAT("
SELECT * FROM city 
WHERE id IN (",@somestring,");");
PREPARE stmt FROM @s;
EXECUTE stmt;

Dont know if is it secure enough for injection , since i guess its not checking parameter

one by one while it not use "USING @a, @b".

CodePudding user response：

You cannot pass an array to your stored procedure, because MySQL doesn't support arrays. Your string '1,3,18,25' is a string that happens to contain commas. This is not an array.

Interpolating an unknown string into a dynamic SQL statement is SQL injection, full stop. You can't be sure it does not contain special characters that would change the syntax of the dynamic SQL query, so it's not safe.

The safest way to use variables in dynamic SQL statements is by using query parameters. But there's a couple of problems: I assume your string with comma-separated numbers may have a variable number of numbers, and you must support that.

Query parameters can only be used for individual scalar values. One parameter per value:

WHERE id IN (?, ?, ?, ?)

The syntax for EXECUTE stmt USING ... supports a variable number of arguments, but not a dynamic number of arguments. You must code the arguments as fixed in your code, and the arguments must be individual user-defined variables (the type with the @ sigil). There's no good way to convert a string of comma-separated values into a like number of individual variables. It's possible to extract substrings in a loop, but that's a lot of code.

And it still wouldn't help because you'd have to find a way to pass a dynamic number of arguments to EXECUTE ... USING.

A common workaround for MySQL users is to use FIND_IN_SET(). This allows you to match a column to a comma-separated string of values.

WHERE FIND_IN_SET(id, '1,3,18,25') > 0

So you could pass your string as a single parameter to a prepared statement:

SET @somestring  = '1,3,18,25';
SET @s='SELECT * FROM city WHERE FIND_IN_SET(id, ?)';
PREPARE stmt FROM @s;
EXECUTE stmt USING @somestring;

In fact, you don't even need to use PREPARE & EXECUTE for this. You can use MySQL variables in a query directly.

SELECT * FROM city WHERE FIND_IN_SET(id, @somestring);

This is safe, because the variable does not cause SQL injection. The query has already been parsed at the time you create the stored procedure, so there's no way the content of the variable can affect the syntax of the query, which is what we're trying to avoid.

This is safe ... but it's not optimized. By using FIND_IN_SET(), the query cannot use an index to search for the values in your string. It will be forced to do a table-scan. Probably not what you want.

So what are the options for solutions?

You could check the input string to make sure it has only digits and commas, and abort if not.

IF @somestring NOT REGEXP '^([[:digit:]] ,)*[[:digit:]] $' THEN
  SIGNAL SQLSTATE VALUE '45000'
    SET MESSAGE_TEXT = 'Invalid input, please use only comma-separated integers';
FI

Once you confirm that the string is safe, then you can safely interpolate it into the query string, as in your example with CONCAT().

My preferred solution is to stop using MySQL stored procedures. I hardly ever use them, because virtually every other programming interface for MySQL is easier to code.