Re: A little question about Sql Injection in SP.

[Ming Yeung]

I tried the 2nd statement myself, and yes, I am able to login using any username.

> That is, the value in the columns is matched to the value in the variables. This is not injection, just comparison of values.

Um, do you mean
[SELECT * FROM user WHERE username = inUsername AND password = inPassword]
would eventually become
[SELECT * FROM user WHERE username = '{EMPTY}' AND password = '{\' OR \'\'=\'}'] ?

So, single quotes are added to the variables(inUsername & inPassword) by MySql? and the single quotes from inPassword will be replaced by some other special characters, e.g. slash{\}?

> In the first case, the *value* of the parameters
> is evaluated. That is, the value in the columns is
> matched to the value in the variables. This is not
> injection, just comparison of values. The query
> will just look for users with that funny empty
> username, and with that quirky password that looks
> a bit like sql.

Um, I am a bit confusing. Let say:

-------------------------------------------------------
'Stored Procedure A':
CREATE PROCEDURE `LoginA`(IN inUsername VARCHAR(20), IN inPassword CHAR(32))
BEGIN

SELECT * FROM user WHERE username = inUsername AND password = inPassword;

END
-------------------------------------------------------
'Stored Procedure B':
CREATE PROCEDURE `LoginB`(IN inUsername VARCHAR(20), IN inPassword CHAR(32))
BEGIN

SET @v_username = inUsername;
SET @v_password = inPassword;
SET @v_sql = 'SELECT * FROM user WHERE username = ? AND password = ?';

PREPARE STMT FROM @v_sql;
EXECUTE STMT USING @v_username, @v_password;
DEALLOCATE PREPARE STMT;

END
-------------------------------------------------------

May I know any differences(performance, safety, etc) between 'SP A' and 'SP B' ?
If both 'SP A' and 'SP B' are safe, which one should be used over the other one?

Thanks Roland,
Ming



Edited 4 time(s). Last edit at 01/12/2006 10:47AM by Ming Yeung.