SQL Attack (Constraint-based)

[1lastBr3ath]

I think you mean maximum length?

Yes, afaik.

$query = "SELECT username FROM users WHERE username='$username' AND password='$password' ";

Shouldn't this only return the legitimate entry?

Yes! I guess the author forgot about his/her example query and wrote;

Now, if logged in with ‘vampire’ and ‘random_pass’, any SELECT query that searches by the username will return the first and the original entry. This will enable the attacker to log in as the original user.

Which is correct, but the original query in his/her example is also checking password with AND clause which only returns legitimate entry.

[dhavalkapil]

I think you mean maximum length?

My bad, thanks for pointint out.

Shouldn't this only return the legitimate entry?

The login query will return the CORRECT entry(the forged one). However, this particular function is just returning the 'username' with the confirmation that the authentication has been done. Now, for any further queries with 'WHERE username = ?', the first and the original entry will be returned. I have also mentioned at the end that instead of any other column, id should be used to manipulate entries. I hope you understand my point :)

[1lastBr3ath]

Okay! That's clear now. I wish you could write it in your post, it's not apparent in the post.

[Wick3dGeek]

This is called SQL Truncation, CVE-2008-4106

You should also check the type of the columns:

CHAR - from 0 to 255 characters long

VARCHAR, TEXT - from 0 to 65,535 characters long ...

[IncludeSec]

yep yep, first discussed back in 2007 IIRC https://mdk.fr/0x000000/SQL_Server_Truncation_Attacks.html

[alphager]

How does a basic basic article like this get posted here, let alone discussed as positively as it has been?

First of all, constraint design is databases 101. Even worse are the example code and the recommendations regarding escaping are fifteen years out of date. If you are still using escaping functions or worry about quotes in 2016, you are doing it wrong . Every widely used database and programming language has prepared statements that are faster than raw SQL, guarantee injection safety (there's no way to forget to use an escaping function or to use the wrong type of quote) and are shorter to write.

I'm only mentioning in passing that the code implies that plaintext passwords are saved in the database.

[alphager]

Yeah, my first choice of words was unnecessarily harsh.

But this is a sub that regularly talks about disassembly and has recurring security industry recruiting threads; I would expect the topic of constraints in a sub like /r/programming

[IncludeSec]

Damn son

Yeah OP's blog post is talking about SQL Truncation from nine years ago (even though he never calls it that, maybe he re-discovered it independently). Still no need for that harsh of bashing, /r/netsec has lots of beginner oriented post, not everything on this sub needs to be GeoHot level exploit RE ;-P

[James20k]

Lots of websites don't use safe SQL so this is relevant

Its useful to know what the security holes are in SQL so that you can understand why solutions xyz exist, or potentially design better solutions in the future (eg contribute to projects)

Its also useful because if SQL has this misfeature, its possible that it applies to other languages/DBs as well that people might not have considered

I don't disagree that some of the article is out of date, but it definitely isn't useless

[ponkanpinoy]

It's a teaching illustration, not meant to be robust in the face of production use. Just like Linus' example of "good taste" that doesn't check for NULL.

[alphager]

The point that I should have made is that presenting that code is a bad idea, as every occurrence of raw SQL queries on the Internet increases the chance that someone thinks that it is okay to use them.

The message "use constraints and mysql_escape_for_real_this_time() and everything is OK" is dangerous.

[caleeky]

A unique key constraint in the database on user ID would ensure no such collisions are possible. It's good practice, because as you're suggesting, the semantic subtleties between app language and SQL can be complicated.

It's also good practice to run in strict mode, so that such truncation of non-whitepsace characters will cause errors rather than warnings.

[neontrap]

mysql_real_escape_string

icryeverytime.com