Case sensitive username and password

Discussions of secure PHP coding. Security in software is important, so don't be afraid to ask. And when answering: be anal. Nitpick. No security vulnerability is too small.

Moderator: General Moderators

Post Reply
edmund9990
Forum Newbie
Posts: 1
Joined: Wed Dec 26, 2007 3:45 pm

Case sensitive username and password

Post by edmund9990 »

I have been strugggling to make my username and password case sensitive:

$sql = "SELECT * FROM user WHERE username='" . $_POST['username'] . "' AND password='$password'
AND "."usertype = '" . $_POST['usertype'] ."'";[/syntax]
User avatar
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

Post by Kieran Huggins »

Code: Select all

yourpage.php?username=bob";drop%20*%20from%20user;&you=screwed
User avatar
Mordred
DevNet Resident
Posts: 1579
Joined: Sun Sep 03, 2006 5:19 am
Location: Sofia, Bulgaria

Post by Mordred »

Your default collation is case insensitive. Compare:

Code: Select all

mysql> select 'a' = 'A' collate 'utf8_general_ci';
+-------------------------------------+
| 'a' = 'A' collate 'utf8_general_ci' |
+-------------------------------------+
|                                   1 |
+-------------------------------------+
1 row in set (0.00 sec)

mysql> select 'a' = 'A' collate 'utf8_bin';
+------------------------------+
| 'a' = 'A' collate 'utf8_bin' |
+------------------------------+
|                            0 |
+------------------------------+
1 row in set (0.00 sec)
@Kieran Huggins:
Lol @ &you=screwed :)
The attack generally won't work on MySQL (may work on other DBs though) , but yeah, edmund9990, you definitely need to read up on SQL injection. Check the "Security Resources" post if you don't know where to start.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Mordred wrote:The attack generally won't work on MySQL (may work on other DBs though) , but yeah, edmund9990, you definitely need to read up on SQL injection. Check the "Security Resources" post if you don't know where to start.
Actually, it does, although slightly modified..
User avatar
Mordred
DevNet Resident
Posts: 1579
Joined: Sun Sep 03, 2006 5:19 am
Location: Sofia, Bulgaria

Post by Mordred »

I meant specifically that the semicolon won't work with MySQL (unless that crazy mysqli function was used, which should be firing offense in my book), not that the injection in general is not possible in this case (or that POST params won't be passed from the URL, heh).
User avatar
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

Post by Kieran Huggins »

Where's Little Bobby Tables, maybe he can chime in on this?

So the semicolon is stripped in MySQL queries?
NoDude
Forum Newbie
Posts: 1
Joined: Wed Jan 16, 2008 1:13 pm

Re: Case sensitive username and password

Post by NoDude »

Bobby Tables got expelled due to the mishap related to his name :)

But seriously, the execution of the query stops on the first semicolon with the mysql_ family of functions.
User avatar
Mordred
DevNet Resident
Posts: 1579
Joined: Sun Sep 03, 2006 5:19 am
Location: Sofia, Bulgaria

Re: Case sensitive username and password

Post by Mordred »

Injecting semicolons is just one kind of SQL injection (for the lazies, hehe)
With MySQL in this situation, you can still carry out read-only attacks against arbitrary columns in any table the current MySQL has access to. It's generally pretty severe, even though you can't delete/insert records. (Usually an attacker in such situation will compromise the admin account, and then will try to use/exploit the capabilities of the administrative interface, it depends on the application being ... errr ... pen-tested)
Post Reply