Re: Dynamic Sql Statement in SP
Ming Yeung wrote:
> Oh Roland,
>
> I'vd just done more tests.
> You are right, There shouldn't be any problem by
> initializing the user variables each time before
> using them.
>
> I got that silly problem is because one
> connection(Session) is being used by many web
> users, which managed by the Pool. So, user
> variables are connection-specific, not as I
> thought of 'user-specific'.
Glad it works out after all. Actually, I mentioned this at the bottom of my post that you were referring too (http://forums.mysql.com/read.php?98,62373,62512#msg-62512). Good of you to point out that the name 'user-variables' is a bit confusing. I never considered that, I just blindly employed the term used in the reference manual, wihtout paying special attention to the meaning of the term. I will explicitly point it out whenever someone else will ask me.
> Now, I created a function to CONCAT the sql
> statement dynaimically.
>
> IF inUsername IS NOT NULL THEN
> SET varSql = joinSql(varSql, 'AND',
> CONCAT("username LIKE '%", inUsername, "%'"));
> END IF;
>
> CREATE FUNCTION `joinSql`(inSql TEXT, inHow TEXT,
> inValue TEXT) RETURNS text
> BEGIN
>
> IF (inSql <> '') THEN
> SET inSql = CONCAT(inSql, ' ', inHow);
> END IF;
>
> SET inSql = CONCAT(inSql, ' ', inValue);
>
> RETURN inSql;
>
> END
Ok, saves some typing ;)
But why not elaborate, and take care of that pesky SQL injection vulnerability too? Someday, you will be in a hurry and you'll find yourself plugging in a search engine you had lying on your shelf in front of some website. Forgetting of course that it does not deal with SQL injection vulnerability.
Just a thought.
Anyway, I'm glad your problem's solved.
(BTW:
I think I'll write a quick blog on the user variables stuff, as your connection pooling makes it necessary to carefully handle user variables.
Could you please post your configuration? I mean, the appserver product-name and version, JDK or JRE version, connector/J version, that stuff? Much obliged, thanks in advance.)