Mysql query question

[indiorunner]

Hey all,
I am doing a simple username/pass db query where I say something like SELECT * FROM tlbusr WHERE username= '$username' AND password='$password'"

For whatever reasion I can use '-' for either/or username/password and the sql executes with atleast one affected row....[eventhough none of the usernames or passwords in the db are empty or have a '-'. If anyone can shed a light on this, it would be appreciated.

thanks,
indiorunner

[mourisj]

Hi,

Do you really have an affected row ??? Wouldn't this mean that data have been changed or deleted ?

Be careful with the field-name 'password': in mysql, there is also a function called password() to encrypt password data:

When I do:

mysql> update person set passwd=password('test') where p_id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

I have crypted the password:

mysql> select passwd from person where p_id = 1;
+------------------+
| passwd |
+------------------+
| 378b243e220ca493 |
+------------------+
1 row in set (0.01 sec)

Can you tell us more about your table? Is the field encrypted?

Have you checked what exactly is submitted to your DB? (i.e. are the variables filled correctely?)

Have a nice day,

JJ Mouris

[indiorunner]

sorry, I used the wrong choice of words. What I meant was when I did SQL injection, I could get a result from the query when I used '-' in either the password or username field . Any advice is helpful.

so for ex:

Say my tblusr table had only 3 fields, username and password (and the usr_id of course).

SELECT * FROM tblusr WHERE username='-' and password = ''-'' gave me a result...which it shouldn't.

-indiorunner

[mourisj]

I don't get this one!
Can you tell me what is the result if you do

select * from tblusr;

without any WHERE-clause ??

If you find a result with the statement you posted, you should find the same line(s) with the statement above.

I hope both username and password fields show the values '-' when you execute your statement !! Otherwise, you really have trouble in your code and we should look at it more closely.

Below, you see the results of my tests with the information you provided.
I guess your results look similar ??.

(Is there a reason why you use single quotes for username value and double quotes for password value ?)

mysql> desc tblusr;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| usr_id | int(11) | | PRI | 0 | |
| username | varchar(20) | YES | | - | |
| password | varchar(20) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> insert into tblusr (usr_id, password) values (7, '');
Query OK, 1 row affected (0.00 sec)

mysql> select * from tblusr;
+--------+----------+----------+
| usr_id | username | password |
+--------+----------+----------+
| 1 | user1 | passwd1 |
| 2 | user2 | passwd2 |
| 3 | user3 | passwd3 |
| 4 | - | - |
| 5 | | |
| 6 | | |
| 7 | - | |
+--------+----------+----------+
7 rows in set (0.00 sec)

mysql> select * from tblusr where username='-' and password='-';
+--------+----------+----------+
| usr_id | username | password |
+--------+----------+----------+
| 4 | - | - |
+--------+----------+----------+
1 row in set (0.01 sec)

[indiorunner]

I did addslashes to the password field and that seemed to do the trick. Also, I setup the same thing with Postgresql and it didn't come up with this problem. This just further proves that Postgresql is far superior to Mysql and less susceptible to SQL injections.

indiorunner

ex:
mysql> select * from tblusr;
+--------+----------+----------+
| usr_id | username | password |
+--------+----------+----------+
| 1 | user1 | passwd1 |
| 2 | user2 | passwd2 |
| 3 | user3 | passwd3 |
+--------+----------+----------+
3 rows in set (0.00 sec)

mysql> select * from tblusr where username='user1' and password=''-'';

+--------+----------+----------+
| usr_id | username | password |
+--------+----------+----------+
| 1 | user1 | passwd1 |
+--------+----------+----------+
1 row in set (0.01 sec)

if you addslashes to the password field, it solves the problem

[mourisj]

I was really astonished about this! I get the same results, but only if I use two pairs of single quotes, not double quotes or single quotes:

mysql> select * from tblusr where username='user1' and password=''-'';

In fact, what you are doing, is a substraction of two empty strings, which, apparently, results in the value '0':

mysql> select '' - '' from tblusr;
+---------+
| '' - '' |
+---------+
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
+---------+
7 rows in set (0.00 sec)

What I still did not get is why I have a result in my query with a numerical value on a varchar field. The query (of course) works also with a 0 replacing the ''-'' :

mysql> select * from tblusr where username='user1' and password=0;

+--------+----------+----------+---------+
| usr_id | username | password | new_col |
+--------+----------+----------+---------+
| 1 | user1 | passwd1 | NULL |
+--------+----------+----------+---------+
1 row in set (0.00 sec)

I really would appreciate if somebody could explain this to me !
😕 Is this some kind of boolean thing, or what happens here ??

Thanks for any hint!

JJ Mouris

[mourisj]

I'm getting closer, but I still don't have a complete explanation:

If I do:

mysql> update tblusr set password=NULL where usr_id = 5;
Query OK, 1 row affected (0.16 sec)
Rows matched: 1 Changed: 1 Warnings: 0

the result changes:

mysql> select * from tblusr where username=0 and password=0;
+--------+----------+----------+---------+
| usr_id | username | password | new_col |
+--------+----------+----------+---------+
| 1 | user1 | passwd1 | NULL |
| 2 | user2 | passwd2 | NULL |
| 3 | user3 | passwd3 | NULL |
| 4 | - | - | NULL |
| 6 | | | NULL |
| 7 | - | | NULL |
+--------+----------+----------+---------+
6 rows in set (0.00 sec)

usr_id 5 is not in the list any more. It's password is NULL.

So the 'password=0' seems to be equivalent to 'password is not null'

Is this right??

JJ Mouris

[mrhappiness]

that's correct

0 is a number
NULL is absolutely nothing, that is: even less than 0