How to limit the impact of and reduce the risk of SQL injection for existing website?

Question

Our website is 100% API based (with an SPA client). Recently, a hacker managed to get our admin's password (hashed with SHA-256) through SQL injection (and cracking pwd) like this:

https://example.com/api/products?userId=3645&type=sqlinject here

It is just an example, but it is deadly to us. Fortunately, it was a nice hacker and he just emailed us about the hack. Lucky for a website under constants attacks throughout it's 5 years of existence.

Yes we DO know that we need to check user input everywhere and do properly format/escape/prepared statement before sending data to MySQL. We know, and we are fixing it. But we do not have enough developers/testers to make it 100% safe.

Now assuming we have 0.1% chance of being SQL injected somehow. How do we make it harder for hacker to find it and limit the damage as possible?

What we do so far as quick fixes/additional measurements:

1. At the output "gate" shared by all APIs, we no longer send the raw PDOException and message like before. But we still have to tell the client that exception occurred:

   {type: exception, code: err643, message: 'contact support for err643'}
   

   I am aware that if hacker see exception, he will keep trying...

2. The user PHP uses to connect to MySQL can only CRUD to tables. Is that safe enough?

3. Rename all tables (we use open source so hacker can guess the tables name).

What else should we do?

Update : since there are many comments, I would like to give some side info

1. First of all, this is LEGACY app, developed by some student-like folks, not enterprise grade. The dev team is nowhere to be found, now only 1-2 hybrid dev-test guy handling hundreds of Data Access class.
2. The password is hash with SHA-256, yes it sucks. And we are changing it to recommended php way.
3. SQL injection is 17 years old. Why is it still around?
4. Are prepared statements 100% safe against SQL injection?

Answer 1

Don't spend lots of time on workarounds or half fixes. Every minute you spend trying to implement anything suggested here is a minute you could have spent implementing prepared statements. That is the only true solution.

If you have an SQLi vulnerability, the attacker will probably win in the end no matter what you do to try to slow her down. So be aware that while you may make improvements, in the end you are playing a losing game unless you fix the root cause of the issue.

As for what you have tried so far: Hiding error messages is a good start. I'd recommend you to apply even stricter permissions (see below). It is debatable whether changing table names help, but probably it won't hurt.

Ok, so what about those permissions? Limit what the database user can do as much as possible, down to table or even column level. You may even create multiple database users to lock things down even more. For instance, only use a database user with permission to write when you are actually writing. Only connect with a user that has the right to read the password column when you actually need to read the password column. That way, if you have an injection vulnerability in some other query it can not be leveraged to leak passwords.

Another option is to use a web application firewall (WAF), such as mod_security for Apache. You can configure it to block requests that look suspicious. However, no WAF is perfect. And it takes time and energy to configure it. You are probably better off using that time to implement prepared statements.

Again, don't let any of this lure you into a false sense of security. There is no substitution to fixing the root cause of the problem.

Answer 2

The only correct way is to use prepared statements.

1. If you disguise error messages, it a bit harder, but won't stop attackers.
2. You can restrict the rights, but all rights granted to the user could gained by the attacker. It is also possible to execute shell commands from an SQL-Injection in some cases.
3. Renaming tables won't help you. The tool sqlmap will brute force the table names for you char by char. This doesn't need much time.

You could restrict the number of calls too, to make it harder for attackers or make alerts on suspicious calls and stop this manual, but the only correct way addressing this issue is using prepared statements. Just grep through your source code and take a look on SQL-Statements with dynamic contents and replace them.

Answer 3

We know, but we do not have enough developers/testers to make it 100% safe.

This is the real problem. Until you hire more people or reassign priorities, you're not safe. Stopping 99.9% of attackers means that your data has been compromised. Band-aid solutions are a bad thing, and they do not work. Don't waste time refactoring your SQL access pattern while you could be fixing actual problems.

As for the code solution, using prepared statements, like many people here suggest, is the easiest way to safely use SQL.

It's a lot easier than trying to use php to clean the arguments yourself, and costs a lot less time. Just grep your entire codebase for everything SQL related and fix it.

Answer 4

Purge all SQL from your code forever

The best solution to this problem is to delete all SQL code as literal strings from your code and never introduce it again. Instead, use ORM software like Hibernate or Entity Framework. This software is much nicer to use than raw SQL anyway, and automatically parameterizes your SQL when it eventually goes to the database. If it is possible for you, have this policy.

If you do not have the time to learn and implement these packages, or cannot use them for some other reason, trietend's answer is the next best thing, using prepared statements. This will allow you to be safe from SQL injection. That is, until you put any pressure on your developers to deliver quick solutions, at which time they may once again forget to parameterize a single variable, leaving you back where you started.

Answer 5

We know, but we do not have enough developers/testers to make it 100% safe.

It is not entirely clear what you mean by this statement, as prepared statements are trivial in every popular web language (PHP, Python, Java, node.js, etc) and are more-or-less a 100% effective measure against SQL injection.

Do you mean you subcontract out development and can't afford to QA the code they write for you on contract? You can't afford not to: you are still the responsible party here.

Do you mean that your developers are too busy implementing features to take a few hours to update the codebase? Everything other than prepared statements will take longer and cost more (e.g. ORM, obsfucation, fine-grained permissions, etc).

Do you mean your developers are not competent enough to perform this simple task? That's a waaaay bigger problem (or to be more precise, SQL injection should not be your biggest concern at that point).

Do you mean your codebase is such a shotgun-formatted spaghetti-logic mess of nested includes that even competent developers will take forever to perform what would otherwise be a simple task of a few hours? Likewise, bigger problem.

Whichever one it is, the solution is to use prepared statements, circa yesterday.

Answer 6

Once the initial "prepared statement" advice has been implemented, I would recommend some reading about SQL injection. Asking "how do I prevent SQL injection" is a rather broad question! Security is a subject that you (rather, your entire dev team) need to be very familiar with in order to reduce the risk of compromise. One hole undermines all your other effort.

Visit the Open Web Application Security Project (OWASP) here: https://www.owasp.org/index.php/SQL_Injection

Particularly the additional material:

• SQL Injection Prevention Cheat Sheet
• Query Parameterization Cheat Sheet
• Guide article on how to Avoid SQL Injection Vulnerabilities

And also

• How to Review Code for SQL Injection Vulnerabilities.

Answer 7

A stopgap measure would be to look into a web application firewall (WAF)- there are a few companies out there that provide this as a service, and a few cloud providers that also have offerings. CloudFlare seems to offer one, I've had one client use Incapsula, and I know the AWS WAF offering has some managed rulesets for SQL injection.

The way this works is all traffic is sent to/through the WAF (either by including it on servers, or setting DNS to point to the WAF as you would for a CDN), and it looks for things that look like SQL injection attempts in the parameters. It won't catch all attacks, and may block legitimate traffic, but it is an available bandaid fix.

Answer 8

By "do not have enough developers" I take it you mean that some influential people in your organisation think there are higher priorities, so whatever resources you do have are devoted to those things. So fixing your security issue is losing out to other things in a cost-benefit analysis.

Your task here is to change their minds, as much as it is to implement the fix. Quantify what's at stake - what would be the damage, both in money and reputation terms, of another breach? There may be laws in your jurisdiction that require you to report compromises of your user's data. Would that be embarrassing? Might there be media reports of your organisation's poor practices?

Think of the investment to fix the security as insurance. In retrospect, after you know your building did not burn down for a ten year period, you might argue that your premiums were a waste. But you and your organisation do pay for insurance, because you face an uncertain future. It's risk management.

Risk has two factors: probability and impact. The probability of another breach is high - you know it can and has been done at least once. If the potential consequences are also bad, you should increase the priority of fixing your security vulnerabilities.

Answer 9

As everyone has mentioned fix the code on the site.

You can make any patches you want to a wall, but until the wall is built correctly any patches will be a bandaide to bleeding problem..

Any other suggestion to clean code is what Linus Torvalds would call masterbation.

• Fix your code (from most obvious injection points to the last I.E Get params) and use prepared statements. how can i prevent sql injection in php

  • You can try a WAF (I.E ModSecurity) temporarily until you're able to get things fixed.

  • Try Perhaps some sort of IP ban on 'people testing the site' or blocking unsafe queries being made.

  • If you have the ability make a temporary (safe) login screen in front until this is resolved.

  • Try a third party service until this is resolved something like cloudflare or whatever else is out there.

PDOs have been available since PHP 5.1 released in 24 November 2005 ... Devs need to understand that they're consequences to their poor coding habits and I personally think that there should be accountability to the quality of work being provided.

At any rate PDOs are an easy thing to implement . If your devs aren't able to provide better quality, then I would suggest a purge...

Answer 10

The most important rule is:

Use libraries, tools and APIs that make the safe option the easiest option.

Nacht says "just use an ORM" which is a subset of this rule.

Prepared statements violate this rule, as they are cumbersome to use, result in code bloat, also they can be often slower than non-prepared statements depending on your database. They are a valid solution, I won't criticize anyone who uses them, but they are not necessarily the best and most convenient solution.

And.. we want the safe option to be easy and convenient, so the programmer uses it out of self-interest and laziness. We want the programmer to have to actually go out of their way and invest some effort in order to use the unsafe option!...

A much better solution is an API like Python dbapi. I wrote a php equivalent years ago, which was like:

db_query( "SELECT * FROM mytable WHERE id=%s", $id )

This is more convenient than prepared statements. Just one line to type. Also it returns the result in a form that foreach() can use, so it is a lot easier to use than fetch() and stuff. More important, the code hidden behind this API will handle escaping properly. This makes SQL injections irrelevant. This handles about 99% of queries if you don't use an ORM. Dynamic queries (usually search) will need special handling though, but it's still easy.

If you keep this rule in mind, then at some point you will have to wonder why you have to use htmlspecialchars(), and why the language doesn't have a feature that would make the safe setting (ie, no XSS vulns) the easiest to use? Why oh why? And that's when you drop PHP.

Answer 11

In addition to all the great answers so far, if you want to tackle the specific problem of extracting user credentials, you can refactor your database to have the passwords in a seperate table with strict access rights, similar to the way Unix systems put them into /etc/shadow.

To verify credentials, you need a stored procedure that takes the entered password (or hash) as a parameter and returns a boolean. In other words, you offload the job of verifying credentials to the database and the actual hash never leaves the database.

This requires minimal refactoring and solves your immediate problem at the root, instead of one specific SQL injection while leaving you vulnerable to the next one.