CODEWITHSUNDEEP
mysql
dullboy
dullboy

Reputation: 225

How to handle where clause within mysql procedure?

Here is my procedure.

DELIMITER //
drop procedure if exists GetID;
CREATE PROCEDURE GetID(IN tb VARCHAR(255), in name2 varchar(255))
BEGIN

set @sel = concat( 'select id FROM ', tb, ' WHERE ename = ', name2);
prepare stmt from @sel;
execute stmt;
deallocate prepare stmt;
END //
DELIMITER ;

When I tried to execute the stored procedure by using GetID('city', 'ny'). I got an error

unknown column 'ny' in where clause ...

Here 'city' is the table name. What is wrong?

Upvotes: 0

Views: 44

Answers (2)

Gurwinder Singh
Gurwinder Singh

Reputation: 39507

Assuming that name2 is a string parameter which to be compared with ename column of the passed table

Put quotes around name2 in the SQL:

set @sel = concat('select id FROM ', tb, ' WHERE ename = ''', name2,'''');

It's usually recommended not to use string concatenation to build SQL queries. Since you are hardcoding the column name in the query, it makes little sense to provide the table name "dynamically". But, if you must, use QUOTE to properly escape and quote the passed string.

set @sel = concat('select id FROM ', tb, ' WHERE ename = ', quote(name2));

Upvotes: 2

Michael - sqlbot
Michael - sqlbot

Reputation: 179244

Never concatenate strings directly into queries. It's bad enough that you're passing a table name in, unsanitized. That needs to be fixed, too, but one correct solution to your immediate issue is this:

set @sel = concat( 'select id FROM ', tb, ' WHERE ename = ', QUOTE(name2));

The QUOTE() function correctly and safely quotes and escapes the argument, and also handles null values correctly... and prevents a SQL injection vulnerability here.

See https://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_quote.

Upvotes: 1

Related Questions