13

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?

  • 14
    There would not be any vulnerability if your user inputs do not affect on the SQL query. However, why not to make it a habit to write secure queries always? There is not much time difference between writing them secure and insecure. – Pilfility Jul 17 '18 at 13:21
  • 57
    The authenticated user might be anyone, including threat users (people logged in on Stack Exchange are authenticated, but that doesn't mean SE should let them inject SQL when posting a comment)" OR 1=1 -- – Xenos Jul 17 '18 at 13:28
  • 8
    "I also know using prepared statements with PHP files is the best way to prevent SQL injection" - no, it is parameter binding which prevents SQL injection. Prepared statements fix other security issues (often exploited in conjection with SQLi) – symcbean Jul 17 '18 at 14:56
  • 2
    Prepared queries typically make your (overall) SQL easier to read when you do have parameters to pass. – Kenneth K. Jul 17 '18 at 14:59
  • 3
    What arguments do you have against the use of prepared SQL statements or more generally SQL statements with bound parameters? Do you believe that they are somehow more cumbersome to program or slower to execute? If you don't then there's no drawback to them, only advantages. – David Foerster Jul 17 '18 at 15:30
47

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.

  • 1
    Your SQL sample is still not injectable, it might just fail if the sub query returns more that 1 row. Having 1 OR 1=1 in your otherTable.id won't change a thing (still, weird for an id value) – Xenos Jul 17 '18 at 13:30
  • 4
    @Xenos Perhaps id was a poor choice - the point is that a value from another table is used naively. – Matthew Jul 17 '18 at 13:32
  • 3
    @Xenos this answer is correct. Just because a user is authenticated doesn't mean you trust them. Anything coming from outside your trust boundary (which is absolutely anything or anyone sending you data) needs to be treated as hostile until otherwise proven safe. This means even authenticated users also. – Anthony Russell Jul 17 '18 at 13:51
  • 9
    How is that using the value naively? It's just comparing it for equality. There's no 2nd order SQLi because the value is not executed as SQL. – Barmar Jul 17 '18 at 16:38
  • 3
    Indeed, this does not show a second-order SQL injection. Instead of comparing the column against the retrieved value for equality directly in the statement, the retrieved value would have to be loaded in the application code and passed into a second statement via concatenation or interpolation. – caw Jul 17 '18 at 22:43
27

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.

  • 1
    This. To avoid SQL injection, use parameters which are provided by prepared statements. – rexkogitans Jul 18 '18 at 13:52
  • I feel like this entire answer could have been replaced with the just the last sentence. This was very confusing and misleading until I read your main point at the end, which really says it all. – Brandon Jul 19 '18 at 13:32
  • @Brandon what part is confusing or misleading? – JimmyJames Jul 19 '18 at 13:36
  • It seemed like you were building up to an explanation of how prepared statements are not helpful towards the problem and were going to pitch some alternate idea like sanitization, ORM (which would use prepared statements under the hood), etc. It just boils down to the fact that it's bind parameters that solve the problem. Prepared statements are prerequisite for using bind variables. – Brandon Jul 19 '18 at 16:07
  • @Brandon Everything I wrote is, to my knowledge, is factual. If you can point to something you think is incorrect, please do so. – JimmyJames Jul 19 '18 at 17:18
5

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.

2

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.

    1. 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.

0

Prepared statements do not protect against SQL injection.

You can build an SQL query using string concatenation with user supplied data, prepare it, execute it, and end up with exactly the same result.

Using query parameters is the most effective solution, and is only needed when including data into queries. Your example SELECT * FROM tblName is not affected by SQL injection because no runtime data needs to be added.

SELECT * FROM tblName WHERE lastName = ?

When executing a query with parameters, the SQL driver will provide an API to put data in place of ?. It does this in a secure way such that the inserted data is not parsed as SQL language.

  • This answer is similar to Jimmy's one, but Jimmy's is a more detailed one. – bradbury9 Jul 18 '18 at 12:25
  • @bradbury9 My answer would probably be a good start to the cited answer. You can try to edit it into that answer, and if accepted, I would delete this one. – trognanders Jul 18 '18 at 21:59
  • Your answer is already contained in that answer, check the "Prepared Statements" section of Jimmy's answer which says that prepared statements allow for parametrized queries and describes why parametrized queries (query parameters) do prevent SQLi. Thats why I commented this answer. – bradbury9 Jul 20 '18 at 10:18
  • @bradbury9 It lacks any real heading that prepared statements are not intrinsically secure, which is really a very key point for someone migrating from them in a SQL injection cultured language like PHP. Long answer != always better answer. – trognanders Sep 25 '18 at 20:08
0

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).

  • A local user of a system on a physical device, assuming no DRM or trusted platform is essentially a fully trusted user. It might be worth making a distinction between trusted and trustworthy. – bdsl Jul 18 '18 at 20:04
  • @bdsl I may be missing a nuance. Even with a local device, you can't guarantee against theft / someone sharing a password. I don't see any scenario where protecting against SQL injection isn't the correct answer? Edit: Sorry, you meant grammatically. You're right. – Basic Jul 18 '18 at 23:18

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.