How Prevalent Are SQL Injection Vulnerabilities?

Krishna Dagli writes to tell us of an investigation, by Michael Sutton, attempting to get an estimate of how widespread SQL-injection vulnerabilities are among Web sites. Sutton made clever use of the Google API to turn up candidate vulnerable sites. You might quibble with his methodology (some posters on the blog site do), but he found that around 11% of sites are potentially vulnerable to SQL injection attacks. He believes the causes for this somewhat alarming situation include development texts that teach programmers insecure SQL syntax, and point-and-click tools that allow the untrained to put up database-backed sites.

Comments

The abuse of SQL injection

[Reality Master 201]

destroys thousands of lives a year. Sure, it starts small - a SELECT there, a few INSERTS on the weekend. Eventually, though, you're using stored procedures and trying to score triggers in the middle of the night.

Just say no, kids.

Re:The abuse of SQL injection

[Reality Master 201]

Hey, thanks. People are stupid and humorless; you get inured to it after a while.

Re:The abuse of SQL injection

[Anonymous Coward]

The bad, though, is now, even if 10 people come by and mod you funny, you still lose karma because funny moderations don't count, but overrated/offtopic/troll/flamebait do.

Re:The abuse of SQL injection

[Reality Master 201]

Yeah, but most people are still stupid and humorless. So, in the end, I come out ahead.

Unfortunately: Not Surpirsing

[charleste]

This is a possibility that was obvious back when I was developing web applications as far back as 1996 using CGI. The approach in TFA was a similar approach we used "back when" to demonstrate the need for (a) not using GET, (b) turning off verbose error reporting, (c) controlling *how* queries were made (e.g. architecture of the app and DB I/O), and (d) storing sensitive data encrypted. The sad part is that it is *still* a problem. I guess it underscores the need for a decent architect as opposed to letting whiz-bang do-it-yourselfers start coding without design, and the need for security analysis, et. Al. Just my 2 cents.

Re:Unfortunately: Not Surpirsing

[CastrTroy]

How does not using GET stop anything, you can POST anything you want to a webserver just like you can GET anything you want from a webserver. Only using POST will make things a little harder, but it doesn't stop anything.

Re:Unfortunately: Not Surpirsing

[Anonymous Coward]

It makes things harder until you put this in the location bar:
javascript:for(i=0;i<document.forms.length;i ++){document.forms[i].method="GET"};alert("done");

Re:Unfortunately: Not Surpirsing

[crayz]

Normally if you're accepting form data, you have a way to control whether its coming from a GET or a POST. Nevertheless, using POST for security reasons(or anything where people would reasonably want to bookmark or navigate to/from a page) makes me angry

Re:Unfortunately: Not Surpirsing

[dgatwood]

Using POST doesn't make it any harder. It just makes the address bar less ugly.

IMHO, any web application should be written to accept both GET and POST. I'm very annoyed with USPS.com for this very reason. I have a small Dashboard widget that I use to track packages. Short of writing some custom redirect script on a server somewhere, I cannot construct a URL that I can use to create a clickable link to the tracking results.

I sent an email to the USPS webmaster and asked if there was anything they could do to help, and they pretty much said "no". Same problem with DHL, but I don't care enough to bother with them because I so rarely get packages delivered by DHL.

UPS and FedEx work fine and have worked fine for years because their programmers had a clue. Needless to say, while I can't control what service other people use to send packages to me, I make it a point when shipping packages to other people to always use a service that supports GET requests in their forms even if it costs more than USPS. So their insistence upon POST over GET is costing them business, at least from this customer.

Re:Unfortunately: Not Surpirsing

[Goblez]

So you write a widgit that creates a request object and POSTs it to the server. It's the same info being sent, it's just how it's viewed in the addressbar.

Re:Unfortunately: Not Surpirsing

[masklinn]

the need for (a) not using GET

WTF? GET has uses just as POST has uses. If a request is to be idempotent (read-only query), then it's a GET. Otherwise it's a POST (or a PUT, or a DELETE).

The fact that PHP fails at understanding HTTP doesn't mean that others do so too. For example, the web.py Python microframework behaves this way: you map URLs to classes, and these classes have any of 4 methods: GET, POST, PUT, DELETE (they can have others, but the others are not important). Each method is called when the framework receives the corresponding HTTP request on the resource. This means that you don't handle a POST request the way a GET request is handled (unless you ask for it explicitely, by calling the GET method from your POST), and that the javascript:for(i=0;i<document.forms.length;i ++){document.forms[i].method="GET"};alert("done"); answer to an answer to your post just won't work.

Re:Unfortunately: Not Surpirsing

[profplump]

Actually, there's an RFC about this. GET is to be used when requests make no changes to the data -- say when you're reading the database (like from the USPS website) -- and POST is to be used when requests change some data or state.

Re:Unfortunately: Not Surpirsing

[ehud42]

"SELECT `userID`,`user`, `password` FROM `table` WHERE `user` = 'trim($_POST['user'])'"


Ack! Nice demonstration of the code that is vulnerable to attacks!

My user id is '; drop database; --

Re:Unfortunately: Not Surpirsing

[Lord Ender]

(a) not using GET

At this point, I realized you didn't know what you are talking about, and stopped reading your comment.

Re:Unfortunately: Not Surpirsing

[OakDragon]

GET is to be used when requests make no changes to the data -- say when you're reading the database (like from the USPS website) -- and POST is to be used when requests change some data or state.

And there's a good, good reason (which many of you probably already know). If your site is indexed by a search engine, and you don't have a proper robots.txt file in place (or the search-bot ignores it), the bot can trigger data changes. Ouch!

Re:Unfortunately: Not Surpirsing

[Ford Prefect]

IMHO, any web application should be written to accept both GET and POST.

No. Web applications should use GET and POST where appropriate - GET for idempotent requests (showing database records, search results, those kind of recurring, non-database-changing things) while POST should be used for things which actually change data, user state, and so on.

Using GET in the wrong places can lead to all kinds of irritations and miniature security problems. Imagine sending an email to your web application administrator containing something like the following: <img src="http://example.com/webapp/user_admin?action=e dituser&username=me&accesslevel=administrator" width="0" height="0">...

Many web applications do get the two horrendously mixed up (I've seen search results done via POST, which is subtly, incredibly annoying) but they're definitely not interchangeable.

For simply displaying a non-password-protected package shipment page, GET would probably be the best solution. But blindly accepting both isn't a good alternative.

Re:Unfortunately: Not Surpirsing

[omgwtfroflbbqwasd]

I think what he means to say is "not always using GET", vs. "never using GET". At least I hope so.

Re:Unfortunately: Not Surpirsing

[Electrum]

IMHO, any web application should be written to accept both GET and POST. I'm very annoyed with USPS.com for this very reason.

This seems to work:

http://trkcnfrm1.smi.usps.com/PTSInternetWeb/Inter LabelInquiry.do?origTrackNum=99999999999999999999

Re:Unfortunately: Not Surpirsing

[wmaker]

maybe he meant $securedvar = mysql_escape_string($_POST['postedvar']);

Re:Unfortunately: Not Surpirsing

[dgatwood]

You can't send a URL to another application with a POST request. If it isn't in the addressbar, it can't be part of a URL, so a Dashboard Widget can't make Safari display the page. So you only have three choices:

• Write a CGI on the web server that converts a GET request to a POST request, sends it to the USPS server, and returns the results.
• Write widget code that makes the POST request with xmlhttp, stores the results to a local file on disk, then opens that file in Safari.
• Write widget code that makes the request, processes the data, and displays it in some form. That's way too much work, though.

Either way, it is very complex compared to simply constructing a URL and using DOM to add <a href="http://foo.com/track.cgi?num=928734"> 928734 </a> into the HTML in the dashboard widget (which can be done entirely in javascript).

Re:Unfortunately: Not Surpirsing

[dgatwood]

Thank you. Now why the &^#$^@ couldn't they have told me that instead of saying "Sorry, no GET url exists"!?! :-)

*sigh*

Re:Unfortunately: Not Surpirsing

[TheSpoom]

You could probably create a clickable link if you could use Javascript as the onclick to postback to the URL in question. I know, an ugly hack, but it could work.

Re:Unfortunately: Not Surpirsing

[arivanov]

Ahem. While this problem is prevalent in PHP/ASP(AFAIK pre .NET) code, it is long gone in most "sane" languages.

If you follow elementary "safe" coding conventions in Perl, Python or Ruby and use prepare() statements it is nearly impossible to write injectable code. I am saying "nearly" because there will always be an inventive idiot to write a code which can allow injection in any language.

Re:Unfortunately: Not Surpirsing

[Tim C]

Hell of a typo if he did...

Re:Unfortunately: Not Surpirsing

[Lord Ender]

Good point--especially if we're talking SSL.

Re:Unfortunately: Not Surpirsing

[budgenator]

#!/usr/bin/perl
 
use LWP::UserAgent;
use HTTP::Headers;
use HTTP::Request;
my $hostname="http://localhost/cgi-bin/printenv";
my $lwp= LWP::UserAgent->new(timeout=>10);
my $h=HTTP::Headers->new;
  $h->content_type('application/x-www-form-urlencod ed');
my $request=HTTP::Request->new("POST",$hostname,$h,$r equestcontent);
my $res=$lwp->request($request);
my $contents=$res->content($res);
print $contents;
exit();

whoo that was hard; all using POST is going to do is get you a slightly better quality hacker cracking your site. If I was really uptitght about website security in addition to doing all the usualy, I'd install a nonce system. Add a couple hidden fields, one would be a checksum of the sesion id, a hidden field and the nounce and it would only be acepted one time. The user would only have a new one on each request; the site might still be hackable, but it would make the hacker play with the tarbaby!

Re:Unfortunately: Not Surpirsing

[budgenator]

#!/usr/bin/perl
use LWP::UserAgent;
use HTTP::Headers;
use HTTP::Request;
my $hostname="http://localhost/cgi-bin/printenv";
my $lwp= LWP::UserAgent->new(timeout=>10);
my $h=HTTP::Headers->new;
  $h->content_type('application/x-www-form-urlencod ed');
my $request=HTTP::Request->new("POST",$hostname,$h,$r equestcontent);
my $res=$lwp->request($request);
my $contents=$res->content($res);
print $contents;
exit();

Just make $requestcontent what USPS expects and change the URL as appropriate and pipe the output to safari

Re:Unfortunately: Not Surpirsing

[Amouth]

why not have it call a javascript function that takes the info and posts it to a new window .. not that hard.

or even have java script go get the damn thing and return it back

Re:Unfortunately: Not Surpirsing

[Bogtha]

GET for idempotent requests (showing database records, search results, those kind of recurring, non-database-changing things) while POST should be used for things which actually change data, user state, and so on.

You are confusing idempotence and safety. "Idempotent" merely means that repeated requests have the same effect as a single request. You should use GET because it is safe, not because it is idempotent. For contrast, DELETE is an idempotent method, but it's certainly not a safe method. For some reason everybody seems convinced that "idempotent" is a fancy word for "safe". See section 9.1 of the RFC, it explains in more detail.

Using GET in the wrong places can lead to all kinds of irritations and miniature security problems.

The example you give for Slashdot highlights the difference between safety and idempotence. It doesn't matter if you make the request once or a hundred times, the result is the same - you are logged out. The request is idempotent. The problem is that it's not safe, because it alters application state by logging you out.

The example you give of sending an email is a bad one. Consider sending an email to the admin containing an iframe that generates a form and auto-posts it.

The correct way to guard against vulnerabilities like this is to generate a per-user nonce. Include it as a hidden field in every legitimate form and check for it in every form handler.

Re:Unfortunately: Not Surpirsing

[Snover]

I hope not, since mysql_escape_string() will also allow for SQL injection by not respecting the character set of the MySQL connection. The PHP Group, in all its brilliance, decided to add another function, which they named mysql_real_escape_string(), rather than simply fixing the first one. /me sighs

Re:Unfortunately: Not Surpirsing

[Ucklak]

I guess you didn't read
No programmer worth his pay would blindly accept any data that is POSTed or GET[got] and pass that into a SQL statement.

But your assuming 'trim($_POST['user'])' is exactly that.

What you don't see is the
No programmer worth his pay would blindly accept any data that is POSTed or GET[got] and pass that into a SQL statement.

Which would be

$_POST['user']=clean_up($_POST['user']);

And the function clean_up($string) is obviously a function to look for injection attacks, bad data, etc...

I'm not going to paste code that includes Error messages for bad data, required instructions for good data, regular expressions for various form data, etc...

Re:Unfortunately: Not Surpirsing

[jam-pearl]

I think what Charleste means is; when dealing with receiving data like passwords, for the sake of privacy use a POST Vs a GET cause URLs get logged at various hops.

Re:Unfortunately: Not Surpirsing

[julesh]

How does not using GET stop anything, you can POST anything you want to a webserver just like you can GET anything you want from a webserver. Only using POST will make things a little harder, but it doesn't stop anything.

Only using POST can limit your exposure area. If a flaw exists in a script that is only accessible to logged-in users, then it cannot be attacked directly by script-kiddies at large. Somehow, they have to get a login to your system first. One possible method of exploitation is sending either a link or HTTP redirect to somebody who is logged in that will exploit the flaw. If you only allow POST variables, this vector won't work, and they'll have to rely on something more complex and less likely to succeed.

It's not great, but it is a useful part of a defense-in-depth strategy.

Re:Unfortunately: Not Surpirsing

[CastrTroy]

Well, with XMLHTTPRequest, it isn't that hard to do a post with Javascript. A little hard than just constructing a url, but not that much harder.

Re:Unfortunately: Not Surpirsing

[Stooshie]

Ahem. While this problem is prevalent in PHP/ASP(AFAIK pre .NET) code, it is long gone in most "sane" languages.

If you follow elementary "safe" coding conventions in Perl, Python or Ruby and use prepare() statements it is nearly impossible to write injectable code. I am saying "nearly" because there will always be an inventive idiot to write a code which can allow injection in any language.

Ahem! This problem exists in all languages. It's bad programmers that cause the problem. the Prepare() method is part of the NewADOConnection() class in PHP as well and is followed as "safe" practice. Bad programmers don't use it.

Re:Unfortunately: Not Surpirsing

[ehud42]

But your assuming 'trim($_POST['user'])' is exactly that.

Followed by:
Which would be
 

$_POST['user']=clean_up($_POST['user']);

Now why would you do that? See, as someone who is maintainnig someone else's crap web code seeing the trim function leads me to believe that is what you thought was a good clean up routine. Especially since the argument being passed is the $_POST stream. Also, if your clean_up routine was so hot, then why didn't it also include a trim call?

If you write small 2-bit apps that only you and the web server see, then fine make your code
unreadable (by doing things that make it hard to trace).

If you had actually intended to scrub a $_POST parameter first, then fine, scrub it - but put it somewhere else!

$cleaned_user = clean_up($_POST['user']); //clean_up sanitizes input and trims whitespace

// now use the variable $cleaned_user in your embedded SQL.

(rant over)

This article is quite timely. I'm currently going through over 120 ASP pages written by 'professionals' that a) has no error handling, b) has embedded SQL that originally had NO SQL Injection protection, c) no cleaning of input variables (like you suggest good payed developers should do), d) does weird stuff like use a variable 4 or 5 times before declaring it - WTF!?!? and on and on and on...

Its Friday and I'm a bit overly cranky.

Simple solution

[CastrTroy]

The simple solution is to use parameterized queries. I don't know why more books don't know why more books don't push this methodology, as it makes you program faster, easier to read, and also makes you invulnerable to SQL injection attacks.

Re:Simple solution

[revlayle]

Indeed, the last few project I have done use COMPLETELY parameterized queries. The only time the SQL statement structure for a command is remotely dynamically built is to add additional caluses based on specific criteria and allows no user-generated input to actually build the clause specifics. With these practices, we have absolutely *NO* SQL injection vunerabilities.

The other side of the coin, that people still forget about, also, is data that is queried and made for display in HTML browsers. Without properly escaping all the data displayed, script kiddies can store HTML/JavaScript. However, this is easily diverted (HTMLEncode for the ASP folksies, htmlspecialschars for the PHP camp, to name a couple) with an html encoding function, which most popular web application systems provide.

Re:Simple solution

[valloned]

Agreed. This alone can prevent the majority of SQL injection attacks. For some stupid reason, though, most RAD applications do NOT do this. Even worse, as you point out, is that many textbooks and online articles give examples using in-line SQL statements. This problem will not go away until both software companies and employers move to a Secure Application Development mentality rather than a RAD mentality. Developers who know only point and click tools with no knowledge of code simply can not build secure applications. When industry realizes they are better off paying the extra cash to have things done right by knowledgeable developters, this trend of unsecure web applications will start to reverse itself.

Re:Simple solution

[Briareos]

Interesting. This is actually quite disturbing, as this is not the behavior I would have expected. The parameter is simply doing a string substitution.

This just shows that if you're using parametrized queries to be on the safe side you have to do it all the way.

There's no way in hell using prepared statements in the frontend will save you if your backend is still unsafe. Of course, in that example you could just scrap the whole SQL function and use a client-side prepared statement - at least in this case you'd have known what's going to happen all the way into the database...

np: Alias & Tarsier - Anon (Brookland & Oaklyn)

Re:Simple solution

[neelm]

I'm sorry, but I don't count this. Just because the SQL injection is in the procedure code and not the app code doesn't mean that parametrized queries are open to exploit. This example shows using a non-parametrized query in the stored procedure - had it been parametrized it would have been safe.

Using a parametrized query is not like search and replace. The parameter version, the input is not escaped, but rather sent separately from the query and marked as data. Trying to protect yourself by manual escaping is foolish and short sighted; you will leave out some character you didn't know to look for and you will open yourself up to having to make exceptions that further allow bugs. Like for a semi-colon - which is needed in parameters. I just wrote one in this paragraph, and the slashcode will need to insert it into the database.

Always use parametrized queries - that's true if you writing in C# or T-SQL.

Re:Simple solution

[revlayle]

Interesting you bring up this example, as I am finishing up a contract project that does something extremely similar (searching on name, grade, and team). My method, programmatically speaking, is searching for a subset of employees. I have an Employee class with a "static" (I put this in quotes at it is a PHP4 class, where there is no specific keywords to indicate a method as static, just implicity used and documented as a static call as the method call is not executed against a class instance) method to return a "resultset" (array of Employee objects) based on the filter criteria.

The method has a basic SQL statement in it, with the universally parameterized elements already defined. An additional function is called to "parse" my form filter fields. The function that parses the filter returns two values: on decribing the additional WHERE elements with the needed parameterization and an array of the parmeter values. This way, I can still dynamically introduce WHERE clause expressions, but still require parameters into the parsing of the SQL statement to prevent injection. A simplified example would be (using, for example, an email address and a department id - using Oracle SQL syntax & PHP):
function parseFormFields($aFilter, &$aArgs)
{
$aWHERE = array();
foreach ($aFilter as $sField => $sValue)
{
switch($sField)
{
case "name":
$aWHERE[] = "NAME LIKE '%' || :NAME_FILTER || '%'";
$aArgs["NAME_FILTER"] = $sValue;
break;
case "department":
$aWHERE[] = "DEPT = :DEPT_FILTER";
$aArgs["DEPT_FILTER"] = $sValue;
break;
}
}
return implode(" AND ", $aWHERE);
}

This would return a suitable WHERE clause expression and fill in the $aArgs argument with proper parmeter values that I could merge with another array or simply iterate through and bind these parameter with the actual parsed statement done by the code that called this particular function.

The "dynamic" part of the SQL statement is just building the additional WHERE expressions needed to finish the query. The parameters are there to keep the actual user_defined content bound as parameters to the parsed query to keep injections in check.

Hope this explained my method worth a damn....

Re:Simple solution

[revlayle]

(sorry for the poor indentation of the code)

Re:Simple solution

[mcrbids]

Anything made can be unmade

It's quite possible to escape characters and be certain that they won't be bypassed.

but it is staggering how many choose not to do at lease strSQL.Replace(";","BLAHBLAH - SIMPLEINJECTDEFEAT"); before executing SQL. Nobody needs to allow ";" in any query.

Which is just retarded. There are many cases where the semi-colon ";" is perfectly legitimately used. For example, this comment contains a number of them. This comment will be saved to a database. Thus, the SQL statement contains semi-colons.

The example you gave proposes that parameterized sql (AKA "prepared statements") is not safe, and then gives an example using non-prepared statements, and even goes so far as to "solve" the problem using (gasp!) a parameterized statement! (Summary: parameterized statements are only safe when you use them?)

It's better to know of what you blather before you blather...

Re:Simple solution

[RAMMS EIN]

``The simple solution is to use parameterized queries. I don't know why more books don't know why more books don't push this methodology, as it makes you program faster, easier to read, and also makes you invulnerable to SQL injection attacks.''

I guess it's because they're cumbersome. I found this example:

SqlCommand command = connection.CreateCommand();
command.CommandText = "SELECT * FROM Customers WHERE CustomerID = @CustomerID";
 
command.Parameters.Add(
      new SqlParameter("@CustomerID", SqlDbType.NChar, 5)).Value = customerID;

That's quite a mouthful compared to

mysql_query("SELECT * FROM Customers WHERE CustomerID = $customer_id;");

or even

mysql_query("SELECT * FROM Customers WHERE CustomerID = '" . mysql_escape_string($customer_id) . "';");

My personal favorite is the Lisp macro that lets you write

(query "SELECT * FROM Customers WHERE CustomerID = " customer-id)

Re:Simple solution

[Fweeky]

Ew. An idiom I'm used to is:

query("SELECT * FROM Customers WHERE CustomerID = ?", customer_id)

Why do you need more?

Re:Simple solution

[Goaway]

Here's why: PHP doesn't even support them out of the box.

PHP is quite probably the single biggest cause of SQL injection attacks on the web.

Re:Simple solution

[Estanislao Mart�nez]

Why do you need more?

That requires you to address the parameters positionally, and you may not be able to do so effectively if you don't know beforehand how your query's gonna look. You at least need to allow the option of having the binding of query parameters to values be done by name instead of position.

In general, people who push for current implementations of prepared statements as strongly as some do in this thread, even though they are right in the arguments they make, miss the point about how complex query generation needs to be for some applications. The real point, IMO, is that we need better ways of integrating database querying with programming languages; string-based composition of SQL statements just doesn't cut it. The LINQ feature that's getting added to C# 3.0 is definitely a step in the correct direction.

Re:Simple solution

[espressojim]

First:

You want an ORM layer, not SQL.

Second, when did parameterized calls = stored procs? I have plenty of the first, and none of the second in my current project.

Also, if you want seperation of concerns (business logic != data retrieval), then what's wrong with coding up your parameterized queryies appropriately? Just check for nulls on the fields you put in, and don't join on those entities. It ain't tough.

Or, use an ORM (Hibernate, others), and you don't have to worry about that at all...

Re:Simple solution

[espressojim]

Many java APIs, you can name parameters (like say, JDBC, Hibernate, etc)

"Select * from customer where customer.name = :name";
setParameter("name", "Bob");

Now, is it still so hard?

Re:Simple solution

[Estanislao Mart�nez]

The problem is not that it's hard. The problem is that it's cumbersome as hell, and doesn't scale to doing really complex query generation. You're still writing your whole query as a string with named blanks in it, and then at the end saying which things go in those blanks. If you need to have conditional logic that dictates what's going to be put in that query, or, God forbid, if you wrote and maintain an application that needs to be able to generate arbitrarily complex queries based on a recursive description of a dimensional schema (as some of us do), you're going to be in SQL text append hell anyway.

Again, the common paradigm for accessing databases from programming languages (embedding SQL as strings in one's language) is crap. What Microsoft is doing qith LINQ, building query capabilities into the language itself, is fundamentally moving in the right direction (though I don't know their implementation well enough to actually endorse it).

Re:Simple solution

[felix rayman]

Cumbersome?

You won't write secure programs because it takes an extra line of code? Jackasses like you almost make me want to quit my job as a developer and become a script kiddy.

For anyone non-fucktarded that is reading this, yes, it frequently takes more lines to write code that isn't vulnerable to attack than it does to write crappy code that is. You get what you pay for, I suppose.

And, as a sincere question, does anyone out there have a single instance of a SQL injection attack that can work if the programmer is intelligent enough (and kind enough to his DBA) to use prepared statements? I have never seen one. If you have, then educate me, I'd love to learn.

Oh but wait, that's an extra line of code. Can't do that! We'll just have a 500 comment article about various ways to check user input because we're too god damn stupid to use prepared statements.

Idiots.

Re:Simple solution

[CastrTroy]

You can even forego the extra line of code. Here we go.

ExecWithParams("SELECT * FROM Users WHERE UserID = ? AND Password = ?",$UserID,$Password);

ExecWithParams is some function that you make yourself to prepare the statement for you, and which support any number of arguments after the initial SQL query depending on the number of parameters you have to fill in. Most languages support functions of this kind, and if they don't then just have one extra arguement that is an array, and define you array inline when executing the function.

Re:Simple solution

[espressojim]

Or, as I've mentioned in other posts, don't use SQL.

Use an ORM solution instead. I haven't written SQL in over a year.

One of my current projects is a generic way to query fairly complex type/value data out of a set of tables without any database knowledge by the user.

Imagine you have a bunch of people with traits, like eye color, weight, etc.

You want to say: Show me all people that have blue eyes and weigh more than 50lb, and do not smoke (or put an "OR" query in there instead.)

That sort of query actually maps onto multiple tables in the database (I think ~5 or so)

I've cranked a nice solution to that where all you have to do is configure where your tables exist, and where your operators/operands are mapped. The framework then generates objects (in this case, Criteria objects from Hibernate) that create the query for you.

And, since the query generated is 'rooted' in the data type you want to retrieve (like Individual) you can run the framework to do this filtering, then add your own restrictions or joins to the query automatically. You can even associate this result to other entities that you didn't even configure for by just configuring how you'd join the two objects, by simply referencing the property of the object that defines the join.

Again, there's no SQL here. It's an OO query language where you pass in objects to represent joins and restrictions.

Java Persistance has some similar functionality, but Hibernate has a LOT of power, once you get over the learning curve of how to use it.

Re:Simple solution

[cortana]

mysqli/PDO?

Of course neither are enabled in Debian's current PHP packages. :(

Re:Simple solution

[RAMMS EIN]

I was going to ignore you, because you're abusive and wrong, but it seems some people agree with you, so I'll respond.

``For anyone non-fucktarded that is reading this, yes, it frequently takes more lines to write code that isn't vulnerable to attack than it does to write crappy code that is.'' ...but not in this case. It's perfectly possible to let people write the query just like the insecure PHP one, but still escape the parameters. The query macro I showed you does that. I've written a function in PHP that let you write

sql_query('UPDATE Users SET name = $name WHERE id = $userid;')

and it will automatically escape $name and $userid for you. No cumbersome create query, add parameter (with type), add parameter (with type), execute stuff, but every bit as secure.

Re:Simple solution

[Fweeky]

You should be able to build the parameter list in the same order as you build the string, ala ez-where. If you can't, well, you can always do named binding yourself:

def query(statement, *params)
  if params.last.is_a? Hash
    named = params.pop
    statement.gsub!(/:([a-z0-9]+)/i) { quote(params[$1.intern]) }
  end
  super(statement, *params)
end
 
query('SELECT * FROM Bla WHERE foo = ?, wibble = :wibble, wobble = :wobble', bla.foo, :wibble => bla.wibble, :wobble => bla.wobble)

Even in a nastier language, it should still be easier to make an injection-safe API that suits your usage pattern than remembering to manually escape each parameter every time.

Re:Simple solution

[Goaway]

Right, "out of the box" is the operative phrase here. If it's not there right in front of every single user, people won't bother with it.

Re:Simple solution

[cortana]

Hm, I forgot to suggest what I often end up doing...

mysql_query (sprintf ("SELECT * from foo WHERE bar = '%s'", mysql_real_escape_string ($baz)));

Point and click tools

[also-rr]

The fact that tools can be used to put up insecure sites is not exactly a failing of the tool. The tool will have had a spec (even an informal spec) - which may have been "put sites up fast and let users sort out the security".

It's only a failure of the tool, or the developer of the tool, if the tool is marked as being a one step solution. Of course a lot are, there is no shortage of snake oil salesmen, and in that case they take 100% of the blame. However most rapid deployment tools contain a clear disclaimer of what it does or doesnt do and a guide to helping you with the rest of the steps.

When those things exist the fault is with the user and not with the tool - elitism aside. It's like using a hammer to kill a man (despite the do not kill people with this hammer label and 100 page guide to not killing people with hammers - now with pictures!) and blaming people killed with hammers on the hammer designer.

Re:Return of the Flat File

[/dev/trash]

This is exactly why I use NanoBlogger.

Re:Sure, blame the "untrained" developers....

[zoeblade]

If the obvious fix is to exclude special characters from password fields, then why allow them by default to begin with?

Because that won't stop a wily hacker from using a tool such as curl to use those special characters as if they'd entered them in the password field. This has to be fixed at the server end, not the client end.

Re:Return of the Flat File

[ip_vjl]

Won't work. The same 'novices' who leave gaping SQL injection holes will now be writing pages that need to access the file system. Now instead of accessing the DB, script kiddies will be traversing the filesystem. Yes, this can be mitigated through file permissions, but there are a lot of servers out there (set up by these same novices) where processes run as root and would have full access to read and write files. So, a bad script could allow them to write to /etc/passwd and have all sorts of fun.

No. That's a stupid idea.

[Reality Master 201]

Stupid application construction isn't a good reason to make the design of the app even stupider.

In the old days, everyone used flat files, because that's what there was. Then someone (several someones, most notably Codd and some others at IBM) realized that breaking the flat data into sets of discrete data that related to each other reduced redundancy and allowed for an overall better quality of data. And it wasn't app specific.

The answer to SQL injection is to test apps more completely (including tests for this kind of attack), to provide extra checks at the database level (for integrity issues, perhaps in the form of constraints, etc. dependent on the scale and structure of the database), and to develop tools/libraries for data access that make this kind of thing hard to do accidently.

Re:Return of the Flat File

[RingDev]

Why convert to an entirely different structure when just implimenting proper code standards will suffice? Using parameterized stored procedure calls instead of dynamic SQL will not only protect you from the vast majority of SQL Injection attacks, but will also improve the performance of your web page.

-Rick

Huh?

[TheLink]

How do a) and b) help?

Which "decent architect" designs a system where verbose error logs are sent to untrusted public users.

And which "decent architect" writes web apps where GET would inherently causes security problems.

In fact, post causes more problems if the target page doesn't issue a redirect, because then the form could be reposted. This shouldn't be a security problem of course (unless it's a login page), since duplicate posts should be handled gracefully. However with most browsers, users would be annoyed with "click ok to repost" prompts.

Of course one shouldn't use GET when confidential parameters are being sent (since it could appear in url histories etc), but GET allows users to bookmark a page or query, and in many cases that's a plus.

Re:Sure, blame the "untrained" developers....

[tuffy]

In short, trusting the client (i.e. the web browser) to not send bad values - either through the INPUT tag's maxlength attribute, JavaScript scrubbing or whatever - is entirely the wrong way to go. The web script must check all user input for validity along with properly escaping everything from the database that's getting sent back via HTML.

Re:Sure, blame the "untrained" developers....

[patrixmyth]

That's a valid point, but doesn't contradict mine. If the server, by default, doesn't accept special characters in a password field, then that fixes most of these problems. Obviously, the password field isn't the only place where you can muck with the SQL, but if you're getting malformed fields from a valid userid and password, then you are much further along the path to shutting out the problem user who misused or had his access compromised. Cheers.

Re:Sure, blame the "untrained" developers....

[dk-software-engineer]

I don't think you understand SQL Injection at all.

If the obvious fix is to exclude special characters from password fields

It's not. First of all, it wouldn't work. Second, it makes to sense at all at any level. Sorry if I seem rude. :)

There are a lot of new programmers (or whatever we're calling people who make websites these days), who are not naturally paranoid and sensitive to the exploitation of their code. They shouldn't need to be.

I agree, but it's a dreamworld. I shouldn't need to fiddle with keys or whatever every time I use my car or get home. But I do.

Luckily you can create pretty safe code by making nice code. It's amazing how many side-effects "nice" has. But yes, you'd need to be a good programmer to make nice code.

Some kind of software checklist

[Realistic_Dragon]

There should be some kind of government run website somewhere.

You would answer questions and it would give you license keys to software that you were qualified to use. For example, I might tick:

Engineer (check)
Artist ( )
Manager (check)
Linux (Check)
Mac ( )
Windows ( )

And it would issue keys for website point and click installation software, Vi, apache and Latex - but deny me keys to powerpoint thereby saving the lives of people who might otherwise have to gnaw off their own leg to survive my 8 hour presentation on optimising synergisyms in a web 3.0 environment by sub molecular interactions.

Re:Some kind of software checklist

[david_g17]

...my 8 hour presentation on optimising synergisyms in a web 3.0 environment by sub molecular interactions.

Apparently, you do not work for the U.S. government, for that is precisely the type of presentation they would love to certify you to give.

Criminalize?

[NineNine]

Hello? Has the Net gotten so crime-ridden that instead of blaming criminals for doing illegal things, that we're now blaming developers? I mean, sure developers need to do all they can, but at what point do we, as a society say, "Hey, let's prosecute some of these assholes that are making life online a royal pain in the ass?" It's pretty absurd that today, you can still do whatever you'd like online (hack sites, networks, steal information), and there are really no repurcussions. We see what, one person getting jail time every 6 months? That's fucking insane. It's time for our government's law enforcement to step up to the plate, learn what the fuck a mouse is, and start prosecuting some of these people. Email is already useless. The web is getting there pretty quickly. SQL injection, firewalls, blah, blah blah.

If I have somebody who is constantly trying to break into my house, to the extent that I have to pay a security professional just to keep them bastards out, I'd be down at the police station talking to the police chief, angry as hell. As is, people have come to accept that there's almost more crime than legitimate traffic on the Net these days. That's insane. Something has to be done about this mess before the entire Net is just useless.

Re:Criminalize?

[CaffeineAddict2001]

IRL crimes have physical evidence and eye witnesses and still most people get away with it.

Considering somebody can break into your site from any one of a million unsecured WiFis from New York to Bangkok, i'd say prosecution is not going to be an effective method of deterrance.

Re:Criminalize?

[bestinshow]

Even if they did prosecute, it does you know good once they'd messed with your database or otherwise hacked you.

It isn't hard to secure an online application against SQL injection. So there's no excuses for the developer to write 'SELECT blah FROM table WHERE field='$forminput';' directly in their code is there?

Your example should be about you going to the police station complaining because you don't have a lock on your door, not because you need a paid security guard.

Yes, I agree that hacking crimes should be punished more, but that doesn't absolve you of your responsibility to secure yourself and your property.

Re:Criminalize?

[NineNine]

Have you ever tried to harden an Internet server of any kind? It's not just installing a lock on your door. It's installing locks on every door and window. It's adding a wall and a moat around your house. It's adding a sniper's nest and an electrified fence. It's really insane the lengths that anyone has to go to just to put any kind of server on the Net these days. At what point does the admin's responsiblity end? Realistically, it takes a security team to continually patch and configure each and every server constantly just to maintain any kind of protection. SQL injection may be relatively simple to fix, but my point is why is 100% of the burden still on admins? Why do we not have teams of either law enforcement or individuals tracking down all of these fuckers and beating the holy hell out of them? (Actually, I know one guy who did track down a hacker... he flew out to his town, and showed up at his door with a baseball bat. I'll let you imagine the rest)

Re:Criminalize?

[NineNine]

My point is that the Net can't keep going like this. Something has to change. The first thing is we as developers and admins need to stop accepting rampant crime as a given. It's just not acceptable. Whether it's an international police group that goes after these bastards, or it's groups of techo-vigilantes (with guns) hunting these people down, I don't know. Right now, any of us online have to just accept the constant attacks, knowing that if we, law-abiding people, try to fight back, we'll be thrown in jail, while these fuckers won't ever be touched.

Maybe it's time to form a security company that actually physically tracks these people down and takes care of the problem outside of the law.

Re:Criminalize?

[RAMMS EIN]

Well, of course the criminals who actually exploit vulnerabilities should be prosecuted, but that doesn't mean the developers should be left off the hook. If your code has injection vulnerabilities in it, it's buggy. Considering that injection vulnerabilities are easy to avoid, you should be ashamed if you code them. If your code is full of them (as many websites I've worked on were; not _always_ because I inherited the code), you're incompetent.

Re:Criminalize?

[twosmokes]

Even if they did prosecute, it does you know good once they'd messed with your database or otherwise hacked you.

You could say the same thing about murder. Or any other crime. The point of prosecution isn't to magically make the misdeed go away, it's to keep the ass from doing it again.

Re:Criminalize?

[budgenator]

Some places in Detroit, they'll steal the bricks off your house!
The internet is like the Wild West, the Sherrif is a bit crooked, and the Marshal is over worked. The no-good dirty rotten cattle russelers have gotten to good at keeping a border between them and the law so sometime you just have to get yourself a hired-gun to even things up for a while. Of course if you've brought up a gun-slinger for a job, it helps to point'em in the right direction. So to do that I'm collected the domains and websites that hire spammers, you know the ones, their spamm emails say "buy V1agra at http://sleezeysite.example.com/", I want all the sleezySite.example.coms. I got a little program called chummer that I'm working on, (I've written about it in my journal, feel free to take a peek), it sends bogus data to phishing sites. It spits out bogus data just as well in the POST method as it does in the GET method, so don't think POST help your sites security one bit. I figure that the phishers pretty much don't do much with the data except sell it, and if the data is full of bogus shit, the real buyers of the data will either go back and break the phisher's kneecaps, or more likely they'll just quit buying and move on to another type of scam. Either way I don't see it hurting civilized folks much and and if we draw the spammers and the phishers into a cross-fire, hooowee the worse that could happen is we'd start a von Nuemann loop in email that turns the universe into a blackhole; but more likely we'd just burn a hole in their bandwidth budget!

So If your so inclined to help, just send a text file of spammer domains to my user name at gmail and when I get 300 or 400 hundred unique spammer domains I'll test chummer to see what happens.

testing methods

[Akatosh]

This 11% was determine by a weak testing mechanism. For every site that baltently spews sql errors to the user there are two that silently return a generic sanitized error, and another two that return no error at all. It would produce more results if you take it a step further and ask yes no questions, such as:

?id=99999' OR '10

and see if the page returns the results of id=10 as expected. It's also common for people to use weak regexp (regexp should NEVER be used to protect against sql injection, see mysql_real_escape_string) and miss some characters:

?id=99999)

or fail to sanitize non us language encoding. Also, get variables are often the most protected. It is much more common to find sql injection in <input type=hidden variables, or in cookie data. The number 11% is extremely low. I'd guess more like 80%.

Re:testing methods

[ecklesweb]

Depends on how you use a regexp. If you're defining the characters that are allowed in the user input, that's one thing. If you're defining the characters that aren't allowed in the user input, then you might as well not have wasted your time.

Re:testing methods

[profplump]

I fail to see how s/\W//g wouldn't leave me with "safe" input. I've yet to see an SQL injection attack that doesn't need a character outside of [a-zA-Z_].

Re:testing methods

[Akatosh]

I speak of using regex as an escaping mechanism, not data verification. Hence the comparison to another escaping mechanism. I was not clear. It would be an apples and oranges comparison otherwise. Regexp should never be used to escape data sent to a database, rather there are mechanisms that use the libraries of the database in question to escape it.

Re:testing methods

[tehshen]

regexp should NEVER be used to protect against sql injection

In Perl and Ruby, variables are "tainted" if they come from outside, and can't be used in sql or system. Running a string through a regex is the preferred way to untaint it. Your mysql_real_escape_string is the equivalent of a simple regex anyway (replacing characters with \characters).

Does Python have a taint mode? I know that PHP doesn't (shame)

Re:testing methods

[Akatosh]

You do know that prepared statements use mysql_real_escape_string to escape with, yes?

Please compare how mysql-5.1.7-beta/libmysqld/sql_prepare.cc sanitizes input vs mysql_real_escape_string in mysql-5.0.24a/libmysql/libmysql and you can verify for yourself. The mysql client libs have been audited by thousands for many years and accounts for multibyte langauge characters. Your regexp does not. You can look at the code mysql uses to sanatize mysql_real_escape_string and prepared statements in mysql-5.0.24a/mysys/charset.c (escape_string_for_mysql). It's only 87 lines. You can try to reproduce it in regexp if you wish, but if you had not looked at escape_string_for_mysql() you would likely fail. Please use a database's client libs to sanatize, not your own.

A simple regexp escaper and mysql_real_escape_string are in _NO WAY_ equivalent.

Re:testing methods

[rk]

To say nothing of

$tainted =~ /^(.*)$/;
my $untainted = $1;

All tainted variables do is remind the developer their value is tainted. Better than nothing, certainly, but it doesn't ensure you fixed the problem.

The "Oh-Sh*t" face...

[moco]

From the article:

* Many development texts actually teach programmers insecure SQL syntax. ...
* Many sites are exposed to SQL injection attacks but don't know it.

I agree completely! I've seen the texts, I've seen the hordes of VB+SQL programmers that learned from said texts moving to the web porting the same "vices" to the new platform.
And I've seen the "oh-sh*t" face on a couple of developers after demonstrating to them that their software is vulnerable to SQL Injection. In both cases the vulnerabilities exposed the customers to the posibility of serious financial damage.

So far, the stupidest work arounds i've seen have been:
Developer: It's ok, I'll switch to post instead of get so the user can't forge the request.
Developer: It's ok, I'll write a method that removes sinlge quotes for every string i get from the user.
Developer: It's ok, I'll write some java script that will validate user input.

Writing secure software is never easy.

Re:The "Oh-Sh*t" face...

[Timesprout]

Well if you had read the article the next ranked issue is PHP includes. Its probably much more accurate to say many of these vulnerabilities are due to poor PHP+MYSQL coders than VB+SQL coders.

Re:The "Oh-Sh*t" face...

[valloned]

Microsoft VB.NET and ASP.NET texts are AWFUL in this regard. Nearly all examples use in-line SQL queries rather than paramaterized stored procedures. Why? Probably because they are trying to fit in with Microsoft's strategy that devoping applications should require absolutely no knowledge of code (or anything else for that matter). The big selling point for their VS 2005 suite is "no code required". That speaks volume.

Re:The "Oh-Sh*t" face...

[jrockway]

> Writing secure software is never easy.

It's easy if you use good tools. PHP is not a good tool. Rather than hacks like mysql_replace_the_string_with_things_that_wont_com primise_my_database(), you should be using tools that make it impossible to inject SQL.

Some ideas:

Perl's DBI, whose docs tell you to ALWAYS write SQL like:

$sth = $dbh->preprare('SELECT foo,bar FROM baz WHERE something=? AND another = ?')
$sth->execute(q{''Some\ things"'}, 10);

Notice that the programmer can't forget to escape the SQL -- because there's no escaping.

Even better is something like DBIx::Class, which lets you write

$resultset = $table->search({something => q{''Some\ things"'}, another => 10});

Again, no opportunity for the programmer to fuck up the SQL in any way. It's just like getting data out of the hash... DBIx::Class will generate the SQL (for any backend), run the query, stream in the results as needed, etc. It's easier and it's better!

Ruby on Rail's ActiveRecord is similar, but it's impossible to do certain types of joins. DBIx::Class is better in this regard. (And Perl is faster than Rails, and Catalyst is more complete rhan Rails :)... but both Ruby and Perl are MUCH better choices than PHP.

PHP makes it easy to write insecure code. Perl makes it hard! (With taint mode, a selection of ORMs, 10000+ well-tested modules, and nicities like Moose, Moose::Autobox, etc.)

Re:The "Oh-Sh*t" face...

[Nos.]

Or use the appropriate PEAR classes with php, like MDB2, which documents how to do prepare/executes. Don't suggest that PHP is worse than PERL because PERL has DBI, PHP has addons as well.

Re:The "Oh-Sh*t" face...

[valloned]

No, I meant exactly what I said. "Texts" meaning books and documentation written on VB.NET and ASP.NET, I'm assuming you missed that word? The platform itself can be quite secure if used correctly. The problem, however, is that the materials used to teach developers, ranging from Microsoft itself to 3rd party books to blogs, often demonstrate the simple solution rather than the secure one.

Re:The "Oh-Sh*t" face...

[sevinkey]

You're right, all of the asp.net examples do just have in-line sql queries.

Dunno why anyone would not use ObjectDataSources for their data in .Net, with parameterized calls to stored procedures behind though.... not only is this more secure than uglying up your UI level code, it's more efficient, and lets you bind to generic List objects instantiated to your own classes rather than using DataTables.

Re:The "Oh-Sh*t" face...

[moco]

I did read the article. And I never said that all SQL injection is entirely the fault of the vb+sql coders.

I was stating that in my experience, lots of developers that used the vb+sql client-server paradigm in the mid-90's coded without giving a rat's ass about security. Those developers learned from books that showed them how easy it was to code with VB by ignoring the security implications. After all, the market was hungry for vb coders and people would pick the thinner of the books that promised them that they would be coding vb in 24 hours or less. And this was "acceptable" for the small business LAN. The problem is that the same developers have migrated to a much more hostile environment, the internet, and are carrying with them the same ways of doing things.

You may correctly state that some of the developers we are talking about have migrated to PHP+MySQL and doing things the same way.

Re:The "Oh-Sh*t" face...

[soliptic]

(I asked this last time SQL injection came up, but I was too late, I got no reply, so I'll ask again...)

I don't understand how this magically fixes things.

In your example (yes, I realise it is just that, a quick example) you have 10 which is a constant. Now clearly if you were just doing queries with constants then there is no danger in doing a straight SELECT * FROM table WHERE something=10!

So your example obviously needs to replace "10" with "$numeric_var", ie $sth->execute(q{''Some\ things"'}, $numeric_var); (Sorry if $ doesn't indicate a variable in perl, I don't speak linenoise ;-) but I'm sure you see my point).

Now, since in your prepare() statement you nowhere stated that the second ? is expected to be a numeric value and any commas, quotes or other funny stuff should be removed, how does this remotely help? Surely you can still feed $numeric_var with "10; <naughty stuff>" - from your prepare statement I can't see how it knows how to sanitise the variable part?

Please be clear I am not casting doubt on your knowledge here - on the contrary, you seem to know the score*, and I haven't yet worked with this sort of prepared queries, and I'm genuinely interested.

* That said, Nos is right to point out slamming PHP as a poor tool is pretty stupid when you can easily use similar DB abstraction layers and the same techniques, if you choose to. Choosing not to is surely programmer error not a problem with the tool. And I can't see how you can claim PHP shouldn't let people have the choice, considering the whole thing about perl fans is venerating TIMTOWTDI.

Re:The "Oh-Sh*t" face...

[moco]

prepared statements are not exclusive to perl. VB, Java, and i believe php have those too. Writing secure software is not easy because of the developer's culture, not because developers don't know how to use prepared statements. Most of the inexperienced developers (or lousy experienced ones) who write web applications don't even know how the http protocol works.

Re:The "Oh-Sh*t" face...

[tuffy]

The parameters are encoded according to the types the scripting language is using. I'm no Perl expert, but Python's DBI uses something similar:

>>> db_cursor.execute("update User set name = %s where (user_id = %s)",["foo",10])

In this case, Python knows "foo" is a string and 10 is an integer and so it encodes both of them the way the database expects before submitting the query. If the "foo" string contained single quotes or other SQL-hostile characters, they'd all be properly escaped automatically.

Re:The "Oh-Sh*t" face...

[Dragonslicer]

You're exactly right, PHP hasn't had anything that does prepared statements for several years

Re:The "Oh-Sh*t" face...

[Ford Prefect]

And I've seen the "oh-sh*t" face on a couple of developers after demonstrating to them that their software is vulnerable to SQL Injection. In both cases the vulnerabilities exposed the customers to the posibility of serious financial damage.

Have you seen the 'oh, that's not a problem, we're using SSL so it's completely secure' face yet?

As for stupidest work-arounds - a site I was doing a vague security audit for (sans source-code, alas) was (is) rife with SQL injection vulnerabilities. On attempting to explain this to the programmer, with very simple code examples, I was confronted by some increasingly complicated 'solutions' to the problem.

It was an integer database ID. But strings would go in. I suggested converting it to an integer. But no, programmer decided to do a search-and-replace on SQL keywords in the string.

So I used lower-case SQL keywords, and strongly recommended converting to an integer.

Programmer added lower-case SQL keywords to his search-and replace routine.

So I used mixed-case SQL keywords, and told him to stop being silly and just convert to an integer.

Last seen, the code was replacing non-digits with &nbsp;. Okay, so it removed SQL injection attacks from that particular variable, but AAAAARGH.

Re:The "Oh-Sh*t" face...

[XanC]

Prepared statements aren't there to guarantee that every datum you insert is the "correct" type for what you're trying to do. What they do is guarantee that nothing in your variables will be interpreted as part of the SQL command.

If I have "10; <naughty stuff>" in $numeric_var, it will attempt to insert (or select, or whatever) exactly that string, without interpreting it. The data may be useless, but it will not be executed.

Re:The "Oh-Sh*t" face...

[soliptic]

Thanks to you and tuffy for the explanation!

Re:The "Oh-Sh*t" face...

[jrockway]

The database we're stuck with at work doesn't support prepared statements. Every SQL query looks like:

$query = "SELECT something,anotherthing FROM ". sqlClean($foo). " WHERE bar=". sqlClean($bar). "AND baz=$baz";

Could you spot the two bugs?

We're using some junk ODBC interconnect, because someone had the brilliant idea that the M in LAMP is "Microsoft SQL Server". Sigh.

Re:The "Oh-Sh*t" face...

[jrockway]

What's PERL?

That aside, the difference is that perl doesn't come bundled with a junk database interface that everyone is told to use. If you want to use a database at all, you have to do a little research. In that process, you learn how to do things properly (i.e. read the docs while DBD::mysql is compiling), and write better code as a result.

PHP is "easy to use", so people do whatever the fuck they think is right, and ususally get it wrong. If you actually had to read something before using PHP, we wouldn't have all these insecure PHP sites around. Of course, at that point PHP would have lost its advantage, and everyone would be using Perl/Python/Ruby :) Catch 22.

As for PHP's addons, it there are like 300 in PEAR, and none come with unit tests. Compare this to 10000 in CPAN, most of which come with a wide array of tests and documentation. PEAR is a good idea, but there's no community around PHP that cares about high-quality reusable code. Perl has that community, and that's why it's a much better programming platform. (Look at thinks like the CPAN Testing Service, CPAN Ratings, CPAN RT, etc. PEAR doesn't have any of that.)

Re:The "Oh-Sh*t" face...

[Logic and Reason]

$stmt = $db->prepare('SELECT id FROM categories WHERE name=?');
$stmt->bind_param('s', $_GET['category']);
$stmt->execute();

Apparently, I've been using nonexistent methods in all my PHP code! Boy, do I have egg on my face!

Seriously, I fail to see how it's hard to write an insecure SQL query in Perl. Does this not work?

$sth = $dbh->preprare("SELECT foo,bar FROM baz WHERE something=$user_input");

Re:The "Oh-Sh*t" face...

[jmertic]

> Perl's DBI, whose docs tell you to ALWAYS write SQL like:

> $sth = $dbh->preprare('SELECT foo,bar FROM baz WHERE something=? AND another = ?')
> $sth->execute(q{''Some\ things"'}, 10);

PDO does exactly this:

$dbh = new PDO("connstring");
$sth = $dbh->prepare('SELECT foo,bar FROM baz WHERE something=? AND another = ?');
$sth->execute(array($something, $another));

Check it out http://www.php.net/pdo

Re:Return of the Flat File

[Nik13]

There's no need to go back to the stone age. Just use prepared statements/parameterized queries (along with the usual stuff like validating user input client *AND* server-side)

Re:Sure, blame the "untrained" developers....

[dk-software-engineer]

If the server, by default, doesn't accept special characters in a password field, then that fixes most of these problems.

But then you couldn't make good passwords. Also, the password field isn't the only... oh wait, you said that:

Obviously, the password field isn't the only place where you can muck with the SQL

Exactly. So maybe you can't write "O'Hare" as a password, but with your method you couldn't enter it as your name. You just can't disallow special characters as a generic solution.

but if you're getting malformed fields from a valid userid and password, then you are much further along the path to shutting out the problem user who misused or had his access compromised.

You can hit SQL Injection problems by using the system exactly as you are ment to, for example if your name is O'Reily.

Re:Sure, blame the "untrained" developers....

[patrixmyth]

No worries. First, let me say IANAWD (I am not a web developer). It would not surprise me at all if my understanding of SQL injection vulnerabilities is less than yours. My understanding is that a SQL injection vulnerablity is when a user submits input to an interpreter that subverts the original intent of the script and produces a different result. I.E. I enter ' or '' = '' as the password field and that is passed through to SQL interpreter to read "where userid = 'user' and password = '' or '' = ''" I'm sure this is just one example of injection vulnerabilities, but by my understanding it's the most common form. What's your understanding? Instead of saying I'm ignant, edumacate me! I'd do the same for you. I'd bet any misunderstandings I have on the matter matches a sizable percentage of slashdot readers, hopefully mostly people in the same boat as myself who don't really need to the knowledge, just try to keep up with things. Cheers.

Massively widespread problem

[shawnmchorse]

If anything, I'd question how FEW sites they claim are vulnerable to SQL injection. It's an insidious problem that just creeps up on you anytime you don't think about it sufficiently (as when writing something quickly, on a deadline... not that this ever happens!). I know that at my workplace we fell victim at one point to a SQL injection attack on one of our (many) custom PHP scripts. We eventually found out how it worked through the web logs and were able to fix it, but honestly even after we did our best to clean things up... I'm dead certain that there are still probably hundreds of places that we're still vulnerable. This is due to a number things including the sheer volume of PHP code in use, the fact that the code has been written at various points in time over a period of six years or so, and the fact that this code has been written by at least twenty different people. It's like trying to plug holes in a dam.

Re:Massively widespread problem

[drew]

It's an insidious problem that just creeps up on you anytime you don't think about it sufficiently.

If SQL injection attacks are creeping up on you, than you are using the wrong tools. If you are using any halfway decent database access library (and suing it correctly) SQL injection is a non-issues. Example, using Perl:

my $sth = dbh->prepare("select * from users where user_id = ?");
my $rv = $sth->execute($dangerous_user_supplied_var);

I know the same thing is possible in PHP (via PEAR:DB), although I don't remember the syntax off the top of my head. Besides making you pretty much invulnerable to SQL injection vulnerabilities (assuming the underlying library is implemented properly), this can give you a number of other benefits, including easier to maintain code, and a decent performance increase on most sane database implementations. If you main method of executing database queries is this:

$foo = mysql_execute($dbh, "select * from users where user_id = '" . dear_god_save_me($scary_user_input) ."'");

Yes, it's a given that you will get bitten sooner or later no matter how careful you are. If you are smart, you use tools that protect you from the problem at the root. Otherwise, you will inevitably screw it up and get screwed, no matter how careful you think you are being.

Re:Massively widespread problem

[GigsVT]

Isn't your fancy object just going to do the same thing in the end? Seems like equally valid solutions to me.

Re:Massively widespread problem

[lagerbottom]

No, no it won't. He's using bind vars which takes away that threat.

Re:Massively widespread problem

[Slashdot Parent]

Yeah, but one of those functions was written by the guy who coded the database driver with the relevant specs in his hand, and the code has been looked over by many people and used in many production systems. The other code was written by some lazy hack who couldn't be bothered to just use bind variables or doesn't even know that they exist.

Whose code do you trust? I know whose code I would trust.

Re:Return of the Flat File

[faust13]

Isn't that called XML?

Re:Sure, blame the "untrained" developers....

[jizziknight]

As some others have pointed out, there are tools to get around that sort of thing. You should be validating all input server side, whether it's validated client side or not. There are also very easy ways to deal with SQL injection already, such as parameterized stored procedures. Doing what you suggest would involve a hell of a lot of recoding, break a hell of a lot of existing sites, and proceed to punish good coders by making them work around the "safe-guard". Programmers who don't take steps to protect against SQL injection deserve to have their sites hacked. And guess what? If they have their sites hacked, they'll probably remember to do something about it in the future, or hire someone who already knows how.

I may sound like an elitist by saying the following, but secure programming should not be "easy" for the "masses". It should be reserved for those who know what the hell they're doing, and they should be paid accordingly. I do not believe it's wise to give the average Joe the tools to make even slightly sophisticated programs. We've seen what happens when we do this; take a look at most websites from the dotcom bubble.

Re:Sure, blame the "untrained" developers....

[CaffeineAddict2001]

This is kind of like saying we can prevent buffer overflow exploits by having the windows API not allow people to enter shellcode into textboxes.

Re:Sure, blame the "untrained" developers....

[merreborn]

All the HTML standards in the world won't stop an attacker with a copy of telnet.

bash$ telnet example.com 80
GET /vulnerable_app.php?id=%2310 HTTP/1.1

Two things to note here: (1) there's no HTML involved in the actual transaction at all (2) like another poster said: you can't trust the client to send valid data.

Stick to purposing solutions for things you know about.

Re:No. That's a stupid idea.

[maxume]

For a blog with 15,000 entries and 5 MB of data, why does it matter? It fits in memory either way, and doing stupid mechanical queries is going to be 'fast enough', and not terribly complicated in the code. Sure, if you know SQL it's dandy, but it smacks of overengineering for a blog app.

Turn Key solutions broken?

[Bryansix]

Why are the point and click or turn key solutions so vulnerable to SQL injection in the first place? I had a friend with a PHPBB site that got shot to all hell when some cracker came along and defaced it. Why wasn't it secure out of the box? Second of all, why is it that every website has to worry so much about security. I know about databases but I don't know the first thing about preventing an SQL injection attack and why should I have to. There is nothing sensative on my sites. Let me throw out this analogy.

Let's say I own a house and around Christmas time I put out an inflatable snow man. Then some vandals come along and pop it. Are you going to walk up to me while I'm sulking over my snow man and say "Don't you know you have to wrap your snow man in kevlar to prevent vandalism and then put up an electified fence with constantine wire on it."? I would give you the strangest look if you did. Then I'd probably say something pertaining to the fact that the police should catch these bastards and presecute them.

So why is it with technology that no emphasis is put on catching vandals and bringing them to justice and a ton of emphasis is put on protecting your site from attack?

Re:Turn Key solutions broken?

[cyber0ne]

So why is it with technology that no emphasis is put on catching vandals and bringing them to justice and a ton of emphasis is put on protecting your site from attack?

Because currently the latter is FAR easier than the former.

While I generally agree with the sentiment that it's not the web developer doing anything wrong, it's the hacker who's wrong... I would also argue that while, by not caring enough about security, the developer may not be "wrong," he is perhaps being "unwise."

Re:Turn Key solutions broken?

[winomonkey]

Perhaps it would be more appropriate to use an analogy of vandals mortaring your snowman. For, you see, the problem with the e-vandals is that they are not actually physically present when they attack your snowman. Yes, there is proof that they attacked you, but chances are that determining their physical location is hard. Sure, you could look at the trajectory of the incoming projectile, but even then you are having to do a lot of imprecise and possibly flawed work to find out just where these attacks are coming from.

Another analogy, regarding security, is that of the attractive and financially well-endowed woman walking in a dark alley in a bad part of town. Sure, she SHOULD be safe, because she is a human and everyone should have some sort of basic respect for the dignity and humanity of all other people. Why is it, then, that she should have mace or a gun in her handbag? Why does she need to take a precaution to mitigate actual harm to her person? Why should she avoid a part of town known as being dangerous?

Apply this to web development. Why should developers protect their code? Why should they take extra steps to secure their sites and servers? Quite simply, the internet is a bad part of town, and we need to make sure that we are not doing things that make us susceptible to attack.

Concertina wire is more likely to help than counting on the prosecution of attackers and vandals.

Re:Turn Key solutions broken?

[Dragonslicer]

This is one of the things that annoys most serious PHP developers. phpBB is probably the worst, most insecure piece of code (and I use the term "code" loosely) written in the past 20 years. phpBB has at least one new security hole every week. phpBB is to PHP as Windows is to operating systems, so it's not really fair to judge PHP itself and any code written it based on experience with phpBB. It would be analogous to saying Linux and BSD are insecure because Windows is; they are all operating systems (probably mostly written in C).

Re:Turn Key solutions broken?

[RAMMS EIN]

``So why is it with technology that no emphasis is put on catching vandals and bringing them to justice and a ton of emphasis is put on protecting your site from attack?''

Which would you rather have: people vandalize your website, but they get caught, or people don't vandalize your website?

Re:Turn Key solutions broken?

[RAMMS EIN]

``Why are the point and click or turn key solutions so vulnerable to SQL injection in the first place? I had a friend with a PHPBB site that got shot to all hell when some cracker came along and defaced it. Why wasn't it secure out of the box?''

With PHP, virtually anyone can write a web forum. And lots of people do. Unfortunately, not all these people are good coders. Not all of them are aware of the security risks. And PHP doesn't provide particularly secure APIs for SQL (or at least it didn't when I last checked). With better languages (or APIs), injections could be made impossible.

Re:Turn Key solutions broken?

[Bryansix]

Third option: People don't vandalize but not because it is impossible to vandalize but because people are afraid of getting caught.

Re:Turn Key solutions broken?

[g1zmo]

I had a friend with a PHPBB site that got shot to all hell when some cracker came along and defaced it.

Why you gotta get all racial about it?

Re:Turn Key solutions broken?

[00lmz]

Let's face it, PHP hasn't had a standard database API for quite a long time (I guess there's PDO now). The most common database that PHP is used with is MySQL, which did not have bind parameters for quite a long time, so PHP programmers resort to mysql_real_escape_string or before that mysql_escape_string and then concatenating the query and the parameters while better databases have supported bind parameters since PHP 3. And even if the database API does not natively have separate parsing and binding stages, at least it would make sense to emulate them, because if the database API later gains the ability to bind parameters / send parameters separately from the statement, the users don't have to change their code. If mysql_query was used like this:

mysql_query($db, "select * from products where (color = ?) and (price < ?)", $color, $maxprice);

Then no-one would complain about escaping statements being "hard". Instead we have things like gpc_magic_quotes instead...

Re:Turn Key solutions broken?

[tehcyder]

an analogy of vandals mortaring your snowman.

I think you've out-BadAnalogied BadAnalogyGuy there.

Re:Turn Key solutions broken?

[Stooshie]

Call the Prepare() method as has to be done with any language.

It's not the language it's the programmer.

Re:Turn Key solutions broken?

[ivan kk]

Because the vandals are in eastern european countries where it's not illegal.

Re:Turn Key solutions broken?

[Bryansix]

Ok, I wasn't talking about Credit Card databases but I would go one step further and say that CC databases shouldn't even talk to the web. They should just be able to receive information but not send it back to the Internet. CC numbers and transaction info should then go to the processing server via a private or virtual private network.

Re:No. That's a stupid idea.

[Reality Master 201]

For a blog with 15,000 entries and 5MB of data, use Blogger or Typepad.

And, frankly, I don't think learning how to use the tools properly and how to test one's code is "overengineering." I think of it as competently programming the application. But I'm old fashioned.

This is way low

[CaffeineAddict2001]

I've worked in web development a while and I find a SQL injection vulnerability in about 90% of the sites I've seen.

It is extremely common to have people just cut and paste the bare-bones tutorial code they find on the web and reuse that same pattern on every page in the site rather than centralizing it in a wrapper. So not only is the string not being cleaned, but it's also a huge pain to fix.

Re:This is way low

[Dragged Down by the]

I'd like to know the percentage of sites built OFF SHORE with vulnerabilities ...

Re:No. That's a stupid idea.

[Stile 65]

Absolutely. Here are some rules to defend at multiple layers against SQL injections (using SQL Server as an example; YMMV on other DB servers):

- Run your web application as a user that connects to the database server and has rights only to SPs and views on the database; this works because the SPs and views have full access to database data, but the user can't access the data except through those pre-defined means

- Encapsulate calls to those SPs and views inside carefully constructed functions/objects/etc. and force all developers to communicate with the DB through those functions/objects

- Inside the functions/objects, use parametrized queries for calling all SPs and SELECTs on views in order to avoid any potential for evil

This way, if someone messes something up at one level, the other two levels are hopefully there to protect against any potential vulnerability. Also remember to, if possible, never store DB authentication information in plain text.

Re:Sure, blame the "untrained" developers....

[patrixmyth]

Alright, back to my PHB duties, but if we only proposed solutions for things we know about, then we'd all still be picking termites out of logs with twigs. I still hold to my fundamental belief that some things are meant for professionals, ie installing ATM machines, and some things are meant for everyone, ie posting websites, and the level of safeguards has to take that into account. Some people may feel that the web should belong to the trained professionals, but personally, I'd rather see a lot of crappy personal and community webpages than a few properly formed and secured corporate and government sites. You may disagree, and probably do, but I'm right. :)

Re:Return of the Flat File

[MagicM]

That, and/or bind, bind, bind. Concatenating user input into your SQL statements is bad on both security and performance.

Re:Sure, blame the "untrained" developers....

[merreborn]

There's nothing wrong with the Joe Sixpack building his own website. The web wouldn't be what it is today, if it weren't for that. However, there is a problem with him developing software on systems open to the world.

Once you add executable code, and a database, it's not 'just a website' anymore. It's a program. And running amateurs' programs on the open internet is dangerous.

Re:Sure, blame the "untrained" developers....

[poot_rootbeer]

This certainly seems to me to be a problem that needs to be addressed at the html standard level

I'm not following you. Why would a server-side exploit like SQL injection be addressed in a client-side display standard like HTML?

The 'type="password"' attribute of the HTML input element is nothing more than a style hint for the renderer -- characters typed into such an input should not echo back to the screen. Otherwise there's nothing that distinguishes it from a value taken from a text input, a checkbox, a file upload dialog or any other HTML form control. An HTTP POST is an HTTP POST.

And without knowing what the server plans to do with the data once submitted (which the client has no reason to need to know), how does one define what "special characters" are? A string that's going to be inserted into an SQL database has a different set of special chars than one that's going to be used as part of a filepath on a Windows box, which is different than one that's going to be used as part of a filepath on a Unix box, which is different than one that's just going to be compared against another string.

Business logic does not belong on the client side.

There are a lot of new programmers [...] who are not naturally paranoid and sensitive to the exploitation of their code. They shouldn't need to be.

The hell they shouldn't.

Programming consists of more than just typing out some PHP code that doesn't cause a fatal error when executed. A programmer needs to be conscious of the expected inputs and outputs of any piece of code he/she is responsible for, and aware of the ramifications when those expectations are not met.

A person who does not exercise such diligence should not be considered a programmer.

Re:Sure, blame the "untrained" developers....

[masklinn]

Indeed, people need to understand that Javascript validation of forms is and should always be a courtesy to the user. It can always be avoided, and this means that client-side validation is done solely for the comfort of the user (so that he doesn't waste time making useless errorneous queries to the server).

This means that obtrusive client-side validations generating popups and shit everywhere are beyond stupid. Keep your JS error messages clear yet unobtrusive, because they will never stop someone who wants to fuck with your server.

Re:Sure, blame the "untrained" developers....

[slamb]

In short, trusting the client (i.e. the web browser) to not send bad values - either through the INPUT tag's maxlength attribute, JavaScript scrubbing or whatever - is entirely the wrong way to go. The web script must check all user input for validity along with properly escaping everything from the database that's getting sent back via HTML.

It's not just "bad" values, unless you believe that all people with names like O'Malley are out to destroy your website. SQL Injection is a very simple problem: people are confusing arbitrary data with fragments of SQL. The former should be passed in through bind variables - or escaped if you're hellbent on destroying your performance - while the latter should be executed directly. It's fortunate for the O'Malleys out there that this mistake is a huge security hole, or they would be rejected by any kind of automated system. People barely care about security; they certainly don't care about "lesser" bugs.

I often see people claim you just check for bad characters - either specific lists of punctuation or anything outside the alphanumeric range - because you can't possibly get all of your database code right. That's an incredibly wrong idea:

• You can and must get all of your database code right. I developed Axamol SQL Library for this purpose. Other solutions vary.
• The "nothing but alphanumeric" crowd's code doesn't work with Unicode. So José can't type his name in properly.

some are untrained, but ...

[AllCdnBoy]

Going through my bachelors, we all learned the same things, yet there were always people that had unsecure code. It didn't come down to "just not getting it". It was more that they were lazy and wanted it done fast.

Re:some are untrained, but ...

[suggsjc]

...they were lazy and wanted it done fast

Rookie mistake. But little do they know that will end up spending orders of magnitude more time trying to "fix" it in the future.

Although the context is different. The best programmers I know are the laziest ones. Not in that they don't do much work, but that they have extensive knowledge of tried and true libraries. They don't reinvent the wheel.

To bring this back full circle, being able to use both a modular AND mature filtering process would be a GIANT leap forward for most web application. I'm in the process of using/learning Pear::DB and all of its features. Also, I just stumbled on the OWASP project http://www.owasp.org/index.php/Main_Page/ It looks like it has promise, but still needs some polishing.

Re:Sure, blame the "untrained" developers....

[tuffy]

I use parameterized SQL queries so the notion of having to check/escape something as simple as single quotes hadn't occured to me. I was thinking more along the lines of ensuring user entries match the necessary encoding and maximum length. For instance, if my web forms want UTF-8 encoded output, is the user sending me UTF-8 encoded data? Or if I want the name field to be a maximum of 50 chars in the database, does the encoded string fit?

A little automated input fuzzing doesn't hurt, either.

What about Google's Search by Number?

[slashd'oh]

Your post made me curious about how Google does it via their Search by Number feature. For example, if you search Google for "usps " plus your tracking number (or just the number), the results will be preceded by a link to "Track USPS package..." I suppose your widget could use Google's custom redirect script, but why is the functionality restricted to Google?

Persistence Framework

[Dopeskills]

Sounds like even more incentive to use a good object to relational framework like Toplink or Hibernate.

Re:Sure, blame the "untrained" developers....

[Mister Whirly]

ATM = Automatic Teller Machines

"ATM machines" = Automatic Teller Machines Machines - definitley leave it to the pros, otherwise you may screw up on your "PIN number"

(Apologies for being a pseudo-grammar Nazi)

Re:Let's find out...

[omeomi]

What I don't understand is how an SQL error message makes you vulnerable to a SQL Injection attack? Even if you are able to find out some of the tables and fields in a web-accessible database, you don't have the password to be able to execute your own queries...Is there just an assumption here that anybody stupid enough to allow a verbose error message like that would also have a database password like '12345'?

Re:Return of the Flat File

[aztracker1]

In the case of .Net languages... Parameterized Queries. should note, that the parameter formatting is slightly different for other database systems.

Re:Sure, blame the "untrained" developers....

[legoburner]

Basically, your code will send an SQL query like this:
SELECT * FROM USERS WHERE USER_ID=3;

This might be assembled like this:
"SELECT * FROM USERS WHERE USER_ID=" + userID;

userID might be taken as a direct parameter from the front end and not sanitised. Even if user ID is a hidden form field, it would still be possible for someone to enter this (simplified for the sake of argument):
someurl.jsp?userID=1; DROP DATABASE DBNAME;

which would go through to your DB as:
SELECT * FROM USERS WHERE USER_ID=3; DROP DATABASE DBNAME;

This would have the effect (with poetic license) of performing the first query as expected and then deleting the database, destroying the website.

The way to get around this basic instance would be to convert the userID parameter to an integer, forcing a failure before the SQL is reached. This could be done in java with Integer.parseInt(userID). However, this does not protect against bad characters in Strings so is not a total solution.

The total solution is to use a parameterised statement (preparedstatements are very useful for other reasons and are one of the ways to get this advantage too). In a preparedstatement for instance, you would say:
thisstatement = "SELECT * FROM USERS WHERE USER_ID=?";
thisstatement.setInt(1,userID)

which sanitises the query using setInt setString setDate or whatever. This will escape the invalid chararacters (such as ') so you can still have any character in a field, like O'Hare for a name, but it will not lead to an SQL injection.

On a related note, another vulnerability I see a lot is when people do updates or deletes using a table with multiple keys. If for instance you have multiple sites sharing a common database such as:
SITE_ID, USER_ID, USERNAME
with the 2 IDs as a PK, doing a simple update on USER_ID is not secure. Even if USER_ID comes from a hidden form field it is still easy for a malicious user to manipulate and change to something else. If you have a multiple PK, you must practically always use both fields when updating/editing/adding.

Note that I have over simplified things but this should give the general gist of the problems.

Re:Sure, blame the "untrained" developers....

[legoburner]

which would go through to your DB as:
SELECT * FROM USERS WHERE USER_ID=3; DROP DATABASE DBNAME;

should be
SELECT * FROM USERS WHERE USER_ID=1; DROP DATABASE DBNAME;
simple copy and paste error there oops

Re:Let's find out...

[arclyte]

Well, I have verbose error messages turned on on my local dev server to make debugging easier, but we log all errors on the live site, returning an "oops! something went wrong" generic error to users. Verbose errors can give you a lot of information, depending on what the error is.

First of all, it's going to tell a hacker what kind of system you're using. They'll probably find out anyway if it isn't apparent, but who wants to give them any help along the way? And then it all depends on what the error is. If i'm making reads to the filesystem or requiring files and I screw up the path, verbose error reporting will spit that path out. Now the hacker knows what your file system looks like, and possibly what kind of system you're using. Also, while the hacker is busy trying out various methods to inject SQL, verbose errors may spit out the entire failed query, giving them full knowledge of how to edit it to make it work...

All around, it's just a very bad thing.

Why errors are good for crackers

[omgwtfroflbbqwasd]

The SQL error message unto itself is not an indicator of vulnerability. It *is* an indicator that you were able to get input from the web form to manipulate/modify the SQL query (and break it). This is the first step in determining whether a given web app is even exploitable. If all you keep getting back is "invalid username" or a normal app response, it's unlikely that the app is vulnerable.

The trick to exploiting SQL injection is being able to figure out the right sequence of input characters needed to gracefully terminate the intended SQL command, while also being able to craft a subsequent SQL query that does what you intend. Alternatively, you may want to modify the intended command. For example, "SELECT orderstatus FROM table where orderid = $FORM{'orderid'}" expects the orderid form field to be some value that's in the table somewhere. If instead you enter "1 OR 1=1" as your input, you'd get every row back since 1=1 for all rows. Using this example, you could also try to append a new query, by entering something like "1; drop database;". Many times an attacker will need to find various escape characters, quotes, etc. to get their input crafted properly to exploit the app.

SQL errors in web apps are what web crackers like to see for the same reason that exploit authors like to see segfaults where EIP=0x41414141.. it means that they were able to get their input down to the execution point in a program that got past the boilerplate protections that the app had in place (if any).

Re:Why errors are good for crackers

[omeomi]

Interesting, thanks. Using this example, you could also try to append a new query, by entering something like "1; drop database;".

Hmmm...that reminds me, time to backup the 'ole database... ;-)

Re:Why errors are good for crackers

[thewils]

As I mentioned in a prevous post on this topic, explicit error messages are a godsend for a hacker. Rather than try to insert a numeric value where one isn't required, you could, for example insert (Select name from users where id=1) and the nice error message will tell you something like "Unable to convert 'George' to an Integer". I've actually seen this on a commercial website.

DB Wrapper?

[TheNinjaroach]

I am pretty new at PHP development, but not new to programming. I had to learn SQL on-the-spot and I'm aware of SQL injection attacks and the risks they pose.

We have implemented a DB wrapper that "escapes" user input for things like ' that would break out of the input. I also check the length on the server side to make sure they're not trying to overflow any of the variables. By the looks of many of these posts, that isn't an "acceptable" amount of protection and that procedures are a must.. Anyone care to inform me or refer me to a good website?

Re:DB Wrapper?

[chez69]

use the stuff provided by pear, don't ever write your own escape functions, or well you'll get owned.

if your new to programming, the first lesson to learn is know what tools you have available so you don't reinvent the wheel.

Re:DB Wrapper?

[julesh]

You should probably be using this API, particularly the prepare statement/execute functions of it.

You don't need to bother with SQL Injection

[thewils]

If you search for "mdb" you can download the entire database without too much trouble.

I recently came across a commercial site where you could substitute, for instance, "(select first_name from users where id=1)" into the page url and a nice error screen came up telling you that it couldn't convert "George" into an Integer.

It's not the SQL Injection per se that is the biggest problem, but the nice error messages you get back giving you, more or less, a SQL command line interface. Errors should be detected and redirected to a sanitized page, or if you can't be bothered, an unceremonious crash.

I notified the owners of that site by the way.

Better APIs

[RAMMS EIN]

I've written about this before. Basically, SQL injection vulnerabilities would completely disappear if better APIs were used. The problem is that queries are composed as strings, which have no intrinsic structure. The programmer creates structure by inserting certain characters (in particular, single quotes) in the string. However, the exact same mechanism is used to add user-supplied data to the queries. Unless the programmer is very careful, this allows the user to affect the structure (and thus the effect) of the query. An example is in the essay I linked to.

Re:Better APIs

[RAMMS EIN]

``The API that is available that prevents this is using parameterized
SQL where the SQL statement is managed/handled separately from the
parameter values. It is more complicated and requires more programming
than just shoving a hand built query at the database but it much more
secure. ''

That's not the only option. In my essay it shows a Lisp macro that
allows you to write queries pretty much as normal, but that still
escapes any data that comes from variables. More convenient than the
parameterized query APIs I've seen, and more secure than the string
composition method. The macro is actually very easy to write.

Re:Better APIs

[Goaway]

Most languages (Perl, Python, Ruby, for instance) have had better APIs for years and years.

PHP doesn't.

Re:Better APIs

[julesh]

PHP has supported parameter substitution for years. Quit spreading this myth.

The problem is simply that none of the standard "how to program with PHP" introductory texts mention it, but jump straight into low-level MySQL-specific APIs that shouldn't ever be used directly, IMO.

Re:Better APIs

[jamespharaoh]

Can you provide a link to this please?

Re:Better APIs

[RAMMS EIN]

Assuming you meant the link to my essay, it's right in my original post, but here goes again: Better Software Through Better Languages. It doesn't actually contain the source for the macro, but the following works:

(defmacro query (&rest parts)
        (format nil "~{~A~}" (mapcar (lambda (part)
                (if (stringp part) part (sql-escape part))) parts)))

where sql-escape should return an appropriately escaped string.

However, I guess it would be a better idea to just use CL-SQL.

Re:Better APIs

[Goaway]

If it's not the default, it's useless. Nobody will know about it, and nobody will use it. The people most likely to know about it are the people who are the least likely to write insecure code in the first place - experienced programmers.

RFC 2616 doesn't say anything about proper use

[omgwtfroflbbqwasd]

Are you referring to a different RFC? 2616 establishes the function for the various HTTP requests, among other things. The only recommendation that it provides is regarding sensitive data as part of a GET URI, from section 15.1.3:

Authors of services which use the HTTP protocol SHOULD NOT use GET based forms for the submission of sensitive data, because this will cause this data to be encoded in the Request-URI. Many existing servers, proxies, and user agents will log the request URI in some place where it might be visible to third parties. Servers can use POST-based form submission instead

Repeat after me kids: "GET and POST are equally tamperable and equally (in)secure. The only thing they are not, is equally logged".

Re:RFC 2616 doesn't say anything about proper use

[profplump]

Apparently you missed section 9 of RFC 2616:

9.1 Safe and Idempotent Methods

9.1.1 Safe Methods

      Implementors should be aware that the software represents the user in
      their interactions over the Internet, and should be careful to allow
      the user to be aware of any actions they might take which may have an
      unexpected significance to themselves or others.

      In particular, the convention has been established that the GET and
      HEAD methods SHOULD NOT have the significance of taking an action
      other than retrieval. These methods ought to be considered "safe".
      This allows user agents to represent other methods, such as POST, PUT
      and DELETE, in a special way, so that the user is made aware of the
      fact that a possibly unsafe action is being requested.

      Naturally, it is not possible to ensure that the server does not
      generate side-effects as a result of performing a GET request; in
      fact, some dynamic resources consider that a feature. The important
      distinction here is that the user did not request the side-effects,
      so therefore cannot be held accountable for them.

Re:Return of the Flat File

[RingDev]

Correct, SPs will not fix every possible exploit, but they will prevent someone from sticking a trunc command in to the middle of your dynamic select statement.

-Rick

Moo

[Chacham]

Blame MySQL.

MySQL is not a database. It does not support Transactions. It doesn't use normal syntax. Why? Because it's easy. So a programmer who has better things to do than learning some arcane syntax can just get the job done and move on. Good? No!

It is because of this laziness that such attacks occur by someone who did take the time to learn the syntax. If people want a quick and dirty solution, so be it, but don't pretend to be a database and open all sorts or vulnerabilities.

The ease of SQL is made for people who take the time to learn it, and naturally code it securely. For those who don't have the time, just don't use SQL.

So, i blame MySQL (and its ilk) for this. And i can only laugh at the sites that get attacked because of their own laziness.

Re:Moo

[TheNinjaroach]

I understand that MySQL is in fact designed to make programming easier, but I've found SQL to be very easy and understandable. We're using MSSQL where I work (it might be the only Microsoft product I have respect for) but found the language quick and easy to pick up.

I do agree it's mostly laziness that gets people (not bothering to research enough on SQL injections) but it is hard to test my own code for all possible attacks.. If one of my apps were to be SQL-Injected I would not chalk that one up to laziness, but possibly incompetence. I've done my homework, the only suggestions I've ingored are parameterized stored-proc calls.. I'm not even sure *they* could prevent an attack.

Re:Moo

[Chacham]

We're using MSSQL where I work (it might be the only Microsoft product I have respect for)

Ehem. It's a fork of Sybase SQL Server. :)

but found the language quick and easy to pick up.

When taught properly. Otheriwse, people "sort of" know how to use it.

As it so happens SQL Server is not strict to the standard, and transfering to strict databases (DB2, Oracle, PostgreSQL, etc) always generates questions like "How do i do this in ...", because they actually never learned the standard.

I do agree it's mostly laziness that gets people (not bothering to research enough on SQL injections) but it is hard to test my own code for all possible attacks..

Testing for attacks is after secure code is written. If the "security" is by adding in "preventions" for the attack, that is not a sign of good coding.

If one of my apps were to be SQL-Injected I would not chalk that one up to laziness, but possibly incompetence.

Which in a coder, is a sign of laziness. At least that's my feeling on the matter.

I've done my homework, the only suggestions I've ingored are parameterized stored-proc calls.. I'm not even sure *they* could prevent an attack.

They can. And it's very easy. The first lines of code should be parameter verification based on bounds checking, and RETURN (or RAISE) an error and exit the PROCEDURE.

Re:Moo

[julesh]

Perhaps you're trolling, but...

MySQL is not a database.

So how come I can store and retrieve data from it?

It does not support Transactions.

MySQL has supported transactions for something like 5 years now. Sure, early versions didn't support transactions.

It doesn't use normal syntax. Why? Because it's easy.

I've seen a lot of variation in SQL syntax between different vendors. MySQL is nothing unusual in this respect. What specific syntax variation is giving you trouble?

Re:Moo

[Chacham]

>>MySQL is not a database.
>So how come I can store and retrieve data from it?

Because it has a mock-SQL interface.

You can also store and retrieve data from a file. That doesn't make it a database.

I've seen a lot of variation in SQL syntax between different vendors. MySQL is nothing unusual in this respect.

They have additions to the SQL standard, and the standard is the main syntax.

Re:Moo

[julesh]

Funny, I never realised that supporting ANSI SQL was a mandatory feature for databases.

Re:Sure, blame the "untrained" developers....

[Ford Prefect]

For instance, if my web forms want UTF-8 encoded output, is the user sending me UTF-8 encoded data? Or if I want the name field to be a maximum of 50 chars in the database, does the encoded string fit?

Try harking back to the good old days of programming, when men were real men, women were real women, and small furry creatures from Alpha Centauri knew how to write functions. Write something like this:

sql_string_sanitise( $string, $encoding, $max_length );

And you can do stuff like that, easily!

Comment removed

[account_deleted]

Comment removed based on user account deletion

Re:Let's find out...

[cascadingstylesheet]

>Even if you are able to find out some of the tables
>and fields in a web-accessible database, you don't have
>the password to be able to execute your own queries...

If you can inject SQL, then *you* don't need the password. The web page sends the SQL to the database server.

Re:Sure, blame the "untrained" developers....

[dk-software-engineer]

It would not surprise me at all if my understanding of SQL injection vulnerabilities is less than yours.

It seemes that you understand the basics, but not the implications.

Instead of saying I'm ignant, edumacate me!

Here's some education: Don't get your education from slashdot! ;-)
But if you want to make sure you understand the basics of SQL Injection: http://en.wikipedia.org/wiki/Sql_injection

The solution is a combination of several good practices in software development. First of all: Make sure you know which data you can trust. Everything that comes from the user: Don't trust! Allow anything coming thru the front door, because you can't prevent it anyway,
First you validate the data. Let's assume worst case: You allow anything, there's nothing to validate.
Next: Insert it into the SQL in a way that can't hurt. (Escape it correctly...)

An extra way to take care of this: Separate your software in layers. Have all database/SQL-stuff in it's own layer. Study "Layers pattern", sorry no links. :)
Then you can define exactly how data should look with traversing thru the layers, and it's a lot easier to make sure that everything is validated, transformed, converted etc. at the right time.

Not a problem

[Firehed]

SELECTing `userID` when it's undefined really isn't a problem. Had you WHERE'd correctly (`userID` again, not just `user`), though, it'd have been a perfect demonstration of one of the most fundamental injection attacks.

Remember kids, NEVER trust user input. Escape things until the key is broken, then make the computer do it for you automatically.

Re:Return of the Flat File

[Cico71]

Why convert to an entirely different structure when just implimenting proper code standards will suffice? Using parameterized stored procedure calls instead of dynamic SQL will not only protect you from the vast majority of SQL Injection attacks, but will also improve the performance of your web page.

Performance is a good point. Some rdbms will cache stored procedure plans in order to reuse them saving compilation and optimization time. Even when not using stored procedures, it's a good thing to use prepared statements since some rdbms are going to produce a plan in cache that can be reused with different parameters values. OTOH this is not always going to be the ideal thing because a cached optimized plan for a given set of parameters values may not be the optimal plan for a different set of parameters values.

Re:Sure, blame the "untrained" developers....

[slamb]

I use parameterized SQL queries so the notion of having to check/escape something as simple as single quotes hadn't occured to me.

Likewise, so neither of us have the problem this article is talking about. CVE's #2 error (which accounts for 14% of all reported security problems, and is present in at least 11% of this guy's web site sample) is really that basic.

As for your checks, I do things a little differently:

• In servlets, I typically let the servlet container and Java runtime take care of character encoding issues for me. By the time I get it, it's a java.lang.String, which is valid Unicode. (I think it's stored as UTF-16...but I'm not quite sure, because that's a detail I don't have to bother with.)
• I tend to just catch length errors coming back from the database. In general, you need to correctly handle errors coming back from the database anyway. If you don't serialize all accesses to the database, you'll get a unique constraint violation at some point. So I go further and just handle the SQLExceptions in general, rather than trying to avoid them. It makes the error path less efficient, but I just don't care about the efficiency of the error path. It saves code and eases maintenance, since I don't have to put "this field can only be 50 characters long" in as many places.

There's far better Lisp solutions

[Estanislao Mart�nez]

You can do far, far better than that in Lisp. A SQL query is essentially a list of column expressions (the SELECT), a list of table expressions (the FROM), and a predicate over the tables/aliases named in the FROM (the WHERE). It's pretty straightforward to represent these as embedded language in Lisp that constructs three-part objects that represent queries with symbolic expressions, and then write a little interpreter to generate SQL statements from the objects in question.

Once you have this, you get the really, really big win: you can write an operation to merge multiple query objects into one, that works as follows:

1. Append the lists of column expressions from the source queries, removing duplicates.
2. Append the lists of table expressions from the source queries, removing duplicates.
3. Join the predicates from the source queries with an AND at the front.

This approach allows you to write code that generates really complex queries in a piecemeal fashion; you can generate separate query objects for individual parts of your query in separate parts of the code, and then merge all the bits at the end into the finished query object, which you translate into a SQL string just before execution. Your embedded language can also take care of escaping automatically for you. You never deal with queries as strings at all.

And this is of course an approach that is limited by the need to support SQL as your target query language; if you didn't have that, you could do a much better query language embedded within Lisp to start with.

Re:Return of the Flat File

[espressojim]

I'd mod you up, but I spent too much time telling other people to get a clue.

PS: try Spring framework or Hibernate, if you don't already and use Java...

Re:Let's find out...

[geekoid]

some errors will return information from the table...like username and password. Others allow you to control the data from the attack and other give you root access after the error.

Re:Sure, blame the "untrained" developers....

[budgenator]

bash$ telnet example.com 80
GET /vulnerable_app.php?id=%2310 HTTP/1.1

the above starts a telnet client which is connected to the webserver at example.com over the standard HTTP port, 80. The operator then starts a standard HyperText Transfer Protocal version 1.1 , by using telnet the operator can see all of the transactions the server makes with the client. Then the operator requests the server sends the page vulnerable_app.php?id=%2310. Any webmaster that doesn't understand using a telnet client to troubleshoot a seriously borked webserver probally has more issues than just SQL injection.

I've seen Good sysadmins running a webserver in the debuger and tracing the entire process, while connecting to it with telnet to trouble-shoot particulary nasty problems.

Only 11%?

[dtfinch]

I just can't imagine that many web developers being so careful. Popular sites are relatively secure, and open source code like online forums are secure, but the security of write-once code you find on average sites is just horrible. Just search google for things like 'allinurl: "isadmin=0"' and you'll know what I mean.

it\'s easy to fix SQL injections

[GrumpySimon]

Magic quotes! No more problem\'s!

Re:it\'s easy to fix SQL injections

[dk-software-engineer]

That one is hard to mod. I thought it was funny because of the irony. But it's really quite informative. But mod it informative, and people will just read the words and not get the irony.

But just in case: Magic quotes does the right thing at the wrong time. This means that strins is sometimes escaped when they shouldn't be, and sometimes not escaped when they should be. So really it's just false security, the only security worse than nothing.
The official advice is to disable magic quotes. It doesn't work and it was never a good idea.

The best solution is still parameterized queries or other abstractions that removes any chance of getting this wrong.

Re:Return of the Flat File

[will_die]

Which database and version are you using???
For the past 10 years in all the major mainstream databases you have not gotten a performance boost for using stored proceures with standard CRUD statements. In truth the use of stored procudures in CRUD with all the coaleses and if statements is actually is around 20%+ slower, when you start hitting 5+ possible variables, then a dynamic SQL statment using parameterized variables.

Re:Are these still around?

[julesh]

For login scripts:
"' or 1=1 --" -- can force a login to succeed in some systems
"' or password='' --" -- works on some systems where that doesn't

For data retrieval scripts:
"' or 1=1 --" -- retrieve all data from the database, not just the record asked for; this is a useful one.

For data update scripts:
"' or 1=1 --" -- update all records in the database, not just the one that's identified

There are many other variations on these.

Yes, not allowing multiple statements mitigates the vulnerability a little. But not a lot.

Re:Return of the Flat File

[Tim C]

SPs? Stored procedures?

He's not talking about using stored procedures, he's talking about using bind parameters. You can still use "normal" SQL, it just prevents injection attacks.

Re:It's more than 11%, I'm sure of that!

[julesh]

But anyway, web developers should not be using SQL any more, except in some very rare cases. We now have things like EJB3 and Hibernate which remove 99% of the need for any SQL within an application.

There are good reasons for using SQL over and above an object persistence layer (which is what I believe both of these are, although I've never used either). SQL is easier to search, easier to analyze in arbitrary ways, and easier to edit by hand if something goes wrong and the system needs fixing.

Re:Return of the Flat File

[RingDev]

Hit Parent a few times. My first post was on using Store Procedures. An Anon Coward responded. The post you replied to was in response to the AC, not the guy talking about binding which yes, is another way to limit injection. Note that neither of these techniques will get you 100% protection however.

-Rick

Even when writing quick and dirty code...

[Slashdot Parent]

Get in the habit of using prepared statements and bind variables. Even when you are writing something quick and dirty.

If you ever find yourself writing SQL queries without prepared statements and bind variables, erase the code and rewrite it with prepared statements and bind variables.

If you still find yourself writing SQL queries without prepared statements and bind variables, slap yourself twice across the face--once for each cheek--and then erase the code and rewrite it with prepared statements and bind variables.

If you still find yourself writing SQL queries without prepared statements and bind variables, obtain a hammer and smash all of the fingers on one hand until they are no longer usable. Find a friend to smash the fingers on your other hand until they are no longer usable. Then, you can write no more lousy, insecure code.

-1: Totally wrong

[Slashdot Parent]

This has absolutely nothing to do with MySQL, PostgreSQL, Oracle, DB2, MSSQL, or the SQL language at all. Nice offtopic rant about MySQL, though.

The fact is, code vulnerable to SQL-injection is database platform-independent. The vulnerability is at a higher level. Do you really think forcing these lazy people to use a "harder" database that takes more time to administer and learn will make them write secure front-end code? Hardly. It'll probably make the front-end code even more rushed.

You want no more SQL-injection? Use prepared statements and bind variables. If you roll your own input-escaping code, you'll foul it up. Doesn't matter what database you're using on the backend. If you foul it up, your Oracle database will be just as vulnerable as your MySQL database.

But please don't let that stop you from mindlessly ranting about MySQL.