Stored procedure and prepared statements

Question

I have 2 stored procedures:

CREATE PROCEDURE `GetEntryCount`(param_Criteria VARCHAR(500))
BEGIN    
IF (param_CRITERIA <> "") THEN
SET @QUERY1 = concat('SELECT user_name,count(*) as count 
  FROM foodmaster d inner join user_details u 
  on d.user_id=u.user_id WHERE (', param_CRITERIA ,') group by u.user_id;');    
PREPARE stmt FROM @QUERY1;
 EXECUTE stmt;
 DEALLOCATE PREPARE stmt;    
END IF;


CREATE PROCEDURE `sample`(param_CRITERIA VARCHAR(500))
BEGIN
SELECT user_name,count(*) as count FROM foodmaster d 
  inner join user_details u on d.user_id=u.user_id 
  WHERE ( param_CRITERIA ) group by u.user_id;
END;

I tried with same param_criteria in both SPs. In the first procedure, i am getting results. But not in second. Why?

Answer 1

Because the second one is, coincidentally, syntactically valid but not the correct way to do what you are doing. You are trying to do dynamic sql without using dynamic sql. What you are doing in #2 is actually putting the literal string ', param_CRITERIA ,' in the where clause, not the content of that variable.

The only way to do what you are trying to do is method #1.

I would also point out that if the value you're passing in method #1 is coming from an untrustworthy source (such as a web form or url url query string) then you are also potentially setting yourself up for an SQL injection vulnerability.

Edit:

You have updated your question so that example number 2 is no longer consistent with my answer, above, so for future clarity, I'll point out that your original query #2 looked like this:

... WHERE (,'param_CRITERIA',) group ...

Now you have removed the commas and the quotes:

... WHERE ( param_CRITERIA ) group ...

This does not work either, because, as I have pointed out, you are trying get the contents of a variable interpolated into the body of the query, blurring the boundary between logic and data. The only way to do this is to craft a dynamic SQL query, which is what you're doing in #1. Example #2 is not a dynamic query and cannot be made to be one.