Using SQL parameters to protect my application against injection attacks

Question

I can't use SQL parameters in Delphi, if I try to use them to protect my login form, I get the following error upon login

[0x0005] Operation not supported

The code I am using is :

SQLQuery1.SQL.Text := 'SELECT * FROM registered WHERE email= :Email'+
                      ' and login_pass= :Password';
SQLQuery1.ParamByName('email').AsString := Email;
SQLQuery1.ParamByName('password').AsString := Password;

SQLQuery1.Open; // Open sql connection
if SQLQuery1.recordCount >0 then form2.Show;

but it is not working, the code below works correctly but is it always unsafe :

SQLQuery1.SQL.Text := 'SELECT * FROM registered WHERE email="'+Email+
                      '" and login_pass= "'+Password+'"';

I am using TMySQLConnection and TMySQLQuery components, set ParamsCheck to True, and using the first code mentioned above which doesn't work, how to correct the problem!

Any suggestion or help would be appreciated.

Thank you

+1 for insisting on using parameters, but note that storing passwords in the clear is also very bad. Use salted hashes instead: SELECT * FROM registered WHERE email = :email AND passhash = SHA2(CONCAT(salt,:password),512) — Johan
– Johan, Commented Dec 27, 2011 at 11:48
According to the docs TMySQLQuery does support parameters: microolap.com/products/connectivity/mysqldac/help/TMySQLQuery/… — Johan
– Johan, Commented Dec 27, 2011 at 11:51
@Johan the password variable contain the password already hashed in MD5 — Rafik Bari
– Rafik Bari, Commented Dec 27, 2011 at 12:03
MD5 is not a secure hash function and if you don't salt the hash a rainbow table will break it in seconds, see: stackoverflow.com/questions/401656/… — Johan
– Johan, Commented Dec 27, 2011 at 12:12
You should 1) Add a salt 2) Hash n times. Hashing one time only is not very safe, rainbow tables made "reversing" hashes easy enough. — user160694
– user160694, Commented Dec 27, 2011 at 13:15

user160694 · Accepted Answer · 2011-12-27 13:26:25Z

3

Check the help for "RecordCount". It may raise an exception if the dataset can't determine how many records are returned. What if you remove it and simply check if the dataset not IsEmpty?

answered Dec 27, 2011 at 13:26

user160694

Sign up to request clarification or add additional context in comments.

1 Comment

Rafik Bari Over a year ago

Thankyou verymuch that is exactly what was causing the problem.

Johan · Accepted Answer · 2011-12-28 07:34:06Z

2

Use salted hashes for your password check
Storing a unencrypted password in a database is a no-no.
Use a salted hash instead:

SELECT * FROM registered WHERE email = :email 
AND passhash = SHA2(CONCAT(salt,:password),512)

You can store the passhash in the DB by doing:

INSERT INTO registered (email, passhash, salt) 
VALUES (:email, SHA2(CONCAT(:salt,:password),512), :salt)

The salt does not need to be truely random, but it does need to be somewhat random and different for each user.

edited Dec 28, 2011 at 7:34

answered Dec 27, 2011 at 11:53

Johan

77.4k28 gold badges204 silver badges346 bronze badges

1 Comment

ypercubeᵀᴹ Over a year ago

I can't figure how the underscore in a column name might be an issue.

Collectives™ on Stack Overflow

Using SQL parameters to protect my application against injection attacks

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related