Re: DBI & MySQL Login Test

[Sewi]

As others said before: You (as many, many others) suffered from the biggest *SQL problem: It's a language itself and it could be hijacked.

I experienced cases where placeholders didn't work depending on the OS, type and version of DB client libs, DB server software and following SQL statements. Here is my solution, assuming you already checked that both $uname and $pword are not empty:

if ($sth->selectrow_array('SELECT COUNT(*) FROM '.$Tablename.
'WHERE username = 0x'.unpack('H*',$uname).
' AND password = 0x'.unpack('H*',$pword))) {
 # Successful login
} else {
 # Oops, something failed
}

What happens?
First: Be really,really,really sure that $tablename is a configured value! If this source is placed into a sub (which may get $tablename as parameter), it would be wise to do a regular expression-check on the value before using it at all!
Second, let's look at the SQL. Assuming

$tablename = "users";

$uname = "user";

$pword = "123";

our SQL statement looks like this:

SELECT COUNT(*) FROM users WHERE username = 0x75736572 AND password = 
+0x313233

You'll notice that no SQL injection is possible any longer, because everything which is read from the user is converted to hex which couldn't harm the SQL string.
This statement requests the number (COUNT(*)) of users matching this username and password. Assuming your "username" column is unique, a value of 0 says "There is no user with this password" while a COUNT of 1 matching row says "I found a user with this username and that password" - which is what you want.
The latter is easy: Instead of preparing a statement, executing it and fetching the result (which was missing in your post), you could also combine all three in one command. selectrow_array returns an ARRAY containing the results for the SELECTed columns. In this case only one: The result of the COUNT.

Last note: If you've got other things to fetch from the user DB, you could also combine this:

my ($UserID,$UserState) = $sth->selectrow_array('SELECT ID,State FROM 
+'.$Tablename.
'WHERE username = 0x'.unpack('H*',$uname).
' AND password = 0x'.unpack('H*',$pword));
if ( ! defined($UserID)) {
 # Oops, no UserID means no user with matching username/password was f
+ound
} elsif ($UserState eq 'LOCKED') {
 # Oops, this user is not allowed to login
} else {
 # ok, everything right, the user is validated
}

(Everything shown here is based on Sybase SQL products, but should also work on mySQL and other SQL servers. As I don't use mySQL mysqlf, I can't test it, sorry.)

[astroboy]

Hmm, that's one way of protecting against SQL injection, I guess. But it means that you'd need to run this kind of query for all SQL queries that use external data.

And it means any internal users who simply want to query the db - using Crystal Reports, Business Objects or a myriad of other tools, now can't.

[Sewi]

Sorry, but you're wrong :-)
I'm using the HEX representation only between the Perl script and the SQL-Server. You could still do a

SELECT LastLogin FROM Users WHERE Username="astroboy"

Note the " around your name, they make this a string.
Try this with your SQL-Server:

SELECT 1 WHERE "astroboy"=0x617374726f626f79

PS: You could also use SQL commands to play with your string, because the SQL server trade it as a plain string, not a list of HEX-codes:

SELECT 1 FROM Users WHERE LOWER(Username)=LOWER(0x617374726f626f79) AN
+D Password=0x617374726f626f79

This would match astroboy, ASTROBOY and AstroBoy in the DB, but not 0x617374726f626f79 or 0x617374726F626F79.

[astroboy]

oh, ok. I couldn't get this to work with MySQL

[astroboy]

Whoops, when I changed to MySQL syntax I got it to work. Very cool.

[swordfish]

Hello Sewi,
Your code example of preventing SQL injection is cool.

On my website, I want to allow users to login, but prevent any hackers from getting my subscribers usernames and passwords.
I don't have a need to print out the username and password.
They must remain hidden out of sight.

As I understand it, in your SQL example you're letting SQL validate for user input. Is that correct?

Can you elaborate on the error checking? I would like to check if a user enters the form with incorrect data.
How do you print an error message?

[Sewi]

if ($uname eq '') {
 print "Please enter a username";
} elsif ($pword eq '') {
 print "Please enter a password";
} elsif ($sth->selectrow_array('SELECT COUNT(*) FROM '.$Tablename.
'WHERE username = 0x'.unpack('H*',$uname).
' AND password = 0x'.unpack('H*',$pword))) {
 # Successful login
 print "You're not logged in";
} else {
 print "Username or password wrong";
}

This code doesn't give you any information if the password is wrong or the username doesn't exist, it only says "ok" or "not ok", but this is what I prefer for login forms.
If you have different error messages for "user does not exist" and "password wrong", I could start guessing a username (as long as I get the "user does not exist" message). Then, once a valid username is known, guess the password for this user. Using one combined error message says: Maybe you got a non-existing user, maybe a wrong password, doesn't matter.

(You need to replace the "print" lines with whatever_generates_user_output_on_your_system.)