2

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?

1 Answer 1

2

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.

2
  • Thanks @Michael for your quick reply. I changed (', param_CRITERIA ,') to param_CRITERIA. Now it should take the value in varaiable, rt?.. But data is still not getting May 14, 2013 at 5:34
  • I have edited second procedure. May 14, 2013 at 10:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.