-4

I was wondering if there is a standard way to tackle SQL injection. I'm asking because I started working at a company and they want me to make the website sql injection safe. Is there way to e.g scan the whole website or procedure to tackling SQL injection in a software that already exists?

Gumbo
  • 643,351
  • 109
  • 780
  • 844
Denzyl Dick
  • 293
  • 3
  • 11
  • 3
    Which technologies were used to build the website? – zimdanen Mar 26 '14 at 14:51
  • Sorry for my vaged question. It's based on PHP 5.3 and MySQL on a Linux server CentoS – Denzyl Dick Mar 26 '14 at 15:05
  • The standard mechanism is to make SQL injection impossible by ensuring all database access is through an ORM, or, failing that, stored procedures or whatever your language's version of Prepared Statements is. Without knowing more about your codebase though it's hard to advise. – RB. Mar 26 '14 at 15:17
  • 3
    So is your question about *detecting* SQL injections or about *preventing* SQL injections? If the latter, see [How can I prevent SQL injection in PHP?](http://stackoverflow.com/q/60174/53114) – Gumbo Mar 26 '14 at 15:43
  • 2
    If you're asking for a silver bullet, an easy-to-apply solution, I'm afraid there isn't one: you need to go through _each_ database query, check if it's vulnerable, and sanitize it. – Piskvor left the building Mar 26 '14 at 15:47

1 Answers1

3

I wrote about SQL injection defenses extensively in a presentation SQL Injection Myths and Fallacies (or listen to me present it in a free webinar at Percona.com).

There's basically no silver bullet, but there are several standard methods of defense.

Penetration Testing

There are a variety of free intrusion detection tools available, such as WebScarab or w3af. These can be a good first step as a black-box testing technique for finding the first layer of vulnerabilities in your website.

Another good article on the subject is Web Application Security: Testing for Vulnerabilities from IBM DeveloperWorks.

Code Analysis

Can we scan the code of a whole website to detect SQL injection vulnerabilities? Yes, there are many code analysis tools available. Which one is best for you depends on the code language you use, and your development environment.

NIST has a pretty huge list of Source Code Security Analyzers.

But even if you use intrusion detection and code analysis tools, remember the old wisdom about testing: Testing shows the presence, not the absence of bugs. That is, any testing is bound to be incomplete, so don't rely only on automated testing.

Code Fixes

Once you find a vulnerability, fix it. There are several coding techniques that help:

  • Escaping or filtering application variables before copying them into SQL code.
  • Preparing SQL queries with parameters in place of dynamic values.
  • Whitelisting content that can't be escaped or parameterized (like valid table names or column names).

These defenses are each very useful, but none work in every situation, so you must learn how to use each of them.

There's no substitute for careful code reviews. Basically, for any SQL query that uses dynamic content for part of the query (i.e. copying application variables or any external input into the SQL string), you must make sure that the content has been made "safe" by one of the defense methods I listed above.

Perl has a feature that supports this, for example. Every Perl variable has a meta-attribute of being "tainted" or not, and therefore unsafe to be copied verbatim into SQL (or executed as code with eval or even output to HTML, risking XSS issues). A variable is tainted if it takes any of its value from external, untrusted content. Or if the variable takes its value from another tainted variable (it's infectious). You can "untaint" a variable by filtering it through a regular expression matching function (this assumes you are responsible for making a sensible regular expression that strips off dangerous content).

Other languages don't have this tainted-variable concept, but you can do something similar by manual analysis, tracing the source of every variable that is copied into SQL strings.

Monitoring

Assuring 100% security all the time is incredibly hard. Even if you correct 99.9% of your app's inept handling of SQL, the last case could be the opening a hacker needs. And of course one of your inept developers might introduce another vulnerability tomorrow.

Any security plan has to include constant monitoring for suspicious website behavior. Many of the worst SQL injection disasters were so bad because they went undetected for months while the attackers siphoned away valuable data.

So you have to keep logs of SQL queries, and watch for patterns of invalid queries. For example, keep a whitelist of known SQL query types that your application code runs. If any SQL queries appear in the log but don't match the whitelist, it might be an illicit query run by an attacker. For more on this idea, see Using the Percona Toolkit to Detect and Even Prevent SQL Injection Attacks.

There are also SQL injection proxy products like GreenSQL that can help to monitor or whitelist SQL traffic transparently.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • The [term *intrusion detection*](http://en.wikipedia.org/wiki/Intrusion_detection_system) is rather used for software that is detecting attacks rather than detecting vulnerabilities. – Gumbo Mar 26 '14 at 22:28
  • @Gumbo, yes, that's a good point. Would you be all right with the term [penetration testing](http://en.wikipedia.org/wiki/Penetration_test) in the context we're talking about? – Bill Karwin Mar 26 '14 at 23:11
  • I’d rather call it *security auditing*. – Gumbo Mar 26 '14 at 23:18
  • I found this document, [Security Auditing: A Continuous Process](http://www.sans.org/reading-room/whitepapers/auditing/security-auditing-continuous-process-1150) from noted security organization SANS Institute. The scope of security auditing seems much more broad than testing a web app for vulnerabilities. Of course it's important and worth doing -- but it's not specific enough to fit the OP's question (which was already closed for being too broad). – Bill Karwin Mar 26 '14 at 23:37