What pages are vulnerable to SQL injection?

Question

I believe I understand the basics of SQL injection. I also know using prepared statements with PHP files is the best way to prevent SQL injection. I was always told that SQL injection happens most commonly when an attacker inputs valid SQL commands inside form data fields or file input fields on a public facing site.

However, if I have PHP files on my site that can only be accessed by an authenticated user, is it still 100% necessary to use prepared statements?

Also, what about SQL queries that don't require any outside user data to run?

Something like:

SELECT * FROM tableName

If I'm not passing any variables to a query is it still vulnerable to SQL injection?

Answer 1

Do you trust all of your authenticated users completely? Including that they won't have their accounts compromised by attackers? It's bad if an attacker gets access to an account, but far worse if they can then leverage that to steal the rest of your database.

For your second point, the example you've used would not be vulnerable. However, take care with more boundary cases such as

$query = "SELECT * FROM tableName WHERE secret='".dbquery("SELECT secret FROM otherTable WHERE id=3")."'";

If it was possible to insert a payload into otherTable in the secret column for record with id 3, this would be vulnerable to Second Order SQLi, even if the insertion routine used prepared statements.

As per comments:

SELECT * FROM tableName WHERE secret=(SELECT secret FROM otherTable WHERE id=3)

is executed within SQL, so not an issue. The problem occurs when data drawn from the DB is considered trusted at the application layer, and used in later queries.

Answer 2

You say "I believe I understand the basics of SQL injection." but the rest of the question implies that perhaps you may still have some confusion. Either way, this is an important topic and many people continue to be confused about the problem and the solution.

I also know using prepared statements with PHP files is the best way to prevent SQL injection.

Using prepared statements is a necessary but not sufficient condition to protect from SQL injection (SQLi). To understand why, it's important to understand the cause of SQL Injection vulnerabilities. Here are the causes:

1. Concatenating SQL statements from user/client input.

That's it. If you take input from the user and put it in a SQL statement which you then compile on the database, you may be (and likely are) subject to SQLi vulnerabilities. If you do this and use a prepared statement, you have resolved nothing. You are still vulnerable.

So why is using prepared statements always given as the answer? If we can't concatenate the SQL from input, that means our SQL needs to be more-or-less hardcoded. But what if you want to pull back the user info for the one who just logged in? We can't write a new SQL statement every time we have a new user.

Prepared Statements

What makes prepared statements useful in eliminating this issue is that they allow for parameters. That means instead of doing something like this:

// Don't do this!  Subject to injection
"SELECT credit_card_number FROM user_data where userid = '" + userinput + "'"

We can do this:

"SELECT credit_card_number FROM user_data where userid = ?"

And compile this statement on the database. When we go to run it, we need to pass in a parameter value or the query will fail. Why does this solve the issue? Let's say an attacker figures out how to get the string: foo' OR 1 = 1 OR 'a' = 'a in to the userinput value. In the first case the following SQL will execute:

SELECT credit_card_number FROM user_data where userid = 'foo' OR 1 = 1 OR 'a' = 'a';

Returning every row in the table. In the second case this SQL will execute:

SELECT credit_card_number FROM user_data where userid = ?

with the parameter foo' OR 1 = 1 OR 'a' = 'a. Unless you have a user with the unlikely id foo' OR 1 = 1 OR 'a' = 'a there will be no results. But let's be clear, you can use the first (wrong) approach with prepared statements and you will still be vulnerable. There is nothing magical in the prepared statement that will sanitize the input. It's that you can't use the second (correct) approach with a regular statement that doesn't support parameters.

I think often people who understand this will use the shorthand "use prepared statements" because the main reason to use them is because they support parameters. But it's really the parameters that are the solution to this issue.

Answer 3

In addition to Matthew's great answer, even if you trust your users, you may still be vulnerable to CSRF, which could allow an attacker to perform SQL injection without having an account.

An attacker may be able to create a link or form, which an authenticated user may inadvertently click or submit, that causes SQL injection to occur. For this reason, I assert that you should never trust user input, and you should sanitize and use parameterized queries for all user input.

Answer 4

This is a very popular question on Stack Overflow, and that made me develop a sort of "standard response" to it.

1. Why are you trying to bargain?

First off, the question sounds like you are trying to bargain yourself some "discount" in the development. But why you're trying so? Why such a question ever arises? Are prepared statements too hard to grasp/implement for you? Then you should ask a different question, "How do I make prepared statements less tedious?" In fact, a prepared statement is easier than any other method of database interaction.

So, in the first place, there is no reason for such a question at all.

2. SQL injection is only a side effect.

You need to format your queries regardless of any possible threats. You are doing it, not for the famous Bobby Tables, but for a humble girl Sarah O'Hara in the first place (or any other human or entity who happen to bear a name that could make a database choke, if not formatted properly).

So, it is not an SQL injection you must be using prepared statements for, but merely to guarantee that your query will be always syntactically correct.

3. There are too many possible pitfalls with manual formatting

Using no prepared statements means you are resigned to format your data manually, and there are a lot of pitfalls on this road. I endeavored to summarize them in my an article on SQL injection. Here is an excerpt:

1. Manual formatting can be incomplete. Let's take the Bobby Tables' case. It's a perfect example of incomplete formatting: a string we have added to the query was only quoted, but not escaped! While, as we just learned from the above, quoting and escaping should always go together (along with setting the proper encoding for the escaping function). But in a usual PHP application which does SQL string formatting separately (partly in the query and partly somewhere else), it is very likely that some part of formatting may be simply overlooked.

2. Manual formatting can be applied to the wrong literal. Not a big deal as long as we are using complete formatting (as it will cause an immediate error which can be fixed in the development phase), but when combined with incomplete formatting it's a real disaster. There are hundreds of answers on the great site of Stack Overflow, suggesting to escape identifiers the same way as strings. Which would be completely useless and would cause an SQL injection.

3. Manual formatting is essentially a non-obligatory measure. First of all, there is an obvious lack of attention case, where proper formatting can be simply forgotten. But there is a really weird case - many PHP users often intentionally refuse to apply any formatting, because up to this day they still separate the data to "clean" and "unclean", "user input" and "non-user input", etc. Thinking that a "safe" data don't need any formatting. Which is a plain nonsense - remember Sarah O'Hara. From the formatting point of view, it's the destination that matters. A developer has to mind the type of SQL literal, not the data source. Is it a string going to the query? It has to be formatted as a string then. No matter if it's from user input or just mysteriously appeared out of nowhere amidst the code execution.

4. Manual formatting can be separated from the actual query execution by a considerable distance.

The most underestimated and overlooked issue. Yet most essential of them all, as it can spoil all the other rules alone, if not followed.

Almost every PHP user is tempted to do all the "sanitization" in a single place, far away from the actual query execution, and such a wrong approach is a source of innumerable faults alone:

• first of all, having no query at hand, one cannot tell what kind of SQL literal this a certain piece represents - and thus we have both formatting rules (1) and (2) violated at once.
• having more than one place for sanitizing (it could be either a centralized facility or in-place formatting), we are calling for a disaster, as one developer would think it was done by another or was already made somewhere else, etc.
• having more than one place for sanitizing, we're introducing another danger, of double-sanitizing data (say, one developer formatted it at the entry point and another - before query execution), which is not dangerous but make one's site look extremely unprofessional

• premature formatting will spoil the source variable, making it unusable for anything else.

  5. After all, manual formatting will always take extra space in the code, making it entangled and bloated.

4. A prepared statement is not a silver bullet.

Last, but not least. We all have to remember that data literals are not the only variable parts in the query. Sometimes an identifier or a keyword has to be added as well. Prepared statements are of no help in this case, yet such query parts should have no less protection altogether. Luckily, for either identifiers and keywords, the list of possible variants is always limited, so we can use the whitelisting approach which I described in my answer to the reference question on Stack Overflow, so I won't repeat myself.

---

To answer your last question - no, a query without variable parts are not vulnerable, as there is no point to inject.

Answer 5

There's no such thing as a truly trustworthy user. Even if you ignore insider threats (which is a glaring oversight nowadays) there are other risks. The user might have written their password on a post-it, or their device may be compromised with malware that could exploit your vulnerabilities without the user knowing.

By leaving a SQL injection hole exposed to any user, you're effectively granting them unrestricted, unmonitored access to your database. Depending on how well you've set up your security model, they're also likely to be able to delete everything.

Fundamentally, you need to decide who you're protecting against and base your level of effort on that, but leaving a SQL injection hole open is just sloppy and exposes you to risk for no tangible benefit. If you're implementing parameterized queries correctly, there shouldn't be a difference in effort to do it right.

One last consideration... Security isn't a binary thing. You should have many layers, each protecting against different attack vectors. If one layer is compromised, the remaining layers will protect your data (or mitigate the loss). In this scenario, a cross-site request forgery attack would allow attackers to own your database. If you'd patched the SQL injection, the worst they could do is what your app allows a user to do under normal conditions ¹.

¹ (And to mitigate that, you can implement rate limiting or a monitoring/warning system. You can keep going forever).