Re: Dynamic Sql Statement in SP

[Ming Yeung]

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

Thanks for reminding me again, I think I will create a java function to check all IN-parameters before passing to SP. Because I came from PHP background, so I used to have magic-quote=On to take care of the SQL injection. I think I have to search for a third-party plugin(less work for me) to do it for Servlet... :)

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

My Environment:
- Win 2003
- mysql-5.0.16-win32
- Tomcat 5.5.12
- MyEclipse -> Struts/Servlet
- J2EE 1.4 SDK & jre1.5.0_06
- mysql-connector-java-3.1.12
- Jakarta DBCP

I'm actually so glad to see MySql's new baby(Stored Procedure). I understand I should use SP instead of just writting plain Sql statements in Java/Php,etc. I hope SP of MySql will get more more stable in the next versions...

Cheers...
Ming

BTW, this is what I have done with my SP so far. Hope it helps someone.

CREATE PROCEDURE `adminSearchAdministrator`(IN inUsername VARCHAR(20), IN inRole CHAR, IN inStatus CHAR, IN inIsCount BOOL, IN inPostfix TEXT)
BEGIN
DECLARE varSqlPrefix TEXT DEFAULT 'SELECT id, username, role, datetime_lastlogin, status FROM myy_admin';
DECLARE varSqlCount TEXT DEFAULT 'SELECT COUNT(*) AS numOfRecord FROM myy_admin';
DECLARE varSql TEXT;
SET varSql = '';

IF inUsername IS NOT NULL THEN
SET varSql = joinSql(varSql, 'AND', CONCAT("username LIKE '%", inUsername, "%'"));
END IF;

IF inRole IS NOT NULL THEN
SET varSql = joinSql(varSql, 'AND', CONCAT("role = '", inRole, "'"));
END IF;

IF inStatus IS NOT NULL THEN
SET varSql = joinSql(varSql, 'AND', CONCAT("status = '", inStatus, "'"));
END IF;

SET @Sql = combineSql(varSql, varSqlCount, varSqlPrefix, inPostfix, inIsCount);

PREPARE STMT FROM @Sql;
EXECUTE STMT;
DEALLOCATE PREPARE STMT;
END

CREATE FUNCTION `combineSql`(inSql TEXT, inSqlCount TEXT, inSqlPrefix TEXT, inPostfix TEXT, inIsCount BOOL) RETURNS text
BEGIN
DECLARE varSql TEXT;

IF inIsCount THEN
SET varSql = CONCAT(inSqlCount, ' ', inSql);
ELSE
SET varSql = CONCAT(inSqlPrefix, ' ', inSql);

IF inPostfix IS NOT NULL THEN
SET varSql = CONCAT(varSql, ' ', inPostfix);
END IF;
END IF;

RETURN varSql;
END

CREATE FUNCTION `joinSql`(inSql TEXT, inHow TEXT, inValue TEXT) RETURNS text
BEGIN

IF (inSql = '') THEN
SET inSql = ' WHERE';
ELSE
SET inSql = CONCAT(inSql, ' ', inHow);
END IF;

SET inSql = CONCAT(inSql, ' ', inValue);

RETURN inSql;

END