SQL multiple column equality

Question

This is not a real production code! this was presented in a Google CTF to find and learn about application vulnerabilities and how to protect from them

I'm trying to understand a weird SQL syntax I've seen on Google CTF. Let's say we set up a table like this:

create table users (
username varchar(20), 
password varchar(20) );

insert into users (username, password) 
values ('admin', 'supersecretrandompassword');

Now, if we run the following query:

select * 
from users 
where username = 'admin'
and password = username = '';

It will retrieve the username and password of the admin user! It doesn't work, however, when doing password = username = 9 (or any other number).

So my question is, how does the last clause evaluated and why does it work with strings but not with numbers?

Thanks!

Answer 1

The boolean expression password = username = '' is evaluated from left to right, like:

(password = username) = ''

So:

• if password = username is true the expression is equivalent to 1 = ''
• if password = username is false the expression is equivalent to 0 = ''

In both cases '' is implicitly converted to the number 0 prior to the comparison.

In your case I suspect that password = username is false, so the WHERE clause is equivalent to:

where username = 'admin' and 0 = 0;

or simpler:

where username = 'admin';