I'm working on a stored procedure that generates a query dynamically based on values of input parameters. The sp uses the CONCAT() function within mysql for building the query. One of the parameters allows a user to enter a search string as a varchar datatype.
Is there function within mysql similar to php's mysql_real_escape_string() that will prevent sql injection attacks?
Below is an example of what this sp is doing:
// trying to post code. keep getting denied. not telling me why.
Re your comment:
I agree with you, however how can you add specific parts to the prepared statement based on inputs. For example, if parameters _A and _B are available, then JOIN two additional tables and include a WHERE statement? Then know which version of the query the prepared statement is being run for to know which parameters need to be bound?
Sometimes you have to use conditional blocks of code:
CREATE PROCEDURE MyProc(IN _A INT, IN _B INT) BEGIN IF _A IS NOT NULL AND _B IS NOT NULL PREPARE stmt1 FROM 'SELECT * FROM MyTable WHERE A = ? AND B = ?'; SET @A = _A, @B = _B; EXECUTE stmt1 USING @A, @B; DEALLOCATE PREPARE stmt1; ELSEIF _A IS NOT NULL PREPARE stmt1 FROM 'SELECT * FROM MyTable WHERE A = ?'; SET @A = _A; EXECUTE stmt1 USING @A; DEALLOCATE PREPARE stmt1; ELSEIF _B IS NOT NULL PREPARE stmt1 FROM 'SELECT * FROM MyTable WHERE B = ?'; SET @B = _B; EXECUTE stmt1 USING @B; DEALLOCATE PREPARE stmt1; END END For your example with conditional LIMIT and OFFSET, you could do it more simply. There is no SQL injection risk from input parameters that are constrained to the INT data type. Then you can default the variables with some sensible value by using COALESCE().
CREATE PROCEDURE MyProc(INT _quick_search VARCHAR(50), IN _limit INT, IN _offset INT) BEGIN SET @LIMIT = COALESCE(_limit, 1); SET @OFFSET = COALESCE(_offset, 0); PREPARE stmt1 FROM 'SELECT ...blah blah... LIMIT ? OFFSET ?'; EXECUTE stmt1 USING @LIMIT, @OFFSET; DEALLOCATE PREPARE stmt1; END I agree with the statement from @YourCommonSense that stored procedures are not the best solution. MySQL's implementation of stored procedures is primitive and hard to use. I see many questions on Stack Overflow asking questions about how to do some task in stored procedures in MySQL, and it makes me cringe every time.
You'd be better off using virtually any other scripting language instead of using MySQL stored procedures.
"You'd be better off using virtually any other scripting language instead of using MySQL stored procedures" <-- Thank you for confirming this!Never use CONCAT() function for building the query.
PREPARE stmt1 FROM 'SELECT * FROM whatever WHERE something LIKE ?'; EXECUTE stmt1 USING @search_parameter; DEALLOCATE PREPARE stmt1; 
