Re: Dynamic Sql Statement in SP

[Ming Yeung]

> No, this should not be a problem. User variables
> exist in the session scope: one session can see
> all the user variables it has created - not the
> ones created in other sessions.

I tested this with Java Struts & DBCP (Connection Pool). It seems to me there might be a serious matter when using user variables. I added the code to my SP for the test:
...
IF @SqlString IS NOT NULL THEN
SELECT @SqlString as sqlStatement;
LEAVE SP_MAIN;
END IF;
...
PREPARE STMT FROM @SqlString;
...

I believe the sessions of the MySql user are not being released, the connections are all being returned to the Pool. Therefore, user variables are accessed by different web users(All web users use the same MySql account 'ming').

I haven't tested it without connection pooling. But as I have seen that it is really a matter to web applications when using connection pooling. Web user 'A' might get the result of web user 'B', and user 'B' might get errors as the result is being free, etc.

Please prove that I'm wrong, I really want to continue using SP with MySql.

> This is dangerous! A user can, either knowingly or
> unknowlingly, inject it's own SQL inside your
> statement. Suppose your user passes this value for
> inUsername:

Oh yes, Thank for reminding me.

Thanks.
Ming



Edited 1 time(s). Last edit at 01/10/2006 06:52AM by Ming Yeung.