stored procedure --> passing user variables to prepared statement

BubikolRamios · #1 (**permalink**) 01-13-09, 06:16

this compiles ok,
the idea is to have one procedure that accepts table name, condition, and returns resultset + cout of records.

the problem is red part because it turns out to be null, can this be done at all (passing user variables to prepared statement)?

Code:

Code:

DELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`test` $$
CREATE PROCEDURE `test`(OUT i_result INT,
                        c_tabela VARCHAR (400),
                        c_condition VARCHAR(400))
BEGIN


DECLARE  i_count INT;

SET @r = CONCAT("Select count(*) from ",c_tabela," ",c_condition ," into ", i_count );
prepare stmt from @r;
execute stmt;

SET i_result = i_count;


SET @s = CONCAT("Select * from ",c_tabela," ",c_condition );

prepare stmt from @s;
execute stmt;

DEALLOCATE PREPARE stmt;



END $$

DELIMITER

;

sco08y · #2 (**permalink**) 01-15-09, 18:34

Okay, first: you haven't verified the user inputs and so you're open to SQL injection. What is SQL injection.

Second: yes, i_count is null. You haven't given it a value.

The solution is to simply put the name in the statement:

Instead of

Code:

" into ", i_count );

You'd do this:

Code:

" into i_count");

You might need to make i_count a global variable.

But read on because you have a major security hole.

You can allow user data, but *only* if you can guarantee that it's valid. The easiest way to do this is to only allow predetermined choices. For example, only allow the tablename to be from an actual list of tables. Only present those choices, and then when the user picks one, verify again that it's from the proper list.

The condition part is trickier. You can't simply let the user type in any condition he wants to. You might accept parameters like this:

Code:

booleanop column1 operator1 value1 column2 operator2 value2 column3 operator3 value3

These could come from drop-downs that force the user to pick acceptable choices, which is also more user-friendly than entering raw SQL.

Again, you still need to validate the inputs. Check that booleanop is either AND or OR, that the operators are either =, <, >, <> or LIKE, that the columns are actual columns in the table specified, and that the value is either a string or a number. If a value is a string, you'll have to replace single quotes with two single quotes. If a value is a number, you need to cast it to a number.

If you do all that, you can allow user input. If you don't, you're basically giving the user complete control over your system.

Internet Services

Web Development

Digital Marketing

Consumer Tech