1

So I'm writing a paper on sql injection for my db class and one thing I don't understand is this.

I assumed that one of the reasons a lot of sites don't allow duplicate usernames is that they use usernames as primary key and that's why a login using SELECT only has one result for it to work. But what happens when they are multiple results? I'm assuming (lots of assumptions on my part lol) that in a site that isn't vulnerable to sql injection it would just give an error and not work but in a site that is vulnerable to it'll work but when there's multiple results from ' or '1'='1 what account does it log into? is it just the very first result in the table?

4
  • 1
    When its vulnerable to this exploit, then in most cases you are logged in as the first user that stands in the database Apr 17 at 19:03
  • Why not try it? Create an database, and connect using a sql client, and test what gets returned.
    – vidarlo
    Apr 17 at 19:22
  • 1
    @UndercoverDog Thank you. this is actually the answer I was looking for.
    – Parnes
    Apr 18 at 8:02
  • @Parnes No problem, I'll put my comment in the answers, so you can accept it Apr 18 at 10:15

3 Answers 3

1

When its vulnerable to this exploit, then in most cases you are logged in as the first user that stands in the database.

2

Well-crafted sites do not use usernames as primary keys (see this SO question), however they do use keys to search. Perhaps read up on how basic SQL injection works, and you'll find that 'OR '1'='1 returns all rows from the queried table (and doesn't log into any accounts; you'd need a password or a different attack such as forging an authentication token). In theory once the table is dumped, you can hope that the passwords were stored in plaintext and log into an account of choice, or attempt to bruteforce the hashes to gain login credentials. Also, once you realize that a site is vulnerable to SQLi, you can your SQL injection attack to dump more data by bruteforcing table names. Good luck on your paper.

1

TL;DR

SQL injection is really more about allowing arbitrary user data to be interpolated into strings, where it can then affect the SQL query that's executed. It's not really about primary keys or the use of usernames as primary keys, so the question itself is likely based on a false premise.

Usernames and Primary Keys

This kind of question my be more on topic on DBA Stack Exchange or Stack Overflow if it's language- or ORM-specific, but the triple-net is that usernames:

  1. Should enforce a uniqueness constraint if you're using them as primary keys, because a primary key (whether singular or composite) must be unique.
  2. Are often better implemented as fields where the primary key is an auto-incrementing field or a UUID, depending on how you plan to use it. NB: integers tend to be easier to use for numeric IDs, but a lot depends on your architecture, data retrieval strategy, and your specific use cases.

Also, note that not all sites use the "username" as a unique handle or identifier. As an example, there's nothing stopping multiple people on LinkedIn or Stack Exchange from having the same first and last names, so the identifier needs to be something else such as an email address—the email address is presumed to be unique, although people could theoretically use group mailboxes or email lists—or some other username/handle which is then mapped to a numeric ID.

The latter is what Stack Exchange does. Your username and password are for authentication purposes, but you can change your username, email address, or other aspects of your account because your real identity within the system is a (generally site-specific) numeric ID rather than the email address you use to login. Since many aspects of your account could change, using your email address or username (with or without a uniqueness constraint) wouldn't necessarily allow you to be uniquely identified by the system if that data changed.

Currently, your handle of "Parnes" is mapped to user:277041, where 277041 is presumably the unique and immutable primary key under which your username, password, and other data can be looked up by the system. You can change most of your data, but the user ID of 277041 will follow you around as a unique identifier. Without internal knowledge of the SE databases, I can't say for sure that it's the primary key, but for practical purposes you can treat it as one.

Preventing SQL Injection

This is a broad topic, but in general preventative solutions include treating all user input as tainted, validating and sanitizing user input or mutable system data before running a query, and using templates with sanitized or escaped variables rather than standard strings for executing queries.

This list is not meant to be exhaustive, and entire books have been written about topic. However, this will at least provide you a starting point.

See Also

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.