SQL Injection Attacks Increasing 384
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.
How difficult is it. (Score:4, Informative)
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. (Score:2, Informative)
Re:How difficult is it. (Score:5, Informative)
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. (Score:5, Informative)
Here is an example, taken straight from PDO's page:The framework is there, PHP developers need to make use of it, but sadly things like the following are still common:
Solution for PHP programmers (Score:3, Informative)
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"?!? (Score:3, Informative)
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 (Score:5, Informative)
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 (Score:4, Informative)
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 (Score:5, Informative)
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:How difficult is it. (Score:3, Informative)
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 [slashdot.org] (Or a similar book/online reference) before using PHP, otherwise there is a very good chance they'd end up with vulnerable code.
Re:Hard for Devs? (Score:4, Informative)
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:
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:How difficult is it. (Score:3, Informative)
--trb
Re:Hard for Devs? (Score:3, Informative)
Here's an example of parameterized queries in Java:Need to insert more? Reuse the prepared statement
Re:How difficult is it. (Score:5, Informative)
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. (Score:5, Informative)
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.
Dynamic 'WHERE' clauses (Score:5, Informative)
- Stored Procedures
- Parameterized Queries
- 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 [cmu.edu] (pops in a new window)Re:How difficult is it. (Score:3, Informative)
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. (Score:3, Informative)
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:Injection preventation doesn't need input check (Score:4, Informative)
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 [php.net] (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 [php.net].
Simple Solution (Score:1, Informative)
Re:How difficult is it. (Score:1, Informative)
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?
Ah it's got to be more sophisticated than this... (Score:2, Informative)
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 (Score:3, Informative)
Re:How difficult is it. (Score:3, Informative)
It is both easier, and much more secure.
Re:you NEED that half of your ass (Score:3, Informative)
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 (Score:3, Informative)
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:How difficult is it. (Score:3, Informative)
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. (Score:3, Informative)
With a more strongly typed language, there is no excuse for passing unescaped strings.
Re:How difficult is it. (Score:5, Informative)
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. (Score:2, Informative)
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 [sourceforge.net] 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:What about magic_quotes_gpc (Score:1, Informative)
Use your database vendor's string escape functionality.
In conclusion, fuck magic quotes.
Re:How difficult is it. (Score:3, Informative)
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=mypw
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)