SQL - What is the real danger of SQL injection?

Question

NOTE: This is not a question about what is SQL Injection, but rather a question to clear up what the actual vulnerability, particularly given some specific test cases.

Background: Please see this video by a group called Modern Rouge. Or, you can ignore it, as it's kind of oversimplified for people who don't have technical skills and aren't already familiar with SQL Injection. I will point our the important part when it's needed.

So, for those who don't know what SQL Injection is (I'm just adding this for completeness and to give background to my particular question), Let's say I have PHP code like this:

$query = "SELECT * FROM users WHERE username='$username'";

This code is vulnerable because let's say a malicious end user puts in their username as ' or 1=1;--, this could make the final query

SELECT * FROM users WHERE username='' or 1=1;--'

Which does not have the intended effect.

So, now watch the video around the 4:00 mark.

The example they give on their test website for SQL injection, they use SQL injection to bypass the password check.

Here's what I don't get, In real life, wouldn't I check the password in PHP instead, making this specific example useless? For example:

if($userRecordFromDB["pwd"] == $pwd) { // User authenticated.

Am I right that SQL injection can't bypass my PHP authentication? What is the vulnerability here? Unless I'm tying the password check to my query (which I'll admit could have been done for the example), even if I have a vulnerable query, my site shouldn't be vulnerable.

Second related question: The video goes on to imply that a single vulnerability could allow attacker access to my whole db! I'm assuming they mean an attacker could do something like so:

SELECT * FROM users WHERE username='' or 1=1; SELECT * FROM creditInformationTable where 1=1--'

$username of course would be ' or 1=1; SELECT * FROM creditInformationTable where 1=1--

However, this also confuses me. Unless I put this data into a nice table or something for them, wouldn't the data never leave the back-end, even if the query was vulnerable? How could they possibly get this information unless I give it to them inadvertently?

Which leads me to the bigger question: What is the danger of SQL injection? Is it purely theoretical, or are there real cases where an attacker could do something like login or access all the tables in your DB from pure SQL injection?

Edit: Scratch all that. Let me narrow a bit.

How does it leave the back-end? Even if something queries the wrong thing, how would my attacker get it? DB APIs like PDO and SQLI return the information to PHP, NOT the resulting page. Shouldn't a well written PHP script catch that wrong data is there, or, at the very least, not echo it all out to the user?

Answer 1

SQL injection is not theoretical. There are news reports about real-world data breaches perpetrated using SQL injection practically every month. There's a great site that collects them: https://codecurmudgeon.com/wp/sql-injection-hall-of-shame/

Here's a good one from last month:

https://motherboard.vice.com/en_us/article/vba5nb/fornite-login-hack-epic-games-website

Bugs on Epic Games Site Allowed Hackers to Login to Any ‘Fortnite’ Player’s Account

...

That page, which is now offline, contained two vulnerabilities that are often found in websites: an SQL Injection (or SQLi), and a Cross-Site Scripting (or XSS), according to Check Point researchers.

Regarding the examples you give, I do recommend validating the password in the application code. Then you can differentiate between "no account found" vs. "account found, but password was wrong" (you don't want to reveal this to the user, but you might want to log the error, and possibly lock the account if they have too many failed password attempts).

But regardless, the SQL statement is vulnerable to SQL injection. Not only with the "OR 1=1" trick that you show, but also if you can trick the query into running UNION-based SQL queries:

$query = "SELECT * FROM users WHERE username='' UNION ALL SELECT * FROM INFORMATION_SCHEMA.TABLES -- '";
                                              ^^ $username ...

If the attacker can find an SQL query in your app (not necessarily the search by account name), they can use this technique to query all your tables. Then they can further use UNION techniques to query the data in all the tables, once they know their names.

---

Regarding your follow-up question:

"How does the data leave the back-end?"

Consider if your password-checking code (for the query above) looks like this:

$query = "SELECT id, username, password_hashed FROM users WHERE username='$username'";

$stmt = $pdo->query($query);
while ($row = $stmt->fetch(PDO::FETCH_NUM)) {
  if (!password_verify($password, $row['password_hashed'])) {
    die("Invalid login for user {$row['username']}");
  }
}

See? The result from the SQL query is output to the user. To the developer of this code, it seems obvious that since they just queried for $username then that's the value that would be returned by the query. They feel that $row['username'] is safe to use.

But it's not — it's some data from the other part of the UNION. By using CONCAT() and GROUP_CONCAT(), the attacker can even put together multiple columns from multiple rows. And they do. It may take them several tries to get their attack query to have the right number of columns in the right positions, but they apparently have nothing better to do.