SQL Injection Attacks Increasing

An anonymous reader writes "Help Net Security has a story that covers the dramatic increase in the number of hacker attacks attempted against its banking, credit union and utility clients in the past three months using SQL Injection." Article follows up on press release with a little more information. Not a lot here shockingly surprising, but it's worth mentioning that SQL injection is a real pain for web developers. You have to be very careful about checking user input.

Comments

How difficult is it.

[El_Muerte_TDS]

Simply forcing request variables to the correct type and escaping all strings is pretty much the only thing you need to do.
Most languages provide the functionality to do that (in php: intval() for all integer request vars, and _escape_string() for string data.).
It's just a small amouth of work, yet a lot of people are way to lazy.

Re:How difficult is it.

[Goaway]

Or, you could use a language that doesn't force you to do this by hand, which is pretty much every langauge except PHP.

Re:How difficult is it.

[eggoeater]

Simply forcing request variables to the correct type and escaping all strings is pretty much the only thing you need to do.

Or you could just use stored procedures.
I've been doing that for years without any problems.
I've also never had any issue with "business logic". I can keep my business logic
seperate with stored procs. (I never understood that argument against them.)

Re:How difficult is it.

[aymanh]

PHP doesn't force you to do that by hand, you can make use of the numerous database abstraction layers for PHP, like PDO or PEAR::DB.

Here is an example, taken straight from PDO's page:

$stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (:name, :value)");
$stmt->bindParam(':name', $name);
$stmt->bindParam(':value', $value);
 
$name = 'one';
$value = 1;
$stmt->execute();

The framework is there, PHP developers need to make use of it, but sadly things like the following are still common:

mysql_query('SELECT value FROM REGISTRY WHERE name = "' . $name . '"');

Re:How difficult is it.

[aymanh]

PDO is built into PHP 5.1. PEAR::DB is part of PEAR which is often installed by web hosts, and it's compatible with both PHP 4 and PHP 5.

However, I agree with you, PHP should have had a DB layer from the start, another problem with PHP is that it attracts uneducated users who read a couple of PHP/MySQL tutorials before writing their first vulnerable query, that's why I believe one should read Essential PHP Security (Or a similar book/online reference) before using PHP, otherwise there is a very good chance they'd end up with vulnerable code.

Re:How difficult is it.

[(trb001)]

I'm on a project where they tried mandating everything be in stored procedures. For truly dynamic querying, it's just not feasible. We have one query where the user can input around 30 pieces of data and they're all optional. A query like that would be painful to write in a stored procedure, so for those we have parameterized SQL. Parameterization solves the problem just the same and allows flexibility to create SQL on the fly (not: we're using Sybase, not Oracle. Don't ask why.)

--trb

Re:How difficult is it.

[beavis88]

1) Use a sensible naming convention. eg P_User_Create, P_User_Delete, etc. Use the naming conventions to effectively categorize your stored procs. This takes a little planning and discipline, but what "best practices" don't? The "intellisense issue" is a red herring IMHO - if anything, you're worse off in this regard without sprocs.

2) USE SOURCE CONTROL. Without trying to be nasty, you're insane (or a one man operation) if you use the database as your authoritative source for stored procs. If you have any environments beyond a production server, the ability to script installation/alteration of procs is essential.

Re:How difficult is it.

[CaptainZapp]

Sure you could have files with all your stored procedures in them

Bingo!

but then you have to have 2 copies of everything.

Stored procedures (like any DDL statements to set up your database schemas) should be handled like any other source code and treated as such. This includes version control

There seems this "but I can pull it out of the database with my super GUI tool, so why should I keep it on file too?" attitude. Well, duh; it's mighty hard to pull anything of a database whoms disk just crashed.

For recoverability reasons database objects (including stored procedures) should be scripted and version controlled. Period.

Re:How difficult is it.

[hey!]

How difficult is it? Well, like most things in life the real answer is it depends.

In this case it depends on how many kinds of things you want to do with data. For simple stand alone applications, like a blog or something, it probably isn't much. Most insertions, updates and queries probably happen behind a DAO pattern anyway; it's easy to enforce semantic checks there, and it's no big deal if the data is stored in some kind of garbled looking encoding. But in the post internet bubble world, databases have been relegated to a non-sexy supporting role, and people have forgotten what databases are about: creating reusable data stores. You can't be sure where data is from when you fetch it from the database, or where it is going to when you put it there. So, you should probably always escape strings before using them in updates or inserts. But the result will very likely be ugliness elsewhere.

The steps you are advocating are OK. But I'd go further. I'd say you should never hand a string to the database tier to be executed, no matter how much you think you've checked input. I think it would be wise to hesitate to hand a string constructed with no user input to the database tier. In other words, you should only use prepared statements; the APIS that doe this should be deprecated, or better yet just yanked out. And those prepared statements should not be prepared from strings that are on the stack, either. There are two reasons for this. The first is that you can't trust malicious input not to have access to the stack. The second reason is that you really can't trust any data that is in your memory space unless you have checked it thoroughly, even if it is NOT user input. For example, you fetch a piece of data from the database, and incorporate it into a string, which you send to the database interpreter. How do you know that string data you got from the database was properly escaped? The answer is, you don't.

So, the steps you advocate are partly good (escaping strings) and partly not nearly enough (explicit type casts for non strings).

Re:How difficult is it.

[eggoeater]

Just what I was going to suggest with one more:

3)600 procs?? It sounds like you've put too much in one database. I've seen groups do this and it usually leads to scalability problems. I'm not talking about multiple servers; just spliting things up catagorically into multiple databases in the same instance. In sql server they're called databases, in Oracle they're called schemas....not sure about db2 or sybase.
The end result is you have all your customer related data and associated objects (views, stored procs, etc.) in one database and all your product data and associated object in another... etc.
As long as they're running in the same instance, there's no performace impact.

Re:How difficult is it.

[Goaway]

I am neither arguing for Perl in particular - Python and Ruby have pretty much the exact same kind of database interface - nor does that kind of interface stop you from manually constructing your own queries. However, you will have no reason whatsoever to do this, because the proper interface with automatic escaping is much less of a pain to use than constructing your queries by hand.

If you're such a hot-shot programmer, why are you using PHP in the first place? Why not write lightning-fast code in C instead? If you're trading speed for convenience by using a high-level language, why wouldn't you want to use something that is even more convenient? And, I might add, faster in many instances?

Re:How difficult is it.

[Goaway]

You've never used Perl's or Python's database interfaces, have you? You use placeholders, and pass in values separately, and the interface itself takes care of proper escaping. You use a constant string for your query and don't build the SQL query by hand.

It is both easier, and much more secure.

Re:How difficult is it.

[misleb]

No, you don't. If you're using Rails, for example, the majority of database queries are handled through ActiveRecord which escapes variables for you. And where you need to build custom queries or query part, you use constant strings like: ["SELECT * FROM table WHERE field = ?", params[:valuefromform]]

No need to run any silly escape functions as long as you use constant SQL strings and let the framework build your query strings.

-matthew

Re:How difficult is it.

[SatanicPuppy]

You should never pass an unescaped string to anything. End of story. Even php has addslashes() and stripslashes(), and even though they're a kludge, they still work.

With a more strongly typed language, there is no excuse for passing unescaped strings.

Re:How difficult is it.

[Aceticon]

Or you could just use stored procedures.
I've been doing that for years without any problems.
I've also never had any issue with "business logic". I can keep my business logic
seperate with stored procs. (I never understood that argument against them.)

If your stored procedures are only very thin layers encapsulating low level database access operations (thus not much more than pre-packaged selects, updates, inserts and deletes) you should have no business logic in the database problems.

On the other hand there's a couple of downsides to such a design:
- It requires developers with a good level of expertise in both the language used to develop the core of the application and the one used for the stored procedures. This is true both for initial development and for maintenance.
- It makes an application tightly couple to the database. If you want to port to another database, at the very least you will have to redo all the stored procedures.
- It increases the likellyhood of having version conflict problems between the core application and the database application components. More specifically, the data-model is usually more stable across versions of the application than the actions executed on data in that model (eg "find all employees in more than Y departments and whose manager is level X"), and thus if you store in the database code which is tightly couple to the actions that the application executes on the data then previous versions of the database (for example, those restored from a backup) are not likelly to work with the lastest version of the application (nor are they likelly to be easilly "fixed" by a DBA).
- It's harder to debug code that crosses platforms and languages

More in general, the problem of SQL injection can be avoided simply by using prepared statements or any other type of SQL query that takes input parameters instead of using string concatenation to make SQL queries that include the input values.

Using stored procedures to solve this specific problem is very much overkill.

Beyond this, the only good reason i can see for using stored procedures like this is for performance reasons if you do some level of post-processing on the results or some sort of "smart" block updating of data. In this case, stored procedures should only be used in a very small number places (to solve high-impact IO bottlenecks between the application and the database) and not in a generic way.

The last couple of reasons i see for such a design are:
- A "job protection" measure by locking the application to the specific skillset combination of a specific developer
- Because the developer prefers-to/is-more-confortable-with developing code in the database that in the core application
Hardly good reasons IMHO

Re:How difficult is it.

[ftsf]

actually PHP does do this, if you use PostgreSQL, which is a much nicer solution than using MySQL anyway

pg_execute("SELECT * FROM blah WHERE meow = ? AND octopus = ?", array($meow, $octopus));

and then of course there are the numerous abstraction layers like peardb and adodb which work quite beautifully at a bit of expense of speed, but makes up for it in portability if you need to change database backends.

Re:How difficult is it.

[mattyrobinson69]

SQL Injection attacks are performed like this (using HTTP GET instead of POST as its easier to explain):

www.mysite.com/login?username=dave&password=mypwd

you would do something like and see if a 'username' was returned:

"select id from users where username='{$_GET['password']}' and password='{$_GET['password']}'"

To attack that code, you would do this:

www.mysite.com/login?username=admin&password=mypwd '+or+test='test

that would run this code:

"select id from users where username='admin' and password='mypwd' or test='test'"

which would always return an ID, whether the password was correct or not, as test is always equal to test,

if you ran this:

$un = mysql_real_escape_string($_GET['username']);
$pd = mysql_real_escape_string($_GET['password']);
$db_query = "select id from users where username='$un' and password='$pw'";

the query being executed would be this:

select username from users where username='admin' and password='mypwd\' or test=\'test';

which would only return the username if the password was actually mypwd' or test='test, which is unlikely

(sql counts \' as a printable ' char, without interpretting it as a quote)

Solution for PHP programmers

[ylikone]

Make sure you specify where you get your incoming data from, like using $_POST, $_GET, $_SESSION, etc, don't just grab them from the air (with globals on).
Make sure you use mysql_real_escape_string() on all incoming data that is headed for the mysql database (to get rid of SQL injection).
Make sure you use strip_tags() on all incoming data that is headed for output on your page (to get rid of cross-site scripting).

Re:Checking input is a "pain in the ass"?!?

[Bogtha]

Web programming is never presented in that light; it's always, "here's a quick little script that fetches twenty records from a database and displays them."

It's actually worse than that, not only is security not adequately discussed, in a huge number of cases, sample code is given that is totally insecure. Newbies are being taught to write insecure code by ignorant tutorial authors.

I'm not sure why, but there's something about web development that makes people with the tiniest amount of knowledge think that they are an expert that can teach others. I've lost track of the number of "OMG Learn PHP!" tutorials that provide code that only barely manages to operate.

No no No no No no NO

[IPFreely]

You don't need to escape strings.

Just don't build your query on the fly.
Bind ALL parameters to placeholders in a prebuilt query. Binding is an instant kill for any SQL injection attack. It is also much more effecient on many databases.

Re:Use PreparedStatements with Java

[IPFreely]

PreparedStatements can be immune to SQL Injection based attacks.

You should stipulate that you must bind all parameters to placeholders. You could use PreparedStatement the same way as Statement and have the same problem. Bind all parameters, no matter what language you are using.

Re:Use PreparedStatements with Java

[(trb001)]

Additionally, make sure you use PreparedStatements/CallableStatements correctly. I've seen people mark up a PreparedStatement like this:

String SQL = new String("select * from user where username = '" + username + "'");
PreparedStatement statemnet = connection.prepareStatement(SQL);

That does *nothing* for you, and is just as insecure. Instead, make sure you use parameterized statements:

String SQL = new String("select * from user where username = ?");
CallableStatement cs = connection.prepareCall(SQL, ...);
cs.setString(1, username);

Most databases treat the two very differently. In the second case, the database compiles the statement and then compares the username field with your value. In the first, your value is inserted and then compiled, allowing injection.

--trb

Re:Hard for Devs?

[Bogtha]

I am curious what language automatically checks your users input for any attempt at SQL Injection.

You're approaching it with the wrong mindset. A database API shouldn't check for SQL injection attempts, it should encode the input appropriately. Avoiding SQL injection attacks is just a subset of correct operation, as anybody with an Irish surname could tell you.

As for an example, well with Python's DB-API 2.0, you write code like this:

cursor.execute("select foo from bar where baz = %s;", (quux,))

It doesn't matter whether quux has apostrophes, it gets automatically escaped because the API is designed as an interface to input data, not an interface that accepts data that has been specially prepared and cannot be distinguished from data that hasn't been specially prepared.

Re:Hard for Devs?

[phasm42]

There is no "automatic checks" -- other languages simply support prepared statements, which sidesteps the entire problem. No escaping necessary, just use a parameterized SQL statement. They also support the standard string concatenation method, but prepared statements are there from the start, and many examples make use of this. Although there is a package for PHP to support parameterized SQL, all the PHP I've seen simply uses string concatenation.

Here's an example of parameterized queries in Java:

PreparedStatement ps = connection.prepareStatement("insert into USERS_LIST (USER_ID, USER_NAME) values (USER_ID_SEQ.nextval, ?)";
ps.setString (1, userName);
boolean status = (ps.executeUpdate() == 1);

Need to insert more? Reuse the prepared statement

for (String userName : users)
{
ps.setString(1, userName);
numBad += (ps.executeUpdate() == 1)?0:1;
}

Dynamic 'WHERE' clauses

[TheRealBurKaZoiD]

I think one thing everyone is overlooking, and I didn't see it mentioned before I posted, is that alot of newbies, and even intermediate SQL developers either can't use stored procedures because they're using some old version of MySQL, or they have problems writing stored procedures that include dynamic WHERE clauses, or they just don't know that you can do that. It's been my miserable privilege to have seen some pretty goddamn bad SQL code in my life, code that was so bad it would make you physically ill, simply because the developer didn't know any better. Remember kids:

1. Stored Procedures
2. Parameterized Queries
3. Learn the SQL-92 Specification (so that you're familar with the language beyond just SELECT, INSERT, UPDATE, and DELETE. There are all kinds of things out there to help you get rid of that dynamic code, like COALESCE, and CASE WHEN, etc.)

Here's the SQL-92 Specification (pops in a new window)

Re:Injection preventation doesn't need input check

[julesh]

are there web application frameworks which don't support parameterized SQL statements?

that would be PHP.

Quit spreading FUD. PHP supports parameterized SQL just as well as any other language I've worked with. See, for example this doc page (search for "Example 2"). Even for databases whose native C APIs don't support the feature (i.e. MySQL), the database abstraction layer PEAR::DB that is distributed with PHP provides emulation.

Simple Solution

[Anonymous Coward]

They're called bind variables. Use them and SQL injection attacks go away.

Ah it's got to be more sophisticated than this...

[pookemon]

I did a quick google and found this as a description for sql injection. I would think that, at the very least, if you handle all your strings (and numbers) properly then this problem goes away. Say you have a field "LastName". If you just concatenate the value entered into the field into your SQL then you're asking for all kinds of problems (Any O'briens etc. out there?).

For all my fields I use a simple function to ensure that the data being put into the query is safe for the query (Replace(foobar, "'", "''") - for SQL Server). For numeric values, well, you just make sure that they are numerical as part of the validation (or you limit the characters they can type into a numeric field).

Re:What about magic_quotes_gpc

[eluusive]

This doesn't fix the problem as there are some vulnerabilities in it with regards to unicode.

Re:you NEED that half of your ass

[_xeno_]

For example, if you are expecting an integer between 1 and 3, you still need to do input checking.

You don't need to, that's what constraints are for in SQL.

Yes, you should still check to make sure the integer is a proper value so you can display a good error message, but if data is supposed to be constrained in some way, you really should have that constraint specified in the SQL schema itself. SQL provides tools for ensuring data integrity, they should be used!

Runs off to check latest MySQL documentation

OK, SQL databases that aren't MySQL provide methods for placing constraints on columns and they should be used. Apparently MySQL 5.1 still doesn't and still documents how MySQL will "coerce legal values" if you try and input something illegal, like a NULL in a NOT NULL column.

Re:I Do Web Programming For A Major University

[CyborgWarrior]

I have sent numerous emails to the sysadmin as well as to my boss. The response is that they are working on constructing a second server (have been for at least 2 months now, perhaps longer). It's not expected to be up and running for quite a long time yet. I have kept all of those emails as well. The problem ends up being that:

a) nobody sees it as a big priority, and since "something is already in the works" that's good enough for them.
b) I'm a student and I am arguing my case against a "professional staff member".

Perhaps that is scape-goatism and perhaps I do need to be more of a squeaky wheel but at some point it comes to the point that I'm just annoying, and since its easier to get rid of me because I'm just a student, thats the end of it. I would rather stick around, continue to squeak, and write code as well as possible (previous programmers have not paid any attention to the potential for exploit) with what is available and have a good idea of what does need to get fixed as soon as the proper tools are available.

I actually just came across PEAR's MDB2 package (thanks to someones mention in this thread of PEAR::DB, which is currently legacy and being phased out) and if I can get all of the dependencies to work on that and it can pre-compile queries for me without PHP 5, then that is a much nicer patch for the time being.

Re:What about magic_quotes_gpc

[Anonymous Coward]

Fuck magic_quotes_gpc. From the docs: 'Magic Quotes is a process that automagically escapes incoming data to the PHP script. It's preferred to code with magic quotes off and to instead escape the data at runtime, as needed.' (Source)

Use your database vendor's string escape functionality.

In conclusion, fuck magic quotes.