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

Another web developer pain...

[Anonymous Coward]

Sudden traffic surge from certain news sites can be a pain.

Re:Another web developer pain...

[professorfalcon]

It's a Slashdot injection.

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.

[Anonymous Coward]

"If your database interface doesn't suck completely, like PHP's default one"

Wow! How intelligent, I expect this to be modded up before I ever post...after all this is slashdot.

Quite honestly, as a programmer, I expect the applications to do as I ask them to, and not hold my dick at every opportunity. If I want something passed to a SQL statement in the way I've asked it to, I don't expect my data to be munged by the application to protect me.

I'm sorry, but this rash of piss poor programmers that don't know how to program, nor care to do any security on their own part is a problem unto themselves and not a symptom of an interfact that completely sucks. Folks that make blind statements about folks who suck generally are the ones wiping their lips afterwards from the sweet juice of man-gina.

I've been programming for nearly 20 years. I've used probably a dozen languages and every year I hear from the kiddies about how much more one app needs to do for you than the other. And usually its a bit more convenient and thus I generally adopt the language that helps get things done quicker. At the same instance, I never forget its me that has to be assured of the security and understand the lower level concepts so that if someone isn't taking this into consideration with this particular release of their language you'll be fucked (and its happened to me before in off version releases of 'secured' 'programming languages' -- luckily I was immune in most instances).

So if anyone is missing a point, its the guy stating there is only one database iterface for Perl, the guy that believe perl is a decent language to write in (in my 20 years of programming and teaching an upper level computer science theory course at one point), perl has been the only language I've decided to entirely skip after realizing how bad it really was and the fact it was designed solely to appease geeks that wanted to repell the opposite sex. You'd find more readable code programming in Klingon, to put a statement that you might understand.

Seriously, if I had mod points today, I would have simply modded every post of yours down today, but I decided to respond anonymously, and I hope mine gets modded down just as yours does. If you are going to write something ignorantly, write it anonymously where most of us can ignore it.

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]

In SQL Server you can do something like this (there's a way to do this in Oracle but I forget. Not sure about sybase.)

create procedure myTestProc @someDateTime datetime = '1/1/2050' as
--put insert, delete, update here....
select * from someTable
where (@someDateTime >= someTable.someDate or @someDateTime = '1/1/2050')

The where clause basically says, if the optional parameter is not the default then check it, otherwise ignore it.
I've never had 30 optional parameters but I've had quite a few and this
trick has allowed me to condense many statements into one.

If this trick doesn't work, you can also use IF statements and keep everything in one stored proc instead of multiple.

Re:How difficult is it.

[ubergenius]

PHP has always had this problem, and it always will, because the major reason why PHP is so prevalent on the web is because it is highly accessible to all users, even the most uneducated and unknowledgable individuals, allowing anyone to make a dynamic webpage. However, just as PHP allows anyone to write easy code, it also allows anyone to write proper, secure code for those who understand how to do it. Once again, the problem lies between the keyboard and the chair.

Re:How difficult is it.

[countach]

Stored procedures buys you nothing in security. Just bind all variables, and you can dynamically create to your hearts content.

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.

[Tony Hoyle]

Yes you do. No matter what language you use if you take user input and put it in an SQL string you're asking for trouble. It's not the language that's the issue it's the programmers.

Re:How difficult is it.

[mdboyd]

Agreed
I definitely don't think PHP is to blame for SQL vulnerabilities. Using it as a scapegoat most likely means you have no idea what you're talking about.
If I had modpoints, I'd mod your post up for all PHP haters to see.

Re:How difficult is it.

[Anonymous Coward]

"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?"

Because the modern CPU has rendered the need for compiled languages pretty much to nothing.

There are times I will revert to a C backend for functionality that needs massive processing without a lot of connection to the outside world. I've done this on a recent project where I needed to analyze text to parse into a synopsis. Early prototypes of the workflow used both PHP and Perl because I like the readability of PHP but one of my lead developers likes the textual nature of Perl...I actually agreed with him on this point and allowed him to design the prototype in this so that we could tweak the algorythm in realtime without having to do a 30 minute compile each time -- which is about how long it takes to do the final C routine. As a development and prototyping language, it worked out, but was slow. It was also very hard to understand mixed with idiosynchratic perl and higher level mathematical formulae to derive this. The C was much cleaner.

If it wasn't for the textual nature of Perl, I would have never allowed it to be used. I ended up keeping a second set of code developed in parallel that by the end was actually easier to maintain and faster than Perl in PHP. Both were several factors slower than the compiled application regardless of how you looked at it.

"why wouldn't you want to use something that is even more convenient?"

Because convenience doesn't mean that you shrug off the responsibility of protecting your code, or using good practices simply because someone else might have put it into their application. If you are doing simple queries, you can easily encapsulate your request and be done with it. Fuck, thats what stored proceedures are for and you don't need any fucking programming language to do that correctly. Work with anything that requires more than a single join and a simple where statement and you give the user the ability to shape this, and you've gone beyond the ability of stored proceedures and views or the simple data encapsulations that you are offered in these languages. Probably why I see all these Perl and RoR applications that make a hundred calls to the database to get one tables worth of information and I find this a limitation.

There are reasons and advantages to use a lot of languages...folks that don't understand this are doomed to keep using the same tools that they did 50 years ago with no advancement.

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.

[Anonymous Coward]

So I assume database driven sites should just not allow the user to enter any input, then it'd be 100% secure!

Maybe Slashdot should implement that policy, at least we wouldn't have to read ignorant shit from you any more.

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.

[ttfkam]

PHP succeeds an older product, named PHP/FI. PHP/FI was created by Rasmus Lerdorf in 1995, initially as a simple set of Perl scripts for tracking accesses to his online resume. - PHP history page

Let me get this straight: you are condemning a programming language but championing the language it spawned.

As for your comment:

Quite honestly, as a programmer, I expect the applications to do as I ask them to, and not hold my dick at every opportunity. If I want something passed to a SQL statement in the way I've asked it to, I don't expect my data to be munged by the application to protect me.

Bwahahaha!! Since when is escaping a single-quote considered an attempt to "hold your dick"? Simple string concatenation for the creation of database is always a bad idea, even for 20-year veterans like you. The last time you were coding at 4am, were you as sharp as you were at midnight?

Also you are falling into the same pseudo-libertarian trap (tripe?) that many programmers seem to these days. You think that as long as you are doing the right thing, who cares what someone else does? In fact, ridiculing others is a sufficient solution to most problems.

It's not.

SQL injection attacks affect me when it's my bank. When was the last time you personally interviewed the web development staff at your bank or credit union? How do you know they are as good as you are? Considering the fact that binding variables is as fast or faster than simple string concatenation in most cases (in some cases, they can be converted to stored procedures transparently on the back end), I have exactly zero problems with a language "holding some dicks" in the name of security. Especially since there is no speed loss in the process.

Correctness, not "what works." It's the difference between modern chemistry and alchemy. You might end up with the right result, but only with trial and error... mostly error.

But perhaps this all points to a greater Slashdot problem: too many people who refuse to get their dicks held once in a while. In more ways than one. ;-)

Re:How difficult is it.

[Ender Ryan]

This moron is defending PHP, which was spawned FROM Perl, borrowing the worst parts of the language, and ditching everything decent, like, LEXICALLY SCOPED VARIABLES, Unicode support, standard naming conventions for builtin functions, etc.

Someone who has "skipped Perl entirely" while using PHP, certainly needs to check their head.

For any doubters:
http://tnx.nl/php
http://czth.net/pH/PHPSucks

PHP - training wheels without the bike indeed.

Re:How difficult is it.

[Firehed]

I think part of the problem is that many of the coders, myself included, aren't that familar with how SQL injection attaks are performed, and thus don't know great ways to go against defending them. I'm about to undertake a fairly hefty PHP-based project (really just for the hell of it), and while I know some basics for how to avoid problems, I don't have a clue how the injection attacks are performed, so I can't easily go about trying one on my own pages to see if it works or not. Of course the site will be designed in such a way that, by and large, the only people with a likely threatening level of access to the database are the administrators, and I doubt too many would want to attack their own site. Thanks, grandparent poster, for pointing out that there are books on this very subject - I'll actually probably try to pick one up in the next couple of days.

Whenever I write my code, I try to do so in a way that the person using the site can't screw it up through ignorance. Meaning I don't want to force the user to escape characters, etc. The lucky side-effect of this is that it tends to act as a safeguard against attacks just as much as it guards against morons. As I said, I'm not especially familar with the subject yet, but going by just what I've read in the comments in this thread, I've got the impression that it's effectively people intentionally using non-idiotproofed things to give some mighty abnormal results. Regardless, it's a PEBKAC issue, the question is which end it's on. Whenever the code relies upon the user doing things correctly, you're asking for problems, whether or not they're intentional.

A fairly quick googling tells me that use of functions such as addslashes(), htmlentities(), htmlspecialchars($str, ENT_QUOTES) (to force it to parse both single and double quotes; depending on your coding style that may not matter), and mysql_real_escape_string() can help, if not entirely fix, the problem. Hell, even something as simple as a str_replace($input, '\'', '''); and/or str_rep($input, '"', '"'); could go a long way.

I'd like to think that it's a relatively safe bet to assume that if your code is idiot-proof, it's also smart-people-proof (just because they're script kiddies doesn't mean they're dumb, and they're certainly smarter than people who wreck their database accidentally using the same method).

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)

Injection preventation doesn't need input checking

[Killeri]

"SQL injection is a real pain for web developers. You have to be very careful about checking user input." Say what? All you have to do is use parameters, not string catenation. Of course, checking the user input is good for other reasons but not for SQL injection attacks. Or are there web application frameworks which don't support parameterized SQL statements?

Qualifications

[Chris Graham]

Perhaps all programmers working on professional database systems should have to get a professional qualification to show that they can write secure code. I wouldn't say the same should be manditory for things like usability or stability (except for special sensitive areas), but being able to write code that actually allows serious danger without qualification is pretty weird. Builders need qualifications, electricians do, gas installers do, ...

Re:Qualifications

[Chris Graham]

That's a fantastic point! Let's force politicians to be able to pass a democratically voted-for test before they can get office. It would include basic history, requiring an understand of, for example, how the Nazis got into power. Or they could be checked that they know a rough summary of the current budget.

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

[fractalus]

The only people who consider it a pain in the ass are people who are (a) lazy, (b) not adequately security-conscious, (c) programming without a framework that provides good tools to do this. The reason we have so many SQL injections is because we have legions of web programmers who were never taught how to write code in a hostile environment. 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." Security is far too often a footnote or an appendix that beginning programmers never get to. Building apps for the web is not like doing your Data Structures I homework. You need a different mindset. It's a lot more like designing locks--for prisons full of inmates eager to get out.

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.

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

[gutnor]

The other category of people that consider that it is a pain in the ass are people that start working on an already existing project containing thousand of webpages developed in a time when security was no concern or when the application was not supposed to be made available on internet or when the application was supposed to be your team little private quick and dirty monitoring tools done by the boss kid during his internship.

There is a lot of legacy code and lot of code that was never meant to see a production server, not every developer has the opportunity to work only on new applications. For any webdeveloper nowadays, it is trivial to make a *new* website safe from web injection, however securing an old crappy one is non-trivial.

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

[ubergenius]

Hell, there are some PHP books out there that do this. I have been primarily a PHP developer for a while now (not because it's the only language I know, I just find it to be the fastest and easiest for me over my experiences with Perl, ASP, and *shudder* ColdFusion, but that's personal preference), and I have found that almost ALL the knowledge I learned from books from the beginning of my experience with PHP is virtually useless nowadays (with the exception of the basics, obviously, like printf() and such). I had to relearn the language as I went along, and I didn't develop a proper security sense of the language for quite a while. This is funny considering the books I read for learning other non-web languages, such as C, C++ and Java, were written with a much more solid foundation, and I still find the knowledge learned in those books useful today. Maybe it has something to do with the web language publishers.

Hard for Devs?

[CHR1S]

How can it be that hard for web developers to check data before it is submitted? I wouldn't imagine trusting the data that an anonymous user can enter into my website.. so maybe I'm just trained to check data. Of course, I'm also glad I use MySQL with PHP where a simple mysql_real_escape_string can prevent any popular SQL Injection attempt.

Re:Hard for Devs?

[Goaway]

You're glad that you use pretty much the only langauge where this is not done automatically for you, but which instead forces you to use a function with a name like mysql_real_escape_string()? And that actually has a similarly-named function without the "_real_" that doesn't do the job right? Just kidding with that other one, here's the real one!

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;
}

A pain for who exactly?

[MosesJones]

but it's worth mentioning that SQL injection is a real pain for web developers

Which web developers would these be? MuppetsR'US ? SQL injection is a pain if you take the input and lob it directly to the database without doing any sort of validation that the information is sensible.

Its a great example of all those people who scream "THIS IS SO MUCH QUICKER TO DEVELOP IN THAN THE OLD WAY" and then bite it after the system goes live.

SQL injection isn't a pain, except for those who think they've found a new quick magic bullet that solves all the problems and the old fuddy duddy practices are now all redundant.

Input checking is a half-assed solution.

[cduffy]

Checking input for escape attempts is error-prone. Passing in parameters as bind variables *isn't* error-prone (with regard to blocking SQL injection attacks); makes string quoting completely moot; and can result in a massive performance increase (particularly against Oracle) to boot.

I continue to be in disbelief that anyone doing professional database work can *not* follow this widely accepted best practice and continue to be employed.

Use PreparedStatements with Java

[sbrown123]

If your webapp is Java based, use PreparedStatements. Never use Statements. PreparedStatements are immune to SQL Injection based attacks since the variable replacements are never interpreted. PreparedStatements are also much, much faster.

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

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:Solution for PHP programmers

[Bogtha]

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

Please don't do this, it's bloody annoying when half your input gets chucked away because you used a special character. I really don't see why that function ever existed, it's a total fuckup and completely unnecessary when things like htmlspecialchars() exist. Encode your user-supplied data properly, don't simply chuck bits of it away.

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:No no No no No no NO

[wandernotlost]

That bears reiterating. If you are passing user input to a database in anything but a bind variable, you are incompetent. Period. End of story.

I've seen it so many times. Why do programmers think that it's a good idea to write their own escape routines when every database has a facility for denoting what is variable data and what is not? Unbelievable.

Re:Hooray for PHP!

[baadger]

Since when is it the job of the language to protect you from SQL injection? I think you're confusing the language of PHP with the standard libraries it ships with, mysql_*() and co. It's worth noting that PHP *does* support prepared statement's using the 'new' object oriented mysqi interface much like the Perl DBI. This handles the casting of types and escaping of strings for you.

Re:I'm not very experienced with SQL Security...

[hawkinspeter]

What about people who have surnames like O'Neil - would you try stripping out the single quotes or would you insist that people use the escaped SQL form O''Neil? The correct way to foil SQL injection is to use parameters.

Re:Hooray for PHP!

[Goaway]

Of course it's the job of the language to make it as easy as possible to write secure code, and as hard as possible to write insecure code. That should be blindingly obvious, especially for a language that's pretty much aimed at people with little programming experience who are likely to have no idea what they're doing.

Re:serious question

[cnettel]

There are some possibilities if some part of your stack is using UTF-8, for example. What one portion doesn't interpret as a ' will effectively hide or be translated into ' at a later point. You can come up with more variations of the basic idea.

"Careful" vs. "correct"

[jc42]

You have to be very careful ...

This phrase is a common tipoff to one of the main problems.

The computer doesn't give a damn how careful you are. If you spend hours carefully crafting a chunk of code that, through your ignorance, has a big security hole, all your care hasn't helped a bit. You have merely produced bad code.

OTOH, someone with good knowledge of the subject might toss off a 30-second routine that, due to their understanding, is highly secure.

Carefulness has little to do with doing a good job. Carefully doing it wrong is merely doing it wrong, no matter how careful you are. And doing it right is doing it right, even if you hardly gave it a thought.

What we need here isn't useless exhortations to "be careful". What we need is education about how code gets into trouble, and training in writing code that doesn't have problems.

Yeah, I routinely write code that checks input. But if there's some hidden gotcha that I don't know about (typically in some library routine that's not visible to me), I'm quite aware that my careful checking might do little good.

Multi-tiered approach

[Billosaur]

First rule of writing CGI: never trust the data! I work in Perl, and when an app is exposed to the outside world, I have to assume someone is going to try and get in through some hole if they can (or worse, will do something stupid that would have a negative affect oon my systems).

It starts with the web page -- validate input data. I know, I know, anyone can copy your page and rip out the JavaScript validation, but it doesn't hurt to put up a first line of defense. Next, before you actually use the data from the form for anythig validate it separately. In Perl, I have taint mode enabled by default for external apps and I treat all the data I receive as if it were dog crap. I massage it with regexes to make sure it is what it's supposed to be, and then pass it on to be processed. I find the best way to put up a wall is to have the form parameters sent to a validation script, then have the validation script call the script which would run the actual query, throwing back an error message to the user (and sending me a message in the process) if something's not right.

Data validation is really not that hard, especially if you know exactly what the inout is supposed to be. It gets iffier if the user can put in pretty much anything -- then you have to be a little more paranoid.

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:I'm not very experienced with SQL Security...

[LeRandy]

Except, the web is international. So "traditional" alphanumerics are not good enough. Or are you telling me that René should spell his name Rene? (in French, the two are pronounced completely differently - Ren (Rene) and Renay (René)) Or how about non-alphabetic languages like Chinese?

Many people use non-alphanumerics in their email. I, for example use underscore.

With the gradual movement of the web to non-latin URLs, too, the need for the acceptance of all printable Unicode in webforms has never been greater.

And as has often been pointed out, you can reduce the risk of your passwords being susceptible to dictionary attacks by using wierd (or perhaps unprintable) Unicode characters. Web & DB devs should do well to note that - I dislike sites immensely that restrict me to alphanumeric passwords - I'd like to use whatever alphabet I choose, to make my password more secure.

I'm not saying that input validation is a bad idea. It just needs to accept and validate input in any appropriate language - which for things like "Name" could be anything, even if the user is an anglophone. Some fields, like DOB, or numeric fields are easily validated - others like "Name" would be better cross-checked against a list of banned inputs, and escaped (or use parameters).

this doesn't match my anecdotal evidence...

[JeanBaptiste]

Often when I am on a page that looks SQL-injectionable, I'll try a few things just for giggles. I've been doing this for a few years now. I'd say that there are much, much fewer injectionable sites then there used to be...

SQL Euphoria

[digitaldc]

The last time I did a SQL injection, I hallucinated that everything around me was displayed in an orderly array.

How to make SQL injection impossible

[hypersql]

Many developers write code like execute("SELECT ... WHERE NAME='"+name+"' ...) because it's so easy, they are lazy, or because they are clueless. Many know that they should use bind variables, but not all (and peer reviews are not very common).

There is a way to solve SQL injection problems: Disallow text literals in the database engine. Or even, disallow literals (including numbers) at all. This could be a setting in the database that is on by default, and only off for certain applications (ad hoc query tools). What do you think about that?

I'm thinking about implementing this feature in the database I write (http://www.h2database.com/):

SET ALLOW_LITERALS 0 (no literals allowed)
SET ALLOW_LITERALS 1 (only numbers, text not)
SET ALLOW_LITERALS 2 (everything allowed)

This would be a persistent setting, and only an admin can change it.

(Of course there are other security risks, like using 'customer id' in URL or hidden fields in a web application. Or relying on Javascript data validation. I don't know what to do about those problems.)

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.

I Do Web Programming For A Major University

[CyborgWarrior]

I'm a student web programmer for the webdevelopment lab in a major U.S. University. The platform they basically told me I had to program on is PHP with MySQL. The server doesn't support anything else and getting the server guy to update or add anything new is a major pain and usually impossible. Point in case: I'm still working with PHP 4.1.2 and MySQL 3!!!!!!!

I still have to write some fairly secure applications (if they get breached there won't be any terribly sensitive information, but there are some things that we would rather be kept private (such as an online-store system for one of the on campus labs.... no purchases online, but the entire store (4000+ items in inventory) is there, along with purchase records, etc). I tried to get the server admin to either upgrade PHP and install the mysqli library so I could actually do compiled queries and all of that, but no go.... Maybe by next year -_- (the guy thought SQL Injections were a local exploit and then thought that since we were running over HTTPS it was okay....)

My point is that sometimes it is not the programmers fault that they cannot make use of the some of available options to make their application supposedly more secure. You have to do the best you can with what you have and write code that is put together well enough that when new features do become available on the server or someone does get breached and it needs to be improved, it is easy enough to upgrade and maintain the code. Right now I'm stuck using mysql_escape_string and type checking (heh, not even _REAL_escape_string...... the PHP version is that old!) and keeping my fingers crossed (and continuing to pester the admin hoping for better results faster). So don't always blame the programmers!! We do have to work within limitations too!

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.

you NEED that half of your ass

[oni]

Input checking is a half-assed solution.

maybe, but you need to do it anyway. You menton bind variables, and that's definitely something that people should do, but bind variables wont stop out-of-bounds inputs. For example, if you are expecting an integer between 1 and 3, you still need to do input checking.

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.

But ... but ... IT'S CHEAP!

[Opportunist]

You get what you pay for. A lot of people already suggested easy solutions to the problem that are just as easy to implement and that would immediately make the problem disappear. So why is it not done?

Simple: The people who write those insecure databases don't even know that those functions and features exist. Some ages ago, they learned a bit about SQL, maybe did a course about it (so they have a sheet of paper saying "Look, I can do it!") and that's it.

HR managers tend to go by papers, and by price. Now, who do you think is cheaper to hire? A person with a well rounded education concerning computers, programs and the fallacies, pitfalls and security issues around them, or someone who learned his SQL statements by heart and has no clue what exactly is going down inside the server?

Sure, both of them will create code that does what the specs say. As long as you only enter data according to spec (which is, interestingly enough, ALL that is checked, even under the SOA). The true quality of code is revealed as soon as you pit something unexpected and malicious against it.

Karma to burn: Why Slashdot is OK

[ursabear]

Mark me OT or mod me down with something, I'm fine with that.

The responses to the serious question post are an example of what's good about /.. In many circles, this question would have gotten "do you want me to write your code for you?" or "RTFM", or "Google (something here)", or statements that question the poster's value in the world. I learned something from the replies, and I appreciated the tone of voice of the replies. I, for one, am so glad so many smart people post here.

OK, back to your regularly-scheduled time sink...

Re:Hooray for PHP!

[husker shiznit]

Those statistics are pretty meaningless when you look at this.

http://www.google.com/search?hl=en&lr=&q=perl&btnG =Search
Results 1 - 10 of about 370,000,000 for perl.

http://www.google.com/search?hl=en&q=php&btnG=Goog le+Search
Results 1 - 10 of about 5,540,000,000 for php

So based on those numbers there should be more results for anything on PHP.

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.

No! Not stored procedures!

[ttfkam]

Or rather, you can use stored procedures, but that's not what is being discussed. We were talking about binding variables. Two different things.

• Stored procedure: a function that runs within the database server.
• Prepared/bound statement: something in code -- usually provided by the programming language's database layer -- that looks like the following:

UPDATE things SET alpha = ?, beta = ? where foo = ? and bar = ?

Then you set item 1 to some value, item 2 to another, etc. Other variations exist as well:

UPDATE things SET alpha = :alpha:, beta = :beta: where foo = :foo: and bar = :bar:

Here you can set values by name rather than by index. The implementation of this on the back end (where you should not be able to see it) may in fact be a stored procedure in databases that support it -- especially for SQL statements that are run repeatedly. However, even if the database doesn't support stored procedures, binding variables will always work since the issue is handled completely in code.

And then of course there are folks who don't want SQL anywhere near their code. That's when you may opt for an object-relational mapping library and/or stored procedures within a database.

Bottom line: escaping each time on your own is error-prone. Better to solve the problem right the first time. And it's easier than manually escaping/validating as well!

As a banking internet security guy . . .

[Kope]

It amazes me that there are banks out there don't do code reviews and pen-test to prevent simplistic attacks like this prior to rolling something into production?

God lord!

We require 3 layers of data validation (as part of the web interface, as part of the middle-ware layer, and within the database as triggered stored procedures for updates and inserts.)

Not doing this SHOULD be criminal in my mind.

Stored procedures - happy scaling

[phooka.de]

Sure you can use stored procedures. And sooner or later you might regret it:

- Your company merges with another company using a different DBMS and you're told the infrastructures should be merged.

- You business grows. While you can always add new application servers, J2EEs etc pretty easily, you'll have a hard time upgrading your DBMS over a certain point - and it's going to be more costly.

- Maybe something is twice as fast on the database compared to the application server. However, you will always have 1 database for all your application servers. So where will the bottleneck be? I'd rather have the operation take twice as long for each request - on each of my half dozend app-servers - than have it run twice as fast on my single database that's slowing down to a grinding halt while the app-servers are idling away. Bye bye minimum response time.

Once upon a time.

[sgt scrub]

I had a friend that was convinced her web front end to a database centric application was bullet proof. The user interface was accessed by clicking on a java script link which controlled the browser behavior. It brought up a browser window without toolbars. In the browser window all of the options were choosen via select boxes. Nothing new was added by the user through the application. She started to brag about the amount of code she didn't have to write to filter user input.

I started a tcpdump -xX port 80 and host her.host Because everything was being passed plain text we could see everything in the uri. After a quick nmap -vv -sV -P0 her.host I connected via telnet her.host 80 After the required http 1.1 hello stuff I started submiting commands to her cgi script; alpha characters instead of numeric, big decimal numbers, negative values... It didn't take long for her to decide to rewrite it.

Perl DBI is pretty good, actually

[Anonymous Coward]

Considering you have "completely skipped" Perl, it surprises me to read your comments on how bad its database interface is.

In fact, Perl's DBI is not only fast, but when used properly (variable substitutions, binding variables, etc) it works extremely well. Also the fact that everytime you change your data source (CSV, XLS, MySQL, SQLite, MSSQL Server, Oracle, PostGres, etc) all your functions don't change. You can always count on:

my $dbh = DBI->connect(@DSN, \%flags);
my $sth = $dbh->prepare($sql);
$sth->execute( @vals );
while( my $rec = $sth->fetchrow_hashref )
{
# Do stuff with $rec
}
$sth->finish();
$dbh->disconnect();

If you're not doing some kind of column binding or type-casting on your form-derived query arguments, you are always leaving yourself open to sql injection.

== IT competence decreasing

[runcible]

With the help of a whiteboard (!) I explained to about half a dozen ( okay, mostly junior ) developers and -- here's the real kicker for me -- *the three most senior members of out QA department, including the department head* that you could use the password

' or 1 = 1 --

for many, many sites on the Internet, regardless of user name.

The whiteboard came in when I had to explain *why it worked*...

_shakes head_

Its a pity...

[dcam]

.. because avoiding SQL injection is relatively easy to do.

1. Use only prepared statements or stored procedures (Note even without concerned of SQL injection this is a good idea).

2. If you use stored procedures do not use any of the passed in values to generate dynamic SQL (otherwise you have just moved the problem from the app to the database).