MySQL Forums
Forum List  »  Stored Procedures

Re: Dynamic Sql Statement in SP
Posted by: Roland Bouman
Date: January 11, 2006 03:10AM

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.)

Options: ReplyQuote


Subject
Views
Written By
Posted
2754
January 09, 2006 06:22AM
1480
January 09, 2006 08:06AM
1218
January 09, 2006 02:10PM
1798
January 09, 2006 08:02PM
1681
January 10, 2006 12:24AM
1452
January 10, 2006 03:07AM
1669
January 10, 2006 06:51AM
1394
January 10, 2006 01:49PM
1740
January 10, 2006 06:35PM
1545
January 10, 2006 07:22PM
1630
January 10, 2006 10:33PM
Re: Dynamic Sql Statement in SP
1626
January 11, 2006 03:10AM
2197
January 11, 2006 04:27AM
1652
January 11, 2006 06:54AM
1477
January 11, 2006 03:34PM
1647
January 11, 2006 03:56PM
1635
January 12, 2006 03:20AM
1445
January 12, 2006 04:44AM
1414
January 11, 2006 09:08PM
1662
January 12, 2006 03:11AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.