Anatomy of a SQL Injection Attack

Trailrunner7 writes "SQL injection has become perhaps the most widely used technique for compromising Web applications, thanks to both its relative simplicity and high success rate. It's not often that outsiders get a look at the way these attacks work, but a well-known researcher is providing just that. Rafal Los showed a skeptical group of executives just how quickly he could compromise one of their sites using SQL injection, and in the process found that the site had already been hacked and was serving the Zeus Trojan to visitors." Los's original blog post has more and better illustrations, too.

Comments

Use a persistence library

[ls671]

One should definitely use a persistence library instead of concatenating strings to help mitigate the possibilities of being victim of SQL injections. They are pretty good at it. Hibernate is a widely used one.

 

Re:Use a persistence library

[Splab]

One should use positional/named bindings and let the driver handle escape sequences, make sure the Web user only has access to what is needed, rather than running everything as root. Use procedures/views where possible and never allow dynamically created queries.

Re:Use a persistence library

[Anonymous Coward]

Or use directly an ODBMS like Versant with the JDO persistence API. That is nearly unbreakable.

Re:Use a persistence library

[AuMatar]

Persistence is just a bad idea, it hides the real performance issues of how databases work, and limits how you can easily manipulate the data. A better idea is just to always use bind variables. Problem solved.

Re:Use a persistence library

[mk_is_here]

A more simple way is to use a parametrized statement. No extra libraries if you are using Java, .NET, or PHP5.

Re:Use a persistence library

[Anonymous Coward]

I have found, that if used correctly, hibernate can be quite powerful; you can still run native and database independent HQL queries if you like.

You can also map your native queries to objects; it is quite easy, and I believe it is same as binding to variables.

The entity manage also helped me to reduce the amount of queries that I hard code into my DAOs; you can query for objects based on their class and ID (yes, it does support composite IDs).

Also provides control for optimizations, and will automatically link objects together (depending on if they are eagerly fetch or lazily fetched)

Read a little about Hibernate before passing judgement on it.

Although, i got to admit; it does hide quite a bit. I have been on a few teams who were using it incorrectly, and the application performance degraded quite a bit because of it. (IE, retrieving a list of hundreds of Entity objects, instead of selecting only the properties they want in a HQL statement)

Re:Use a persistence library

[Anonymous Coward]

"Use procedures/views where possible and never allow dynamically created queries." How true. Still upsets me how many developers are anti Stored Procedures

Re:Use a persistence library

[SpazmodeusG]

You still need bind variables mentioned by the gp if using HQL.
http://www.owasp.org/index.php/Interpreter_Injection#ORM_Injection

Re:Use a persistence library

[Hurricane78]

Yeah, until someone comes at it with a cross-site scripting attack. ^^

Re:Use a persistence library

[Anonymous Coward]

I second this. Preventing SQL Injection is ridiculously easy, with either parametrized statements, stored procedures or a persistence library and of course input sanitation. There is simply no valid excuse for failing to use either of these methods to prevent it.

Re:Use a persistence library

[Anonymous Coward]

> Still upsets me how many developers are anti Stored Procedures

Using stored procedures is harder than just creating the SQL query.

You need to know even more about the DB.

Even it's harder if you have to get the DB guy to do stuff for you.

Yes you can run your own DB in development, but in the production environment, you may depend someone else (DBA) to set up all those stored procedures so that stuff works.

In contrast on sane programming languages using explicit SQL queries with "bind variables/parameters" can actually be EASIER than using SQL and doing the quoting for each variable yourself.

Such stuff used to be hard to do properly on PHP+MySQL (example - there's boneheaded stuff like register_globals, magic_quotes and also mysql_escape_string() vs mysql_real_escape_string(), good thing there's no mysql_the_actual_real_escape_string_this_time_no_kidding() ;) ) .

And many hosting sites still are stuck with the "old PHP" ways.

Re:Use a persistence library

[Archon-X]

My only issue w/ stored procedures comes from an abstraction quarrel:
Where should the logic be? The code? The DB?
What if I need to debug, what if someone else needs to debug?

I've seen way too many nasty examples of shit going awry in databases, because someone has crazy triggers or stored procedures in place without documentation..

Re:Use a persistence library

[Splab]

For PHP + *SQL, use DBO, first proper interface for databases in PHP IMO.

Where I work there is no interface to the database other than stored procedures, yes writing programs takes longer and requires one of the DBAs to make the procedure, however, we have never had a single incident of some cowboy programmer forgetting to add a where clause to an update/delete, nor some insane environment where random pageviews clobbers the databases.

Re:Use a persistence library

[will_die]

And use of procedures do nothing to prevent SQL injections besides they generally show down your queries. Views also don't help much until you disable thier abaility to be used in insert and updates.
Use parameterized SQL be in with dynamic SQL, procedures or views.

Re:Use a persistence library

[Splab]

The logic for the dataset should be in the database where it belongs.

Crazy trigger/Crazy procedure problems are the same as every where else, if it's undocumented the code is hard to maintain.

Not sure what your problem with debugging a procedure is, most databases has interfaces for tracing procedures, I actually find SolidDb procedure trace to be preferred over normal print statements in .

Re:Use a persistence library

[Splab]

That really depends on your database flavour. SolidDB which I primarily work with, it is impossible to construct dynamic queries within a procedure.

Also your claim that procedures only slow down databases is just plain wrong. Databases with procedures where the SQL is immutable will genrally run much faster than your dynamically generated versions. Philippe Bonnet and Dennis Sasha claims (their book, "Database Tuning") that as much as 9/10 of your average query time spend in the database is spend on the query optimizer, SolidDB for instance will cache all cursors within a procedure (when instructed to), enabling performance gains in some cases (in our system) of up to 3000%, moving the data up into a higher language like C is unlikely to speed up your performance because the database will be unaware of what you plan on doing with the result set and is thus unable to prefetch data and optimize the general retrieval of data.

Re:Use a persistence library

[Anonymous Coward]

Fun

Re:Use a persistence library

[will_die]

Don't know SolidDB when doing this type of stuff it is in MS-SQL Server and Oracle.
The statement to use store procedures usually comes from MS-SQL server people who read and continue to repeat information from over a decade ago when MS-SQL server treated procedures and dynamic SQL differently; unfortunatly this has spread into Oracle space. With the query optimizer under MS-SQL Server and Oracle you don't have to worry about using procedures since dynamic SQL is treated the same way, especially if you use paramterized queries.
Where you really get the slowdown in the MS-SQL Server and Oracle is that with the stored procedures being used for everything they contain alot of ISNULL and COALESCE checks which are slow in addition to causing a new query to be generated.
This is for standard CRUD statements if you have something really complex and makes multiple calls back to the database parses something then makes some more queries then yes procedures are going to be faster.

Re:Use a persistence library

[edumacator]

Note: I even admit in my profile I'm a bad web developer.

I have JFGI, but most of the stuff I've found leads me to articles I don't fully understand how to implement. I mostly code simple websites for my school and friends that have little db interaction, but I'd rather learn to do it right from the beginning, so if anyone has some links to good articles for beginners to understand how to properly secure their SQL code, I'd be happy for the help.

Re:Use a persistence library

[ArsenneLupin]

No need to use a persistency library, but there is no excuse to set up queries by concatenating string. Use wildcards instead! All modern databases support them. executeQuery("update users set score=? where id=?", 95, 113); There is no way anybody could abuse that. The only place where concatenating may be accepatble is for variable ordering: executeQuery("Select * from users order by "+column+(desc ?" desc":"")) And here you better make sure you compare column against the list of valid columns first.

Re:Use a persistence library

[weicco]

Use procedures/views where possible and never allow dynamically created queries.

There's an excellent article on dynamic queries and little bit about SQL injections here but it's Sql Server specific so I don't know if it's any good for the Slashdot crowd: http://www.sommarskog.se/dynamic_sql.html

Re:Use a persistence library

[DrXym]

Persistence is just a bad idea, it hides the real performance issues of how databases work, and limits how you can easily manipulate the data.

That assumes performance is somebody's number 1 priority. An app might use something like OpenJPA or Hibernate because code correctness, scalability, time to market or portability are more important than performance. Besides, I bet for typical database queries, the performance boost from handwriting SQL vs Hibernate (hql) / OpenJPA (jpql) generating it would be neglible. If you absolutely had to hand tune some SQL you could even slap it in a stored proc or function which is probably a good idea anyway for some actions.

If performance or a legacy database was a concern all over the place then iBatis or its ilk might be a better fit. Then you can handwrite every SQL call but at least it sits in an XML file so it doesn't pollute the application code. It's still harder to code than using a persistence API though.

Re:Use a persistence library

[andi75]

What about "The code was written and deployed when PHP3 was new and noone will give me any money for fixing it and probably blame me if anything breaks."?

I have written a lot of web stuff from 1995-2000 (first in C using Tom Boutell's cgic, then in perl, then in PHP) and yes, quite a bit is still in use today. What should I do about all those old vulnerabilities lying around (keep in mind that I mostly quit web development in 2001)?

Re:Use a persistence library

[Anonymous Coward]

first proper interface for databases

AKA "the lowest common denominator". Thanks, but I'll keep using pg_prepare/pg_execute where I can, and pg_query with pg_escape_string where I can't.

Let me know when someone comes up with one of these "generic" interfaces that can actually manage to use the database's prepared query API (rather than the usual fakeout of escaping and inserting the text into a standalone query for you, removing the query planner benefit of the database knowing what you're doing, and hoping to God that your API knows what it's doing when it escapes the text).

Re:Use a persistence library

[Anonymous Coward]

How is persistence a bad idea? Persistance libraries make it easy to manipulate the data in an object oriented way and with sensible caching most performance issues become non-issues.

Having said that, Hibernate (for example) uses parameterized statements under the hood, so sql injection is also a non-issue. Lots of problems solved :)

Re:Use a persistence library

[Michael Kristopeit]

There is simply no valid excuse for failing to use these methods to prevent SQL Injection.

you're not accepting "job creation to fix the mess"? think of the unemployed!

Re:Use a persistence library

[mgkimsal2]

PDO has been around for years, and offers standardized escaping and binding for all the major db platforms. If you're stuck with an "old PHP ways" host, they probably are still using PHP4 and have register_globals set to on - IOW, time to move to a modern host. Just like you wouldn't stick with a Java host only offering 1.3 or 1.4, it's time to vote with your wallet and move to modern hosting operations.

Re:Use a persistence library

[Anonymous Coward]

What has Cross-site scripting to do with SQL Injections?

Re:Use a persistence library

[moreati]

Sure you're aware of this, but to make to clear for everyone. Python, Perl and other languages don't require extra libraries to do parameterized queries either. In Python the pattern is

import db_module
conn = db_module.connect('user/pass@host')
curs = conn.cursor()
curs.execute('select field1, field2 from table1 where field3 = ? and field4 = ?', ('foo', 7.6))
curs.fetchall()

Exactly the same number of lines as doing it with string munging, but type safe and zero chance of sql injection.

Re:Use a persistence library

[SQLGuru]

Vendor products tend to shy away from stored procs and views because it ties them to a particular back-end (and can limit sales). Instead of spending time writing database code, they just show it all into the front-end. That doesn't mean they can't take steps to prevent SQL Injection.

Re:Use a persistence library

[Anonymous Coward]

Use addslashes on all variables and make sure you set the character set and you are done. Go to sleep.

Re:Use a persistence library

[Yaa 101]

Nah, just interpret the arguments and stop your program when it goes out of type or range, never use arguments directly.

Re:Use a persistence library

[SQLGuru]

You define it up front in the project and stick to it.

Look at the skill set of the team. If you don't have a strong database guy, your logic will probably be in the app layer.
If the database is shared between apps/services/etc., then more logic needs to be enforced there. Data integrity triggers to prevent bad data from getting into the database from any side. Access to tables going through stored procs.

If you have to debug, you work through it regardless of where it is. Just like testing anything with multiple layers (gui, app layer, remote web services, database code, etc.), you test each layer individually but using the same call as a collective test. Eventually, you will isolate the layer with the issue. Dig in deep and root out the problem.

Re:Use a persistence library

[TheSunborn]

IT's very simple. Don't use any of the mysql_* functions.

Use the PDO prepare function (http://dk2.php.net/manual/en/pdo.prepare.php) and remember newer to pass any input you got from the user directly into the string you give to prepare.

In most cases(99%) the string you give to the prepare function should really be constant and not depend on user input at all.

Re:Use a persistence library

[digitalaudiorock]

For those using php, ADOdb is a great way of doing this, and also adds a lot of great functions similar to the perl DBI: http://adodb.sourceforge.net/

Re:Use a persistence library

[lehphyro]

No, the logic belongs to the app. The database is there to store data, how we are going to use it is app-dependent.

Re:Use a persistence library

[hibiki_r]

For me, the fact that you have to keep writing XML mostly by hand is the reason I'd not move to iBatis for the parts of my app that must be hand-coded: I thought that 5 years ago we all had figured out that having to change XML configuration files along with code every time we make a significant change was a loss instead of a gain. That's why frameworks like EJB3 and Hibernate started supporting annotations: XML makes a lot of sense as a data format, but more often than not, it's used as something that is so linked to the state of the code that it is meaningless on its own: How often does anyone change iBatis XML files without changing the code it relates to at the same time?

I use the EJB3 interface on top of hibernate for the simple stuff, and switch to hand coded SQL for the reporting queries that must be hand tuned to be even remotely effective: Retrieving a report through hibernate when it has to join a dozen entities to get the data leads to madness

Re:Use a persistence library

[aldousd666]

Stored procedures are not the cure-all for everything. They are good if you have only a few ways of doing things, but it's ridiculous to write a different stored proc for every single column that you want to sort by. Its stupid to write a new stored proc for every possible way of varying the query. Yes they do guaruntee some kind of type checking and parsing compliance, but you can do that with a prepared statement as well. Dynamic SQL is a lot more flexible, especially when the number of stored procs would be combinatorial in number. You just have to be smart, and know what to do. Try converting your values to the types you want. Make your own parser if there is no other way, but for example, in the .NET world you can use ADO.NET with the typed parameters on text queries and it's every bit as safe and efficient as a stored proc. I'm not sure how well or not this translates to PHP and MySQL but I think the db module has most of the same stuff, if I recall correctly.

Re:Use a persistence library

[elnyka]

> Still upsets me how many developers are anti Stored Procedures Using stored procedures is harder than just creating the SQL query. You need to know even more about the DB. Even it's harder if you have to get the DB guy to do stuff for you. Yes you can run your own DB in development, but in the production environment, you may depend someone else (DBA) to set up all those stored procedures so that stuff works.

I've never had that problem... that is, having a problem depending on a dba to implement stored procedures at the developer's behalf. Don't know how things are elsewhere, but most of my work was with JEE+Oracle with DBAS strictly separated from developers by well-established procedures (or by strict procedures on what to put on the db when developers were the same as dbas.)

SQL statements would be tested in a development db and then converted into stored procedures. That is, code never executed anything via jdbc that was not a stored procedure. Even if it was a single SELECT COUNT(*) FROM DUAL just for kicks, that'd go into a stored procedure.

On the systest database, it was set up so that the db account used by the application could not execute anything but stored procedures. Here, developers still had the ability to create the store procedures and refine the DDL scripts that creates them. Scripts for rollbacks were created as well. Installations and rollbacks would be rehearsed until we were satisfied with them.

Come time to deployment in production, we would hand over to the DBAs the DDL scripts with instructions on how to run them (and with instructions and DDL scripts for rolling back.) They'd flip their switches, we'd flip ours and voila. Shit is running fine.

But I've also worked with places where working like that is just impossible. Either DBAs being "don't touch my db!" prima dones and developers being "I need full access!!" whores. Very few places have DBA and development teams that can work professionally in tandem.

Re:Use a persistence library

[QuoteMstr]

Except that Python's DB-API is a horrible mess. Depending on what db_module is, you might need to spell your query as:

1. 
   curs.execute('select field1, field2 from table1 where field3 = ? and field4 = ?', ('foo', 7.6))
2. 
   curs.execute('select field1, field2 from table1 where field3 = :1 and field4 = :2', ('foo', 7.6))
3. curs.execute('select field1, field2 from table1 where field3 = :field3 and field4 = :field4', {field3:'foo', field4:7.6})
4. curs.execute('select field1, field2 from table1 where field3 = %s and field4 = %s', ('foo', 7.6))
5. curs.execute('select field1, field2 from table1 where field3 = %(field3)s and field4 = %(field4)s', {field3:'foo', field4:7.6})

These aren't options that you have as a programmer, no. db_module.paramstyle tells you what format to use, and your application needs to use the one the back-end is expecting. It's a perfect example of why "can't decide? just make it an option!" is not a viable strategy.

Re:Use a persistence library

[jbezorg]

Add this.

"NOTE: This software has reached it's end of life and will no longer be supported."

If you want to tidy up one last time.

Assuming MySQL, use "mysql_real_escape_string" (PHP 4 >= 4.3.0, PHP 5) takes into account the character set of the connection.

http://us2.php.net/manual/en/function.mysql-real-escape-string.php

Then something like:
// Reverse magic_quotes_gpc/magic_quotes_sybase effects on those vars if ON.

if ( get_magic_quotes_gpc() )

{

        $product_name = stripslashes($_POST['product_name']);

        $product_description = stripslashes($_POST['product_description']);

} else {

        $product_name = $_POST['product_name'];

        $product_description = $_POST['product_description'];

}

// Make a safe query
// %s - cast the input to string
// %d - cast the input to base 10 int

$query = sprintf("INSERT INTO products (`name`, `description`, `user_id`) VALUES ('%s', '%s', '%d')",

                  mysql_real_escape_string($product_name, $link),

                  mysql_real_escape_string($product_description, $link),

                  $_POST['user_id'] );

Re:Use a persistence library

[Simon Brooke]

One should definitely use a persistence library instead of concatenating strings to help mitigate the possibilities of being victim of SQL injections. They are pretty good at it. Hibernate is a widely used one.

Speaking as someone who has used both approaches, Hibernate is a lot of overhead for, in many cases, very little gain, and having used it on a number of large projects my team has decided not to use it in future. Of course you must sanitise all values passed in from untrusted clients carefully before they are spliced into any SQL string, but there are a number of frameworks which do this which are far lighter weight than Hibernate.

Re:Use a persistence library

[Rysc]

Perl DBI is pretty good about this, AFAICT. Of course it is only generic for some values of generic...

Re:Use a persistence library

[ShannaraFan]

Putting the logic in stored procedures allows ME, the DBA, the guy with the SQL know-how, to tune the gawd-awful query that you, the pointy-clicky .NET monkey, is using to bring my server to its knees. NINE left joins again subqueries, each with a GROUP BY, then another GROUP BY applied over the query as a whole? WTF are you thinking? Fixing your code requires a new build & deployment cycle. Fixing a stored proc, I can do that with a simple DROP/CREATE script.

Yes, I'm bitter. I'm surrounded by pointy-clicky types who insist on procedural thinking when writing queries. Set theory? What's that?

Re:Use a persistence library

[cayenne8]

"but it's ridiculous to write a different stored proc for every single column that you want to sort by. Its stupid to write a new stored proc for every possible way of varying the query. "

??

You can write stored procedures that take variables, for instance..one that will change the ORDER BY clause to what you want. Also, it isn't much of a problem to overload stored procedures...same name, but behaves differently by the number or types of parameters you call it with.

Re:Use a persistence library

[Anonymous Coward]

I'm sorry, but that's wrong for most RDBMSs. Stored procedures are generally compiled making them faster than the exact same query run outside of the procedure, plus since they're compiled dynamic queries are no longer possible which cuts down on the majority of injection attacks. Plus you can grant permissions to just the SP and none of the tables which will lock things down quite nicely.

Yes you should still sanitize your inputs but SPs make this all so much easier. It also makes your design much more modular and flexible. It's really easy to tweak a SP on the fly instead having to dig into the code recompile and push it all out again.

Re:Use a persistence library

[ultranova]

Use procedures/views where possible and never allow dynamically created queries.

Unfortunately, there are situations where you have to use dynamically created queries. For example, to the best of my knowledge, queries like "SELECT item FROM table WHERE keyword IN ($1, $2, $3...)" can't be written to accept an arbitrary number of keywords without constructing the query string dynamically. Of course you can and should still use positional parameters, so it's not like this causes a vulnerability.

Using views, on the other hand, has absolutely nothing to do with protecting against injection attacks; they're simply the SQL equivalant of functions. Ditto for stored procedures. After all, SQL injection is about messing with the parsing stage of the query, rather than the dispatch or execution states.

Re:Use a persistence library

[Z_A_Commando]

I attend a major university that uses PHP and MySQL as their main server tools. The MySQL database that's provided with the hosting account is a "customized" version of MySQL 5.0 that does not allow stored procedures. The functionality is simply not there. This means that every query has to be dynamically generated (in a sense). I hate it because I'm losing serious performance and I end up with sometimes massive queries because I can't offload them to a stored procedure. In this case, my only true recourse is to use escape strings and type checking. I wouldn't be "stuck in the 'old PHP' ways" if I could help it.

Re:Use a persistence library

[smartr]

jdbc, the most basic way to get java database access, lets you do this with a PreparedStatement... The fact that I still see code that just stupidly takes a string from a client and concatenates / runs it on the server frustrates me to no end. Java code letting this shit in? -guess the developer didn't RTFM... the short manual...

Re:Use a persistence library

[MemoryDragon]

Exactly, no persistence layer will help you against sql injections per default, simply by swithing to an orm layer you are safe from them is a common myth instead you just shift the problem from sql to hql or jqpl, only parametrized queries really help you to avoid that.

Re:Use a persistence library

[CastrTroy]

It's ok to create dynamic queries as long as you aren't generating those based on user content. Doing the following (VB/Pseudocode) is perfectly fine.

sql = "SELECT item FROM table WHERE keyword IN ("
FirstValue = True
ParamNo = 1

For each Value in MyValueList
If Not FirstValue Then
sql &= ","
Else
FirstValue = False
End If

sql &= "@Param_" & i
cmd.Parameters.AddWithValue("@Param_" & i,Value)
ParamNo += 1
Next

sql &= ")"

Since there is no user input used in generating the query, you can never have an SQL inection attack, and still use dynamic queries. There are ways to do dynamic queries, without opening yourself up to attacks.

Re:Use a persistence library

[jlechem]

I was going to MOD this up as super informative but I had to pipe in myself ;)

Having worked in a small startup, a major Fortune 500, and in between companies this kind of thing is by far the best approach over the long run. The places where the DB/Code guys are separate always end up with a better product. Simply because it allows people who excel at something to really apply that benefit to what their doing. I love writing code but hate writing SQL and maintaining databases. So I tend to focus on the code and the DB stuff gets done but pretty half assed. Now people could say you should do it all equally well but in real life that never happens. Let the database go do his thing and the programmer guy do his thing. Get them talking together and your product will benefit greatly.

Also when logic is in procs, views, whatever you don't need to redeploy anything to achieve results. Simply change the database and it's done.

Re:Use a persistence library

[CastrTroy]

Well this is the entire problem. People doing stuff that they have no business doing. There's a lot of coders out there who could write a lot better SQL than many DBA's I've seen, and a lot of DBAs who can write SQL better than coders. Now it all comes down to how you want to run your company. Do you want to hire a coder, who can't write SQL, and and DBA who can write sql, and get them to communicate together to get the jobs done, or do you want to hire 1 person, who can do both coding and SQL, and they can get the job done by themselves? Oh, the other problem. If they can't code SQL, what kind of job will they do in the other programming language they are working in. Most of the coders I know who work in an environment where SQL is used, and who don't know SQL aren't very good at writing the other half of the code either.

Re:Use a persistence library

[Qzukk]

What if "item"s came from the users in the first place? Most databases don't return the strings pre-escaped for reuse in the database.

Personally, web programming is where "Hungarian Notation" style variable names shine: I have htVariableName, dbVariableName and the original inVariableName, and it's blindingly obvious when I'm using the wrongly-escaped string in the wrong place or re-escaping something I already escaped.

Re:Use a persistence library

[Anonymous Coward]

Why do people find this so hard to protect against? 99% of your field entries are alphanumeric. Just regex strip everything else out before posting to the DB. Strip out common SQL Injection strings like "--" and "//" and you are 99% of the way there. It becomes more difficult when dealing with Facebook/Gmail forms where special characters and HTML are involved. In that case just escape it / url format it / etc. The real key here is knowing what data you REALLY need. Users are going to ask for the world, but does every field really need to be rich html? And does that user REALLY need to key in a ";" or "//" or "--"?

Re:Use a persistence library

[DavidTC]

Yeah, all this SQL stuff always confuses me. Partially because I often am in the Joomla framework, which doesn't let you do parameterized queries, and, while I guess you could do stored procedures, I've never seen the need.

Instead, I simply take all input and make sure it is sane. Is it supposed to be a number? Put an (int) before assigning it out of $_POST. (Now there's a JRequest::getInt that I'm learning to use instead.) Am I putting a string from a user into a database? I use $db->getEscaped(). When I get it, unless I can think of some justifiable reason otherwise.

I understand the point of all this, as people often leave themselves open to SQL injections, but I suspect the people who do that don't even consider the possibility anyway, and this entire discussion is lost on them. I guess, in a large company, it might make sense to require all queries are parameterized(1) or stored, so morons are required to do things sanely, but smart programmers check things at the start, and don't run around with obvious malicious input to start with.

I mean, what if, instead of an SQL injection, it was some other vulnerability? Sanity checking on just the DB query risks the rest of your program having insane data. Your program presumably does do more than write input straight to a DB, even if it's just checking permissions to write to the DB.

What if they passed in '0+523' as their userid, and your program ended up checking that, yup, they were logged in as their passed userid, '523', and then later, yup, their passed userid was '0' and hence they're an admin? Sounds crazy, but not impossible in typeless languages.

Sanity check at the start, as you assign to other (typed, as far as you can) variables, then permission check those variables if needed, and then you're good to just use raw SQL for 99% of the stuff, and all your calculations will be good. Escape and possibly even pre-quote all strings that are going to a database. (Which is especially handy, as if the strings have quotes to start with, you can instead assign NULL without quotes to that variable, and have both magically work using WHERE `blah`=$variable.)

The only time you'll have to worry about escaping at SQL query time is input where you can legitimately have quotes and semicolons and stuff in it and you used that input elsewhere. Which a lot of people do, if only to display what the person entered...but that's silly. It's much better to write a 'display' function that pulls from the data, in one place, and on an input or update, you use that to queries the data you just saved so a) they can see it worked, and b) see if you truncated it or whatever. If they typed data that ended up in a database, and you want to show said data to user in confirmation, put it in the database and pull it back out, don't display their inputted data that supposedly made it to the database.

I'm sure there are circumstances where you might want the unescaped data, and fine, escape that tiny fraction at query time. But in general, just get the semicolon out of their 'userid' at the start, and stop fucking around making sure each and every query is safe if they've decided to do that.

1) Of course, idiots with automated tools can write insane queries, also, and DBAs get real pissy about that and demanded stored queries, but that's not really relevant to this discussion, which is talking about security, and not idiots who can't conceptualize that SQL servers are finite.

Re:Use a persistence library

[brainboyz]

Your sort problem can be solved by something similar to this (from the memory of a non-DBA):

SELECT blah, blah1, blah2
FROM table1
ORDER BY
CASE @sortby
WHEN 'blah' THEN blah
WHEN 'blah1' THEN blah1
WHEN 'blah2' THEN blah2
ELSE newid() //random sort!
END

Rarely have I ever needed a dynamic query with correctly written procedures. Pivot tables are the only dynamic queries in the company's database right now; even those are parameterized in stored procedures and only dynamic when the column set isn't stable.

Re:Use a persistence library

[aldousd666]

yes, technically you can do that, and I have done that before, but depending on the kind of variation you want, it's more difficult.  Also some dbms will let you parameterize where clauses, and some will let you do a limited 'case' statement on them, and still others won't at all.

  In a query builder pattern you can append nodes (which can be strongly typed to check that it's not injecting things) to be converted to query strings after you're done, but in a stored proc its a load of IF blocks or case statements that are tough to maintain and the query will have a tendency to be non-deterministic anyway.  Stored procs as security construct are ok for CRUD, but they're not actually safer than doing it the way I suggest. It's just a different, and in my opinion, more difficult to implement and maintain, way.

Re:Use a persistence library

[aldousd666]

I mean 'order by clauses' not where clauses.

Re:Use a persistence library

[Anonymous Coward]

At this rate, I'd rather roll my own. Individual calls to bindValue when we've got HASHES in our language? Inability to reuse parameter names (where x=:foo and y!=:foo)? Fuck no!

bindValues($_POST) and letting the prepared query decide what parameters it's actually going to be using, rather than dicking around with every possible combination of ways to search for something in my table? Hell yes!

Re:Use a persistence library

[larry bagina]

maybe you should use prepared statements.

Re:Use a persistence library

[Anonymous Coward]

Tell your people to hire real programmers (because real programmers know TSQL and set theory just as well as you do) and stay the hell away from my business logic, thanks. Worry about replication, administration, and index tuning like you're supposed to. (The "A" in DBA, ya know...)

In the rare case that I can't figure out how to make a specific query fly - I'll ask for your help - but rest assured that the code stays in my control.

Re:Use a persistence library

[he-sk]

I cringe at this particular example of boilerplate code, b/c I have seen it so often lately and it's such an obvious choice for refactoring. If your language of choice has string join operator, the above code can be expressed in two lines:

items = list.join(", ")
sql = "SELECT item FROM table WHERE keyword IN (" + items + ")"

Much more readable with a better chance to spot the bug in your code: What happens if list is empty?

IHMO, better yet is to put the values in a temporary table using a batch insert and then perform a normal table join. This will allow the database to at least cache the query plan. Depending on the smarts of the SQL execution engine it could apply various other optimizations: Like sort the inner table (items) and then reorder the access to the outer table. (Of course, these kind of optimizations are theoretically possible without using two tables, but the last time I checked, PostgreSQL didn't bother.)

Re:Use a persistence library

[mk_is_here]

It would be better to use the mysqli extension (bind_param) if you are in PHP4 environment. Of course, if adding extensions is not an option, sanitize with these functions is the way to go.

Re:Use a persistence library

[fusiongyro]

I wouldn't trust MySQL's stored procedure system anyway. Last time I used it, there were copious warnings about how it could segfault your server. Also, MySQL can only optimize the outermost query, so if you're doing something with nested selects (such as, using views at all) the performance will be poor.

Re:Use a persistence library

[Anonymous Coward]

Right now do it descending or ascending. If block?

Re:Use a persistence library

[theshowmecanuck]

Most of the coders I know who work in an environment where SQL is used, and who don't know SQL aren't very good at writing the other half of the code either.

This includes coders who insist Hibernate is the be all and end all of database interfaces. 'I don't need to know SQL well because I have just abstracted that whole icky database thingy.' Meanwhile you get both shit code (because they are all silver bullet use the latest technology and agile technobabble to write factory and strategy patterns everywhere when a simple 'if' statement would have worked well) and shit database performance. No I am not frustrated nor bitter at blind lemming-like stupidity. OK, well a little... (and yes, hibernate and other ORMs are good, when used in moderation).

Re:Use a persistence library

[mhelander]

You will have to loop through MyValueList anyway to put the values into sql parameters,
Your code looks cleaner but leads to two iterations over the same set.

Re:Use a persistence library

[mhelander]

Good morning.

I somehow (generously) assumed you had a list with parameter names in it, but of course you are concatenating the actual values, directly opening for just the kind of SQL injection attack that this thread is all about.

You do NOT want to put the user input values (the keywords in this case) into the SQL query text directly, since this is what opens for a SQL injection attack: If one of the keywords is "Hello' drop tblUsers;" (note the ' escape character) then pasting that directly into the SQL query would result in tblUsers being dropped.

So what you do instead is to add a parameter (named or indexed, depending on the db) to the SQL query.

That is, instead of making your select statement look like this:

SELECT item FROM table WHERE keyword IN ('Hello', 'World')

You should ALWAYS make it look something like this:

SELECT item FROM table WHERE keyword IN (@Param1, @Param2)

And then you add the parameter values (the strings "Hello" and "World" in this case) separately (as parameter objects with matching names / indexes) using whatever API you have for this in your data access methodology of preference.

So, the code you cringe at does things exactly right, whereas your "refactored" version commits the very error that TFA tries to build awareness around.

Re:Use a persistence library

[alien9]

Actually that's a real problem when you have to maintain versioned systems.

You can't track versions of stored procedures as easy as you can do it within the code. At least I'm not aware of a fair method to do this.

Re:Use a persistence library

[lehphyro]

Tell your people to hire real programmers (because real programmers know TSQL and set theory just as well as you do) and stay the hell away from my business logic, thanks. Worry about replication, administration, and index tuning like you're supposed to. (The "A" in DBA, ya know...)

In the rare case that I can't figure out how to make a specific query fly - I'll ask for your help - but rest assured that the code stays in my control.

+1 Why DBAs posts are Insightful and this one isnt? DBAs should not try to develop our apps. Leave it to the professionals.

Re:Use a persistence library

[IICV]

Just do one thing: whenever you get any sort of input that the user might have ever possibly touched, name it something like _untrusted. Once you've sanitized the contents of the variable (using whatever method's appropriate for what you're doing) only then put it into .

Re:Use a persistence library

[jbezorg]

I agree 100%, but Andi seemed like he wanted a quick out and bind_param was discussed elsewhere.

Going into that option seemed like beating a horse he just wanted dead.

Re:Use a persistence library

[Rei]

There are other arguments against stored procedures... for example, that they're not readily portable between DBs. And anyway, 99% of the time, a statement can just be written with bindings -- case closed.

What I find is that a surprising number of developers don't know that bindings exist. I've many times just happened to run into code that injection was a risk for, informed the developer, and was told, "Oh! I'll just put in a replace statement for "'" in the input.", and I had to take a lot of time to explain to them why A) that would not do the trick, and B) why adding more replace statements is not the right solution. Come on, people -- learn bindings and use them!

And it's not just SQL that's vulnerable to injection. I once coded for a free MMORPG (which shall not be named) and was appalled to see their lack of security checks when I audited their code. And they were upset with me for auditing it, as though leaving potential or known security holes in was fine (security through obscurity -- even though the client was open-source). The only one I got them to fix (with my patch, at that) took forever, and it was a *huge* security hole. On the MMORPG, if you said a weblink, it'd automatically transform into a clickable URL. They would run your web browser through a pipe, invoking the shell with whatever arguments were needed to run the browser and passing it the URL. Anyone seeing the hole yet? If your URL contained a shell substatement, that substatement would be run as well. So you could inject, say, "rm -rf ~/*". And due to the way they displayed the links, the user might never even see the malicious code in the URL. Massive, massive security hole, and it took several days for me to convince them that it needed to be fixed. They were afraid that if they changed that "working" piece of code, something might break and it might delay their next release. Delay nothing... that's a MASSIVE GAPING SECURITY HOLE, people!

I never was able to convince them to sanitize incoming packets to the client... they were insistent that TCP/IP would always protect them. I also found some scanf-style overflow holes that I couldn't get them to fix. Talk about putting expediency over safety. :P

Re:Use a persistence library

[TimothyDavis]

Another way to phrase what parent is saying is that the database is a shared resource. It only takes *one* poorly written ad-hoc query to screw performance for everyone interfacing with the database.

Another difficulty is to database schema changes. Want to change to a partitoned table? Can't really do that when the DBA has no idea how all of the client apps are interfacing with the tables using dynamic SQL. I have seen too many situations where the end result was that the DBA made the change, and waited until applications started breaking to figure out how people were (incorrectly) using the schema.

Re:Use a persistence library

[he-sk]

Mea culpa. I was only looking at the re-implementation of the list join and was under the impression that the input values had already been cleaned up. In my defense, I will point out that this proves exactly my point: His code is so unreadable (=unmaintainable), that I introduced an error when trying to clean it up. The most important part of his code appeared at the end in midst of boilerplate. I would expect at least a comment that points out that what is happening in line 11 is completely orthogonal to the surrounding code.

Here goes the second version:

sanitized_list = map(list, sanitize_func)
items = sanitized_list.join(", ")
sql = "SELECT item FROM table WHERE keyword IN (" + items + ")"

Or, if you insist on binding parameters:

placeholders = ['?' for item in list]
placeholders = placeholders.join(", ")
sql = "SELECT item FROM table WHERE keyword IN (" + placeholders + ")
execute(sql, list)

I stand by the second part of my comment: Depending on the circumstances, loading a temporary table and then doing a simple join can be a better way to achieve the same result.

PS: Since we're talking to the database eventually, the performance penalty due to iterating twice over the same data set is negligible. In any case, code readability is usually much higher on my priority list than speed.

Re:Use a persistence library

[pixelpusher220]

Using stored procedures is harder than just creating the SQL query

Exactly my point.

To me the key difference is there are 3 positions here. Application Developers (Java, .NET, etc), DBA's (key point, ''administrators' not DEV) and Database Developers.

The third position is almost never in existence. They are the bridge between Application and the DB. I don't expect my site Sys Admins to do application code, why would I expect my Database Admins to do DB code?

Unfortunately costs and money usually push the DB dev work onto the App Developers because the lack of DB quality is chosen over a higher headcount. I try to ask why not just have the DBA's do the application work and cut out even more ;-)

Why Java/.NET programmers are expected to know SQL fluently while DBA's are not expected to know the Application languages is ridiculous. App programmers can code SQL, but tuning it, designing it and implementing it in best practice ways is best left to Database people (developers not DBAs).

Re:Use a persistence library

[Digicaf]

As a software automation consultant for some major institutions, I say to you "Please, for the love of God, preach your views to the world".

I often find myself reviewing application logic written and implemented by a developer that then goes on to call db interface code (usually plain sql) also written by that same dev. 90% of the time, the interface code is HORRIFIC. I can't say that loud enough. I've seen embedded sql/plsql that would make a junior dba's head explode.

Re:Use a persistence library

[mhelander]

I agree with all your points, and I also think your code snippets do look nice (I am particularly fond of placeholders = ['?' for item in list] ), but on the other hand it presupposes a language where such "fancy" syntax is available. Given a VB-style pseudocode, it doesn't get much better than CastrTroy's code above, which is currently for unfathomable reasons modded "3 Funny".

Re:Use a persistence library

[ShannaraFan]

The code is yours to control, but the queries are not. Rest assured that I will not hesitate to kill your crap query if it is affecting the performance of my database. I only wish that were as rare as you seem to think.

Re:Use a persistence library

[CastrTroy]

I don't know what all the fuss is about. Really my code (apart from Slashdot not showing tabs) should be easily read by any programmer. I think that using a for loop to loop over my array of values is quite straighforward, and you can tell what it's doing without thinking about it. If a simple loop with 8 lines in it is too unmaintainable, then we really need some better programmers. Also, in both your queries, you still haven't added your parameter values to your command object. You'v only created the SQL statement. Creating the SQL statement can be done with an easy join statement. Adding the paramter values to the command, not so much. You could do a list comprehension, but I didn't want to get into a lot of syntactic sugar that wouldn't be supported in many languages. A for loop is probably the simplest and most readable code and should be readily understtood and maintained even by the least experienced coders.

Re:Use a persistence library

[Doctor Faustus]

The database is there to store data
The database is there to organize data. Any old file can store it.

Re:Use a persistence library

[nate nice]

You forgot to read the "hire real programmers" part. Any programmer who is hired to write code and SQL should be able to avoid disaster queries and use a query analyzer if needed. But, a DBA shouldn't touch a line of code. If anything, they should be involved in a code review. Another system at a company I worked at is our DB guy would review ALL queries that were submitted. But this creates overhead. Every company is different, if your system works for you then good for you. But I'd agree, you should hire competent programmers if their queries are that bad. One of the questions we give to prospective programmers is "what is wrong with this?" SQL. We've had some baddies come through and have had some system destroying SQL we've weeded out, and most the mistakes are simple things.

Re:Use a persistence library

[he-sk]

I think that using a for loop to loop over my array of values is quite straighforward, and you can tell what it's doing without thinking about it.

I wasn't ranting against the for-loop per se, I was irritated that you used a for-loop to join a list of (albeit generated) strings with punctuation. Every programming language has a function for that and if not it should be easy to create a general-purpose function yourself.

Also, you have to think about your code, otherwise you'll miss the action in line 11 as evidenced by yours truly. My point was to make your code more clearly communicate what it does so these errors have less chance of happening.

No one has yet commented on the bug that all three examples share (the empty list case) but that is handled just fine by my alternative (loading the list into a temporary table and joining in-database).

PS: The execute function binds the list values of the second argument to the sql string in the first argument.

Re:Use a persistence library

[mfnickster]

For PHP + *SQL, use DBO, first proper interface for databases in PHP IMO.

Could you provide a link? Are you referring to PHPDBO ?

Re:Use a persistence library

[Jaime2]

Well, my DBA has been letting a misconfigured replication publication make one of my apps slow as molasses for 5 months now. He keeps pointing at my app whenever it comes up in meetings. However, the app runs great in the stage environment (even with the same load as production) and -- this is the best part -- he still has yet to break out SQL Profiler and give anybody any data.

So, the moral of the story is still "good people create good systems". Bad people screw stuff up whether they are developers or DBAs. I hate it when good DBAs think that all power should rest with them because everybody else is stupid.

Rest assured that I will start embedding my DBMS (BerkeleyDB or similar) into my applications if you start negatively impacting my productivity. As the number of applications that use your DBMS decreases, DBA staff will be cut.

Re:Use a persistence library

[Jaime2]

Unless the dynamic SQL is all generated in one Data Access Layer library that is far more searchable than the pile of seven thousand stored procedures, most of which are no longer used by any application.

I'm not saying that stored procedure are inherently bad or dynamic SQL is inherently good. I'm saying that that badness or goodness is derived from the quality of the code, not the container it resides in. Stored procedures don't magically turn morons into geniuses. I'll also add that most DBMSs have a poor native language. Java or C# are a thousand times better than T-SQL at doing conditions, loops, error handling, and all of the other stuff that goes on in a non-trivial data access layer, with the exception of set based operations.

Re:Use a persistence library

[Jaime2]

Morons are morons. Advocating stored procedures will not fix them. As more code moves to stored procedures, you'll need to hire more database developers. There is a very limited supply of non-morons in the world. Eventually, your stored procedure writing team will be populated by the same morons that wrote the interface code that made your head explode.

Re:Use a persistence library

[jamesh]

No, the logic belongs to the app. The database is there to store data, how we are going to use it is app-dependent.

Be careful with generalizations. It's a matter of requirements not some universal law.

If it makes sense to put it in the app and you don't particularly care about performance or security (because you trust your users) then put it in the app. I would have a problem with letting an untrusted user execute direct queries against my database though - the app has to log in somehow to the database with a username and password, and it's never hard to extract the username and password, and so suddenly a malicious user has access to do pretty much anything they want. If the app can delete a single order then the user can delete all orders etc.

Some advantages of putting business logic in the database are:
1. code reuse - your windows app, linux app, web app, and mobile app can all re-use the same business logic and just have to focus on the presentation layer.
2. control - you get to control exactly what the app can do, audit it etc, because all access goes through your stored procedures
3. performance - as fast as your network is, you aren't going to beat having the code running in the database process itself
4. performance again - left to their own devices, users will construct horrible queries that will sap the very life out of your database. If they have to go through your stored procedures then they can't wreak as much havoc on your server.

But at the end of the day the requirements might be that only a few trusted users are going to use this app, all access is via the web, and they want it finished yesterday, and so suddenly none of the above advantages really matter so much...

Re:Use a persistence library

[lonecrow]

Or you could just learn to write better more flexible stored procedures.

I haven't resorted to dynamic SQL in at least 6 years and now I can't believe I ever did. And when I look at some of my old code with dynamic SQL, I can't believe how much MORE work it was.

Its not just the input parsing, its also taking advantage of permission chaining. The DB account that I use in the application only has permission to run those stored procedure it specifically. So even if the application is compromised and the intruder gets the DB account info they still can't do anything other then run those procedure with those typed variables.

IMHO Anybody using dynamic SQL in a web application should have their head examined. There is no excuse other then being to lazy to learn to do things right.

Re:Use a persistence library

[aldousd666]

What are you talking about? Tell me how you would be so flexible as to encapsulate 100 optional ways of filtering your rows, with a user driven query interface to report on your data, optionally sorting on columns in both directions, while paging it server side without writing a combinatorial number of stored procs or one with 1000 parameters.  Huh? I'm waiting.   Stored procs have a place, they're fine, and I'm not opposed to them. But they are not a religious relic.  They are just what they are, an option.  If someone can compromise your app and get at your db info, and you take refuge in the fact that at least your stored procs can only be run (which are the way your database is modified to begin with from your app,) then you have spent too much time worrying about that and too little worrying about securing your web app.  Security chaining can be considered a HOLE in security because it only checks the right to run the proc, which may have side effects galore.  What happens if you can run your proc and change yourself to be an administrator? where is your precious security chaining there?  Get over it.  Say I have a report, where I want to maybe sometimes filter based on the date, and then sort based on the last name, and then i want to limit them based on some other thing. But the next time I run it, I just want to sort descending on the date with a larger page size.  Do I write a proc with 15 parameters?  And in that proc do I do a bunch of if blocks? God help the guy who maintains your apps.

Re:Use a persistence library

[lonecrow]

You make it sound like that all those things are problems. I just wrote a custom CMS and member management systems where the main member list screen allows about a dozen optional filters (12 parameters) with SQL paging and variable sort columns all in one proc. The entire proc is only 100 lines long and not very complicated. (and it does not use any dynamic sql in the proc either)

Here is a screen shot of the filter form. http://www.lonecrow.net/images/member-list-filters.jpg

I haven't added "sort by" to the filter form yet but it's in the proc. It works like this:

declare @OrdSeq int
set @OrdSeq = 2

select top 10 * from member
ORDER BY
CASE @OrdSeq WHEN 1 THEN lastname ELSE NULL END ASC,
CASE @OrdSeq WHEN 2 THEN lastname ELSE NULL END DESC,
CASE @OrdSeq WHEN 3 THEN firstname else NULL END DESC,
CASE @OrdSeq WHEN 4 THEN firstname ELSE NULL END ASC

Sure I need to add two lines for each column I want to sort by but for most apps that I do you can easily predict a handful of columns that people want to sort by.

No procs are not religious relics but I have found them to be the best tool for the job in pretty much every circumstances.

Concerning security chaining. If you use dynamic sql then you have to grant the applications account direct access to the tables and the intruder can throw ad hoc queries against them and inject all sorts of things. None of my procs can be used to elevate permissions because they are only performing CRUD operation against user tables. And since the parameters are typed they can't be injected. The procs can't be used to do anything they were not written to do. So yes if someone got a hold of the the application account they could delete or mess up user records but they can't compromise other databases or the system itself.

But, If your saying that security chaining is only one part of a proper defense in depth strategy, then I would agree.

Seriously, everything you complained about being hard to do in a proc is not really a problem. And from a performance, security, and maintainability point of view procs beat dynamic SQL every time. If I were you I would do myself a favor and spend some time boning up on T-SQL (or whichever flavor you use). The benefits to your apps will be huge. Just thing about all those times you watched some user doing things the hard way in Excel and when you show them an easier way they say "but I know the way I am doing it now". I think that is the zone your in. Your fighting to do things the hard way.

ps. I am using MS SQL 2000. The newer version (2005, 2008) have a bunch of cool new features that make things like paging even easier.

pps. If your looking for how to do server side paging this link has a lot of strategies. They save the best for last. Scroll to the bottom for the rowCount method.
http://databases.aspfaq.com/database/how-do-i-page-through-a-recordset.html

Re:Use a persistence library

[aldousd666]

I know how to do CTEs, sorting, paging, all of that stuff. That is not the issue. The issue is there is no benefit to having this all in stored procs other than the fact that you get to lock down by execute permission only. IF that is your goal in life, then fine, do it. But there is a lot of benefit to be had by using dynamic SQL generation, so long as you are protecting against injection.  There is no need to throw everything in a proc, especially read only queries using strongly typed parameterized statements.  I generally do all update or insert code in procs, and I will do complicated queries I construct with Lambdas using LINQ. This is so much faster to develop, doesn't litter my code with SQL, and look, it's just about as secure as your way.  I don't understand what the huge deal is with everyone saying 'must stored procedure!'  I have been doing this for 15 years, I'm not an idiot, I have seen the shift in opinion from one way of doing this to the other more than once.  It's just stupid to say that you have to always do stored procs. My complexity examples were just that, examples. Maybe not the greatest examples, but I was trying to illustrate that if you have to change just one little thing, like maybe I use conditions 1 and 12, and next time I use 25,26, and 27, it's ridiculous to put that all in a proc, when i'm just doing some filtering. The reason I throw up the idea of the server side paging is because the typical solution is to draw it all back and filter client side, which i hate, but throwing that on an already complicated multiple parameter proc just makes it that much moreso.  i wasn't implying that I, you, or anyone, doesn't know HOW, I'm saying that the idea that you MUST do it in a proc is ridiculous.

Re:Use a persistence library

[lonecrow]

Right and all I am saying is that I have I am not forcing myself into proc due to some ideology, I have just found it to the best choice in pretty much every case.

Re:Use a persistence library

[Splab]

Sorry apparently my brain decided to go mushy on me, the system I was thinking of was PDO (what an unfortunate name), linky: http://www.php.net/manual/en/book.pdo.php

Re:Use a persistence library

[ultranova]

In my defense, I will point out that this proves exactly my point: His code is so unreadable (=unmaintainable), that I introduced an error when trying to clean it up.

Your defense doesn't hold water. If you don't understand what code does, don't try to clean it up; and whether or not you understood his code, yours is open to SQL injection attack and thus wrong.

Also, if you have trouble reading simple if statement in a for loop... Well. Maybe you shouldn't be writing Web apps?

Re:Use a persistence library

[Splab]

Sort of true and it is a big problem, but this doesn't go for just SPs, versioning in a database is a very very tough thing to do since some of the changes you do to a database can't be undone afterwards, especially true if you like me work on a database that isn't allowed to be taken completely down.

What we do is keep all procedures like all normal code in the SVN repository, anything deployed is in a tagged branch, it does require high amount of discipline, but again, this is why only a few people are allowed into the database with admin privileges.

Re:Use a persistence library

[moreati]

Indeed, for cross database compatibility paramstyle sucks. It uses whatever the underlying database provides, and they all do it differently just as they all have different SQL dialects. I haven't looked into it, but I wonder how say the Perl DB-API can get around this without putting an SQL parser/generator in the database driver or doing simple find/replace and instroducing sql injection vulnerabilities.

If cross database support is required, an ORM is the best approach.

Re:Use a persistence library

[lehphyro]

it's never hard to extract the username and password

You cant use security as argument, if you assume the app is so vulnerable, why not assume the DBMS is as vulnerable?

code reuse

Code reuse is a thing to be left to developers to decide what is to be reused and what is not, you have to design specifically for it and SQL is not as helpful as an object-oriented imperative language when doing it. And different environments require different logic, your mobile app will not have the same data requirements as your enterprise app.

control

You can have control without procs, simply take a look at all SQL commands the app will execute. It's not like the app will execute ANY combination of commands.

performance

Use of procs only is a performance issue actually, you cant execute batch inserts for instance. In MSSQL, we had to generate XML that had to be parsed in our procs so we could send X records per round-trip, much slower than simple batch inserts.

users will construct horrible queries

Just take a look at all commands an app will execute and tell your users what needs to be corrected.

Re:Use a persistence library

[jamesh]

You cant use security as argument, if you assume the app is so vulnerable, why not assume the DBMS is as vulnerable?

Sure I can. It's a perfectly valid reason to use stored procedures. It's the difference between safe, audited access, and unlimited access, and helps to reduce the 'attack surface'.

Code reuse is a thing to be left to developers to decide what is to be reused and what is not, you have to design specifically for it and SQL is not as helpful as an object-oriented imperative language when doing it.
And different environments require different logic, your mobile app will not have the same data requirements as your enterprise app.

Sure, it depends on the app, but 'create a new order' or 'add a new user' are potentially complicated operations and there are a lot of situations where it makes a lot of sense to keep the complexity in one place.

Stored procedures don't have to be written in SQL either. I think Oracle supports Java stored procs, and MSSQL supports external DLL's.

Creating a separate 'Business Logic' layer in the language of your choosing with a published API between the database and the presentation layer also solves this problem (and most other problems) to some extent.

And as I said first up, it depends on the app and it depends on the requirements, not some universal law of "stored procedures are always good" or "stored procedures are always bad", and making generalizations like you did with your "No, the logic belongs to the app. The database is there to store data" comment is just going to get you into trouble.

Re:Use a persistence library

[QuoteMstr]

SQL parser/generator in the database driver

You don't need a full SQL parser: you just need to keep track of quoted strings, and you can do that with a four-state DFA.

Re:Use a persistence library

[lehphyro]

It's a perfectly valid reason to use stored procedures.

Your DBMS can and will be vulnerable to unwanted access too, if not more than the app itself (I know many big companies which allow "everyone" to have direct access to production databases). Any auditing you can do at the database level can be done at the app level. Database security is not an advantage over app level security.

Sure, it depends on the app, but 'create a new order' or 'add a new user' are potentially complicated operations and there are a lot of situations where it makes a lot of sense to keep the complexity in one place.

This place surely isnt the database which must contain only data. The objectives of "new order" and "add user" are the same but the ways you get there can vary wildly.

Stored procedures don't have to be written in SQL either. I think Oracle supports Java stored procs, and MSSQL supports external DLL's.

I'm not going to tie my app to some DBMS. I want to develop and test quickly with in-memory databases like HSQLDB for example.

Creating a separate 'Business Logic' layer in the language of your choosing with a published API between the database and the presentation layer also solves this problem (and most other problems) to some extent.

Yes, that's why the database must store only data. Application logic is much more flexible handling security, testing, reusing, versioning of API and assets, coupling between applications, etc.

making generalizations like you did with your "No, the logic belongs to the app. The database is there to store data" comment is just going to get you into trouble.

A few situations when procs could be used are performance targets that the application must meet, but the developer should choose to use procs, it's not something a DBA can demand and even then the logic itself is in the app, the proc is like an optimization.

Re:Use a persistence library

[badkarmadayaccount]

Subclass String to sql_query, add inheritance from a well constructed enum, and carry on generating dynamic queries. Any unsafe queries will result in a type error. Why handle it yourself if you can use the type system?

Re:Use a persistence library

[badkarmadayaccount]

If performance sucks, the implementation is bad, if the interface sucks, the concept is bad. A really good ORM would truly make SQL pointless. I don't think that is that hard. And who said it had to be mapped to objects? LINQ ain't OO, AFAIK. Afterwords, you can be concerned with implementation. VISCs were slow for VMs only until JIT improved, and we have a long way to go.

Re:Use a persistence library

[moreati]

Ah okay, thank you for the pointer.

Re:Use a persistence library

[DrXym]

A late response (I was on vacation). I like annotations since they eliminate a lot of drudgery but I still think there are occasions when XML or a separate file is a better approach, even for persistence APIs. Say for example you missed some constraint and had to fix it, it would be far easier to fix by tweaking an XML file rather than dragging in development, qa & release engineers to rebuild the application. In general though annotations is far simpler to deal with.

A cautionary tale' OR 1=1

[kyz]

...for these modern times.

Aarghhhh

[boner]

I for one am sick and tired of these types of attack. Whoever, in their right mind thought it was a good idea to expose SQL query inputs on the Web?

Ever heard of input sanity checking? It was very popular in the say, 60's, 70's and 80's. It means you reject fields you don't expect to be there, instead of arbitrarily passing them onto the backend database. These types of attacks illustrate what is wrong with web security: developer convenience trumps common sense everytime...

Next time we see Ballmer hopping along shouting developers, maybe could he please add the words 'SECURITY BY DESIGN', please, pretty please?

SQL injection attacks are asinine because they are so prevalent, easy for the hackers AND easy to fix. We should name and shame every site, and every web-application stack that allows these attacks to take place.

nuf said.

Re:Aarghhhh

[dltaylor]

Sanity input checking was EASY when it was programmed into the 3270s.

To make a "Web Programmer", whatever kinda tool (operator) that is do some real work and provide a sane interface is like having just the one chimp pound away at the keyboard and produce Shakespeare immediately.

Re:Aarghhhh

[gmack]

The problem is that what a programmer does is largely behind the scenes and no one really know what they do anyways. The current crop of "programmers" are web designers who learned a bit of web programming to add to their skill set. They don't understand any of the implications of what they are doing and only know how to take results from a database and display it in a nice looking web page.

Re:Aarghhhh

[xtracto]

Then what needs to be done is make the libraries have this security implemented *by design*.

That is, the only possible way to get or insert data from a database should be the correct one. Security should be an enforced feature of the library (PHP, Java, etc).

It is kind of like "accessibility", it is available there (at least say, in Java and Flash) but *because* it is not compulsory, very few programmers implement it.

Re:Aarghhhh

[Anonymous Coward]

Leave them to it.

It'll keep us that actually know what we're doing in a job :).

Re:Aarghhhh

[Anonymous Coward]

Jesus Christ, you can't even talk straight. Can you think straight? Code straight?

Re:Aarghhhh

[zefciu]

But forcing programmer to do things 'right way' can limit his abilities to hack interesting solutions. Yes for sanitization as *default*. No for limiting my possibilities of writing queries the framework's author didn't think about.

Re:Aarghhhh

[vadim_t]

Please provide an example of how would it work.

For instance, in Perl I can do a query safely like this:

my $sth = $dhb->prepare("SELECT * FROM users WHERE user_id = ?");
$sth->execute($user_id);

But, I also have a bit like this:

if ( $filter_owner ) {
    $cmd .= " WHERE owner_id = ?"
    push @args, $filter_owner;
}
 
...
 
my $sth = $dbh->prepare($cmd);
$sth->execute(@args);

The second bit is also safe, but it creates a query by concatenation which could be used unsafely.

Re:Aarghhhh

[Anonymous Coward]

The invention of things such as PHP abstracted security to yet another level. Instead of dealing with buffer overflows, you're now dealing with SQL injection and XSS.
There is only one way to get security and that's by taking it seriously and not letting libraries, etc, do it for you.
The more that security is simply assumed, the more that it is ignored and the more that it becomes an issue.

Re:Aarghhhh

[The Mighty Buzzard]

Yeah and a bungee cord is easier to hold your front door closed with than a deadbolt or even a standard doorknob but you'd still have to be a fucking moron to use one.

Re:Aarghhhh

[ZeroExistenZ]

developer convenience trumps common sense everytime

You're clearly not writing software for a living...

There are a few things more important than security: time to delivery and budget.

Colour this with unrealistic expecations and you get situations like these:
"What's your estimate?" *honest assessment* "Ok, so if you work harder, you can do it in less time right? (all programmers are soo lazy.. I read that somewhere)"
"Well, it depends on what I encounter while bringing this analysis into reality..."
"Just make it work so we have something to show for by date xx-xx-xxxx"
^

Even in large coorperations with large budgets, the smaller one's usually are more idealistic but are on a tight budget.

Because alot of developers are struggling with getting the "damn thing to work", and there are so many shifts in deadlines, "security", as a seperate item, often is neglected because people are relieved they're having something "up and running".

I do agree though, that initial design and architecture should be welldefined and requires extra attention with security measures and considerations built-in, whereas many developers are running around with such a sense of urgency and pressure they just want to get to "coding thing" instead of thinking first what and how they'll code it, yet it doesn't change or improve the environment and pressure which results in these things.

Re:Aarghhhh

[Anonymous Coward]

In that case there should be a switch to turn security off, which every day sends an email to the CEO letting him know. If he's OK with that, then sure.

Or strike that - how about some giant message on login? That must be disabled using an _additional_ procedure? The way programming chimps work is, they will try to cobble something together until it works, but if there is an explicit "security on-off" switch that they MUST turn off to succeed, they might actually take the time to read what is in the attached documentation.

Re:Aarghhhh

[BBadhedgehog]

*> Ever heard of input sanity checking? It was very popular in the say, 60's, 70's and 80's.

Exactly! Guard the borders of your application, regardless of where those borders are: file, UI, webservice, whatever. Do it right at the border and only when you're sure it's not going to blow a hole in the system, then let it through.

And always use whitelists.

Re:Aarghhhh

[l0b0]

Ever heard of input sanity checking?

Yep, it's that enormously annoying thing that almost no developers get right. They filter out emails that contain + or -, names with accents, and zip codes / phone numbers for other countries. You should never reject a value from a user: If it looks wrong to you, suggest that they change it, but for f's sake put it in your DB. And don't tell me about quotes or backspaces - RTFM or Google it.

Re:Aarghhhh

[Kjella]

Then what needs to be done is make the libraries have this security implemented *by design*.

Libraries do, but they're powerless against string concatenation unless it's impossible to run raw SQL. I think the only thing you could do is deny non-paramter values at all, but it'd make everything a lot more annoying and probably have a performance impact. Like you couldn't say "WHERE is_active = 1" but had to use "WHERE is_active = ?" and bind the value.

Re:Aarghhhh

[daveime]

If it looks wrong to you, suggest that they change it, but for f's sake put it in your DB ?

Wha ??? No, no, a thousand times no !!!

First Name ? "Mr javascript:redirect_to_malicious_website"

Sure, let's put that in the DB, and hope to fucking christ whoever takes it OUT of the db and uses it on a webpage has the sense to escape it ???

If data is suspect, you never insert it into the DB. Passing on the responsibility to the output stage is no substitute for stopping the crap going in on the input stage.

Re:Aarghhhh

[ZeroExistenZ]

They don't understand any of the implications of what they are doing and only know how to take results from a database and display it in a nice looking web page.

Well, there are many like that, and in essence that's webdevelopment, right?

Consider an application where you can control the logical flow, you need to know your basic language and your GUI's behave the way you expect. Done.

Now, for being a webdeveloper you need to know HTML, XHTML, CSS, JavaScript, PHP, MySQL, MS SQL, .NET (preferably working knowledge of 3.5 and playing around with WCF/WPF), AJAX-concepts and implementation, various toolkits and libraries in place, XML, XSLT, JSON, WebServices, COM+ interaction, and need strong afinity around security concepts and be aware of injection methods, sniffing, current state of hashing algorithms, make sense of server technology and scaling (if your server is in fumes, you need to kickstart it) so that extends to IIS, Apache. If you're going more the el cheapo/opensource approach, it's mostly a box running Apache, MySQL and PHP (for which you need to subtle differences through different releases) often Mediawiki too, so you'll need to find your way around a Linux station and often are deploying and setting up such a box ad hoc as well... It adds up quite fast if you've consulted a bit and in each environment encounter different setups, architectures and approaches.

"Web development" has gotten pretty involving to get the pretty display, for which there isn't really a good methodology anymore as the web has evolved in such a way the "hypertext markup" combined with "style sheets" sortof feels dated. (that's why you have XAML, Flash, Flex, .. trying to solve the problem adding to complexity).

I do agree; webdev is pulling data and storing data while showing it in a pretty way, modify the page based on that and have a fluid user experience. However, those lasts are pretty difficult if there's a clear idea about the result and you need to depend on external parties (IE bugs, FF bugs, toolkits bugs, API frameworks with bugs, ...) to get your thing done.

I think webdevs are the gluers between all these frameworks and layers, there's maybe not much writing logic, but trying to make sense of the mess and compiling and stringing very specific technologies (legacy or hyped new) together in order to have a functional and pleasing result.

It's odd to me that there's a general looking down on webdevelopers, not just from non-techies, but also from techies whoe feel their work is "so much more significant" because "they have to think more and aren't a code monkey", yet wouldn't survive in an unstructured choatic environment where you have to think on your feet and act quick when things fall out and can't have flow in a straight line (say "I'll write function x and y today, and nobody will bother me all day while I do so") but are constantly interrupted or required to take some action, asap and efficient, while you're juggling a dosen projects, maintaining another handful and are trying to please clients. Plus ofcourse, get new projects worked out, writing analyses and following up/leading communication of 3rd parties in order "to hook up that webservice the client wanted to implement" and god knows what.

But yes, it's just displaying stuff on a page, right? I can show you complex systems (webbased stockmarket software fe.) which makes your head spin and cry in desperation (I've seen some break up and give up on the legacy mess), yet it's all "just showing data in pretty boxes" and "pulling it from a datasource" (stock market floor) and "saving it" (processing orders with business rules and automating processing of orders all within legal limitations) all to meticious specification of the clients, all with their own perculior wishes?

"But they are the lazy programmers and we don't know what the hell they are doing, but they have no concept of the implications of their work, sir.". Put

Re:Aarghhhh

[WiFiBro]

It's not such a big deal to filter all user input inorder to prevent SQL injection. It's simply a habit you need to learn and stick to.

It is more difficult to make a site that allows some people to provide content including html and script, and still prevent evil content to enter your database / pages.

And it is difficult to enforce a strict password regime because many a client have asked to remove the safety measures for convenience sake. I guess we all know examples of dumb passwords. Like 'coconuts' for the admin section of a coconut group.
 

Re:Aarghhhh

[Anonymous Coward]

but for f's sake put it in your DB. And don't tell me about quotes or backspaces - RTFM or Google it.

You should treat userdata as an ejaculate; it's like saying "ok, I want your DNA" and someone starts facialling you, because it's not what you actually ment (you wanted an oral swabbing.) that's why you wear a mask and gloves doing such operations...

So, you rather wrap your semendata in a condom or tissue when you handle the data-ejaculate, unless it's your own or someone (woman) who wants it or you're involved in a trusting and loving relationship where ejaculates have become part of a regular interation which is known to be trusted and ok.

The database doesn't want AIDS and the business logic shouldn't hocus pocus when it comes out of the database, the database must be trustable while pulling things out of it; in many cases alot of people are pulling things out of the database and trust the database to be AIDS-free. Input should be checked and validated as proper, if you cannot predict what "proper" is, invest time in r&d and datamining/statistical data-validation routines.

tl;dr: Your database is your lover who doesn't want AIDS. Because if something comes out of it, you don't want it to be spewing AIDS all over the place, you secure input, not output (otherwise you'd have women wearing vagfilters during menstruation and giving birth instead of using condoms).

Re:Aarghhhh

[Anonymous Coward]

When people are talking about unsafe query concatenation, they're talking about "WHERE owner_id=".$filter_owner;

Your query's only risk is that someone will make a typo and then none of the ? marks line up with the arguments anymore.

Re:Aarghhhh

[gmack]

The problem is not the web devs it's the managers who didn't realize they need both a programmer and a webdev.

They are very different functions. If you have only webdevs you tend towards the sort of security mess we are seeing here. If you have only programmers you end up with a site that is butt ugly and useless from a user interface perspective.

Your stock market display software is a good example of a case where the entire project will fall apart unless you have programmers who can move the data efficiently and securely and then some skilled webdevs to handle the user interface work.

Re:Aarghhhh

[ZeroExistenZ]

If you have only webdevs you tend towards the sort of security mess we are seeing here. If you have only programmers you end up with a site that is butt ugly and useless from a user interface perspective.

This is a very valid point, yet "programmer" and "webdev" is often seen as very closely related with a blurry line; in my experience a "webdev" is a programmer who's proficient with webtechnologies, but usually has a blind spot for design. (or the inability to be visually creative and create pretty interfaces, but might be brilliant with logical creativy and finding solutions). The agencies I've worked for had the design part done by "designers" who drew a few designs, shook hands on one and had a "webdev" implement it. They never touched the websites, just sliced up images when they were done.

Maybe my strong reaction was rather based on the difference of concept we have from "webdev" and "programmer", for me they're very closely related wheras you seem to see the "webdev" as a designer with a course of HTML or something alike :)

Re:Aarghhhh

[grumbel]

Whoever, in their right mind thought it was a good idea to expose SQL query inputs on the Web?

Most people are not doing it because they want to, but because the software they use allows such things to silently happen behind their back. It is a classic case of in-band signaling, you are pumping data through the same pipe as code and when the data isn't properly escaped, things break in bad and unexpected ways. To get rid of this once and for all you need to seperate the pipes, seperate the data and the code and don't allow them to be mixed. LINQ for example does that by moving the query language into your programming language, instead of having it as string-magic somewhere outside the languages syntax.

Re:Aarghhhh

[l0b0]

The counterpart to accepting any input is sanitizing any output. It's really very easy if you have centralized DB fetching (and you should).

Re:Aarghhhh

[Anonymous Coward]

no, 99% of developers will just default to always turning off the "safe mode" and then keep making the same stupid mistakes they make now. The other 1% already do correct input checking and wouldn't be impacted by the enforced rules anyway.

Re:Aarghhhh

[dkf]

It is more difficult to make a site that allows some people to provide content including html and script, and still prevent evil content to enter your database / pages.

The issue there is that you're allowing that at all (see CWE-79). The solution is to not allow general HTML/script input from non-trusted sources (i.e., they can upload new HTML with sftp, but not through a web form) and instead support some greatly restricted syntax (e.g., bbcode or wikisyntax) that is easy to convert to guaranteed fang-free content. And use a proper templating library for output of content from the database instead of hacking things.

Re:Aarghhhh

[vadim_t]

I think you didn't understand my question. The grandparent said: "That is, the only possible way to get or insert data from a database should be the correct one". That excludes any kind of "habit you need to learn and stick to", it must simply be impossible to do otherwise.

My question is, how do you actually implement a system like that? I'd like an example code of a hypothetical system that would allow me to compose an arbitrary SQL query with variable amounts of selected columns, JOIN and WHERE clauses, etc, while guaranteeing that it won't be vulnerable to SQL injection.

To make the above more challenging, my code also constructs queries that sometimes include calls to stored procedures inside them.

Re:Aarghhhh

[pintpusher]

Is there really a need for "interesting solutions" in yet another 3-layer web app? It's a serious question as I don't do this kind of work. But it seems to me that this stuff is already so well known that production sites shouldn't be looking for new interesting (and thus untested) ways of hacking together queries. Forcing programmers to do things "the right way" for established designs and purposes doesn't really seem like a problem to me, though I'm sure it takes some of the fun out of it.

Re:Aarghhhh

[gmack]

That's exactly what most of the people I've worked with or cleaned up after who call themselves "webdev" are. Designers who can pull of some HTML and make use of the odd AJAX libraries and now they know some PHP or ASP to make themselves more marketable.

The result is a ton of SQL heavy apps that manage their input validation in Javascript.

If you want truly scary you should see what happens when these guys go upscale and learn java: Java daemonized apps that use the SQL server to store their internal state and SQL tables instead of sockets.

Re:Aarghhhh

[Jeppe Salvesen]

Agreed.

Until there is legal accountability with regards to obvious security defects, there will be no real improvement. When there is, software development costs will rise noticeably and consequently economic growth will be ever so slightly slower.

Re:Aarghhhh

[cavtroop]

for all that is holy, you (along with this thought process) are the root of the problem. lemme guess: you work in sales?

*sigh*

Re:Aarghhhh

[R3d Jack]

You're clearly not writing software for a living...

There are a few things more important than security: time to delivery and budget.

Did you say that during your interview? Try doing things "right" the first time, like *always* using prepared statements. I have found that I can write code the "right" way just as fast as I can write "dirty" code, and I don't waste time debugging and refactoring. Not to mention that I never have to worry about an SQL injection attack. I'm shocked at how prevalent such attacks are, given that they are so easy to stop.

Re:Aarghhhh

[l0b0]

Heh, no. Finished MSc in CS in 2004, worked in large companies + CERN since then, and doing a PhD now. And yes, I believe it's easy and desirable to accept any input. Learn to escape and unescape (or parametrization when possible), along with the basics of Unicode, and your users will love your software even more.

Re:Aarghhhh

[Anonymous Coward]

Ever heard of input sanity checking? It was very popular in the say, 60's, 70's and 80's.

Yes, back when programming was a respectable, well-paying profession. Now it's all "Git 'R' Dun!" and if you don't do it fast enough and cheap enough, there's BILLYUNS of people in India who'll be glad to work 90-hour-weeks for next to nothing to do the programming instead while we toss your overpriced slow lazy butt on the street.

Of course, those next-to-nothing folks are under pressure to "Git 'R Dun!" too, so they don't do sanity checking, either.

Re:Aarghhhh

[Anonymous Coward]

Mod parent up!

Re:Aarghhhh

[Anonymous Coward]

The problem is that you are, basically, constructing a program (the SQL statement) using another programming language. That's always going to be dangerous because it requires that you always generate the sub-program correctly; but the language used by the sub-program is sufficiently complex (and nonstandard) that meeting that requirement is extremely difficult for nontrivial tasks.

One possible solution is to use Hibernate, which basically allows you to build the SQL parse tree by hand. For example, here is what your second example would look like:

// User.class says what data we are pulling out
Criteria c = session.createCriteria(User.class);
c.add(Restrictions.eq("user_id", userId));
 
if(filterOwner != null)
  c.add(Restrictions.eq("owner_id", filterOwner);
 
User u = (User) c.uniqueResult();

It's also worth noting that while this turns out to be little more than a SQL parse tree, you could just as easily use it to create an access plan for a non-SQL database as well, thus grafting SQL's power onto a "NoSQL" database (or "key-value store" or whatever retarded terminology is trendy today).

I'm not actually a big fan of Hibernate's specific implementation, and in practice I only use it when I would otherwise be building the statement by hand. But you asked for an example.

Re:Aarghhhh

[b4dc0d3r]

You implement a system like that by preventing people from composing an arbitrary SQL query with variable amounts of selected columns, JOIN and WHERE clauses, etc. Hopefully when you read that it will make sense.

If you are doing a different join for different circumstances, it's probably a different query and should have a dedicated stored procedure for it. If you need different where clauses, you can use the construct:

WHERE (variable is null or field == variable)

.

What you're describing is basically an adhoc query environment, which should not be allowed. That's the point. Figure out what your database needs to serve up, and have a parameterized query ready for it. Pass all variables through as parameters.

If you want to provide arbitrary queries with arbitrary joins and whatever else that is not a good idea, you're going to have to authenticate a user first to ensure they have rights to do arbitrary stuff - usually on a read-only replicated database copy. But that's not the system that was described.

So to answer your question, the system would not allow SQL string concatenation - only calling stored procs and passing variables. One of the properties of the command or connection or whatever object/class you're using could convert it to ad hoc mode, where you enable string concatenation but also use an alternate set of read-only credentials. So your data connections have a normal read/write connection and a separate read-only connection.

This is entirely possible to do in current systems if you don't want a new library, you could add a new read-only database user and make all of the SELECT queries use the read-only ID. Go crazy with ad hoc stuff because this ID can't update the data. Then only use the read/write user when you actually make a change. You can implement a data access layer that handles all of this for you, if you like. The only annoying part is back-porting the code so it properly selects which method to use, and not forgetting one.

Re:Aarghhhh

[IntlHarvester]

Take a look at a pile of resumes, and its clear there is no firm definition of "web developer". Almost every different "webdev" will have a different skillset ranging from purely front-end HTML designers/coders, to database developers with zero HTML knowledge. (And you will get some Flash guys in there as well.)

Workflow also differs quite a bit from shop to shop. Some places have a firm division between "designers" and "coders", while in others the design people are also responsible for implementation.

Re:Aarghhhh

[sbjornda]

There are a few things more important than security: time to delivery and budget.

Your project manager is only doing part of the job if s/he doesn't include a Risk calculation as part of the budget. Risk is a cost. Security holes increase the Risk.

--
.nosig

Re:Aarghhhh

[gpuk]

Ok but seriously what we are talking about here is really not that hard. It should be standard procedure to escape user input before it hits the dbms. I mean all we're talking about is casting strings to floats or integers where numbers are expected and escaping string input. In PHP you'd run the input through intval()/floatval() or mysql_real_escape_string() before you shunt it to the db - it isn't rocket science...

Re:Aarghhhh

[HeckRuler]

You know, if you're so rushed for time and can't spend a moment to learn your tools and how to make them secure, maybe you should'nt be ranting your griefs to slashdot. If you have to rant, you could do it with a boss or someone that could actually make things better. Or if you're just ranting to blow off steam, may I'd advise a punching bag. That will at least burn a few calories.

Re:Aarghhhh

[WiFiBro]

"The issue there is that you're allowing that at al"
Yep, the real world sucks.

Re:Aarghhhh

[DavidTC]

Holy crap, I just posted exactly this same thing.

Checking for malicious input at database time is idiotic and dangerous.

What, are these people only writing input to the database? They're not using it, I dunno, in any sort of calculations before that? They don't end up in a session or anything? They aren't used in calculations? It's all straight to DB?

Normal programming actually uses variables passed in to do things. Hence, programmers have to sanity check before they do things, on the variables they are going to do things with. Sanity checking at output to the DB is, um, rather late.

And if they are just going to pass them to a DB, they can also SQL escape them while they're sanity checking, then they don't have to worry about checking every damn query.

Has web programming really devolved to 'Writing user supplied values to databases?' (And presumably reading back?)

Re:Aarghhhh

[Anonymous Coward]

I didn't know Rodney McKay was a web developer.

Re:Aarghhhh

[vadim_t]

What you're describing is basically an adhoc query environment, which should not be allowed. That's the point. Figure out what your database needs to serve up, and have a parameterized query ready for it. Pass all variables through as parameters.

It's not practical in all cases. Take for instance an interface that displays a list of songs, and allows sorting by name, artist, genre, duration or size. Repeating the same query with minor variations 5 times is ugly, and leads to maintenance problems.

What you're describing is basically an adhoc query environment, which should not be allowed. That's the point. Figure out what your database needs to serve up, and have a parameterized query ready for it. Pass all variables through as parameters.

But this kind of thing isn't unusual at all. Look at any bug tracker. You can search by any field, using any condition, sorting by any column. It's not completely unrestricted, but the number of permutations is still very large.

The list of things I'd like to do wouldn't necessarily happen all at once of course. Sometimes I want to vary the columns I get, sometimes the joins, sometimes the sorting, and sometimes there's a combination of all those. But for me at least it's not an unrestricted SQL command line, it's a flexible query with many possible permutations.

If you want to provide arbitrary queries with arbitrary joins and whatever else that is not a good idea, you're going to have to authenticate a user first to ensure they have rights to do arbitrary stuff - usually on a read-only replicated database copy. But that's not the system that was described.

This is an admission of failure -- you can't do what's needed, so you opt to limit damage. But it's not going to work perfectly. Even with readonly access SQL injection allows things like creating a query with 20 joins that will DoS the server.

So to answer your question, the system would not allow SQL string concatenation - only calling stored procs and passing variables.

This is also not very practical. I have an application that consists almost entirely of stored procedure calls. But still, there are limits to that approach.

For instance, this DB can be used by two applications. One talks to humans directly, so it outputs text. The other interacts with another system, so it needs identifiers. The easiest and cleanest way to do this for me was:

my $cmd = "SELECT stats.*";
$cmd .= ",users.name" if ($want_names);
$cmd .= " FROM get_user_stats() stats";
$cmd .= " INNER JOIN users ON stats.user_id = users.user_id" if ($want_names);

If the stored proc always returned the names it'd be wasting time on an extra join that's not needed in many cases. The other alternative is to duplicate something, but duplication often leads to problems, and is ugly.

Re:Aarghhhh

[Anonymous Coward]

You're right, there is a lot to web development.

just at the guys here:

NorCalis

Clearly, they have mastered it.

Re:Aarghhhh

[Blakey Rat]

Except you save time using database stored procedures, since you can offload that work to DBAs (in most companies.) And stored procedures (mostly) solve this problem entirely... so I don't buy it, frankly.

Re:Aarghhhh

[Anonymous Coward]

> There are a few things more important than security: time to delivery and budget.

What the hell kind of projects are you working on where passing arguments to prepared statements is saving you noticeable time or budget over using plaintext?

Re:Aarghhhh

[thetoadwarrior]

This is true but that's why it's good to have designers and developers rather than relying on someone who does it all because it's hard to ensure you build a top notch website that looks fabulous and is completely secure.

Re:Aarghhhh

[nate nice]

100% accurate. I don't get raises and promotions because I put together a long presentation on how secure my work is. I get them because I can churn out code fast, on budget and on time. I of course take measures that are easy to do, etc, but I'm usually off to the next thing some PM has been bugging me about and just say "Screw it" and get my other stuff into testing so QA can do some general functional testing.

Also, the domain I'm in isn't dealing with anything a PCI system would need. But still, I've learned the game is production, regardless of what is inside the blackbox is ultra secure or not. Getting things out on time and on budget, as you said, are really the only things that count.

Re:Aarghhhh

[baileydau]

I have to agree with this 100%.

As the developer you can't tell an end user what their name is. (cue obligatory xkcd reference: http://xkcd.com/327/ ). I could just as easily change my name to <script>do_something_evil();</script>. It's NOT your call.

You can, and should enforce type checking. If it is supposed to be a date field, it should be a valid date, but in general text fields are "anything goes".

It's your job as the developer to sanitise it when it goes into the database and AGAIN when you display it to someone.

I can see an argument for storing the data in a way that is already sanitised for viewing, but that can cause more issues. eg convert the example above to: &lt;script&gt;do_something_evil();&lt;/script&gt;
But what happens when that version was really my name??

Re:Aarghhhh

[Jaime2]

You mean like the text boxes in ASP.Net that don't accept angle brackets by default? Unfortunately, this doesn't work with SQL. The most popular escape character is the single quote, which is also common in strings (like the last name O'Brien). SQL injection protection is easy if you keep one rule in mind -- never ever ever ever use string concatenation to build SQL. Use your DB library's bind variable feature.

Unfortunately, as simple as SQL injection protection is, a large percentage of people don't do it. Much of it is due to a lack of training or conflicting information. Just look at this thread -- half of the people are piping in with the correct answer (variables) and the other half are spouting solutions which don't solve the SQL Injection problem (stored procedures and persistence libraries). Both wrong solution partially solve the problem, but not completely. Persistence libraries all have a way to specify actual SQL issued and someone always abuses this feature create a SQL Injection vulnerability. Stored procedures fix some variations of SQL Injections, but if the stored procedure call doesn't properly use bind variables, then the call itself is vulnerable to SQL Injection.

Re:Aarghhhh

[shutdown -p now]

If data is suspect, you never insert it into the DB.

Oh, so you're that guy whom Ms Babcock hates.

Re:Aarghhhh

[Anonymous Coward]

To fix that you jail the pointy haired freaks when the software breaks. Not the coders but the people who ordered and passed the code on for public consumption.

Re:Aarghhhh

[Vellmont]

Ok but seriously what we are talking about here is really not that hard. It should be standard procedure to escape user input before it hits the dbms.

Why do that when there's prepared statements? Any decent language and DB driver should support it. It's a hell of a lot simpler and safer than figuring out all the various different things you might have to escape.

Re:Aarghhhh

[b4dc0d3r]

You wanted a design that meets your requirements, and when security is part of the design it's an admission of failure?

This is an admission of failure -- you can't do what's needed, so you opt to limit damage. But it's not going to work perfectly.

Did you even read what I wrote? I offered a better way of implementing what you're doing, then admitted that it did not fit your scenario, and offered one that did.

Views help reduce duplication, and they can be parameterized. Some database servers allow using the results of a stored proc, so you can refactor a lot. Your example isn't the only way to accomplish what you want. You can do a stored proc that returns vastly different results based on input. In this system, you'd pass want_names to the database, and it would return the results for you. You can bypass the different number of columns problem by returning CASE when want_names then name ELSE '' END, so even the strictest systems will work.

Take for instance an interface that displays a list of songs, and allows sorting by name, artist, genre, duration or size

You could implement the sorting client-side with javascript, that's a more efficient way to interact with a website. The client already has the data, so why send it again? This is the point where you might be tempted to insert "that's an admission of failure". Here's my indication that I'm about to make an alternate suggestion. I'll even begin a new paragraph.

But, let's do it in SQL. You can use a view to eliminate duplication. You can use Order by case to determine which column to use, no duplication needed.

Selecting with different criteria can be done by using WHERE (@variable1 IS NULL or file == @variable1) for each variable. You only need to make two copies of the code if you want to use equals or like, or you can refactor everything but the criteria into a view, or you can add another variable that lets you check (@variable1 IS NULL or file == @variable1) when a 'like' variable is unset, or (@variable1 IS NULL or file like @variable1) when set.

So there goes your duplication complaint, and limits of practicality. So let's add a basic check for mismatched escape characters on to what I wrote and ignore your complaints. I think you underestimate what can be done in SQL.

Re:Aarghhhh

[Xest]

But then how would PHP win in the performance benchmarks if it had to do things properly from the start!

Really, this is the problem, this is what it comes down to- arguments of how they impede performance and shouldn't be enforced on years, but it's bollocks.

I'm not against optional security for the few times people do need it, but it should be opt-out, not opt-in.

This is somewhat what Microsoft pulled off with the .NET philosophy, but they only did it half-arsed. The fact that code is managed by default, but if you really want to and can be sure you can handle security yourself, you can drop out to unmanaged code.

Like you say, there's no excuse for libraries not to be secure by default, performance simply isn't a good enough excuse to override security.

Re:Aarghhhh

[gpuk]

If you prefer prepared statements, PHP supports it. Personally, I prefer to avoid PDO and opt instead for finer control (and I've been doing PHP for so long that I have my own classes that I tend to re-use from project to project).

Re:Aarghhhh

[vadim_t]

You wanted a design that meets your requirements, and when security is part of the design it's an admission of failure?

Yes if it doesn't fulfill the original requirement.

The requirement was "a system that allows building arbitrary SQL queries while being immune by design to SQL injection". The readonly access is good and all from a security perspective, but the reason to bother with that is either the admission or the suspicion that SQL injection could be possible after all, and making it readonly is a way to limit damage.

But, let's do it in SQL. You can use a view to eliminate duplication. You can use Order by case [4guysfromrolla.com] to determine which column to use, no duplication needed.

That's definitely an use of CASE I hadn't thought of, thanks for that. But that's still rather limited.

The main problem here as I see it, is that what I asked for is a general solution that will work in all cases, and gave a few examples of what I might want to use it for. Now the examples of course don't use every possible feature, so by cleverly crafting some SQL you can figure out how to implement the examples I was talking of, without needing a general system. But that kind of misses the point I was trying to make.

All this started from a post of that there should be a database API that guarantees the impossibility of SQL injection by design. I asked what would this API look like. Implementing a set of stored procedures that safely accomplish a specified task completely misses the point, because all the security there hinges on the programmer knowing what she's doing while writing those stored procedures and the code that calls them, and that's not really a "API secure by design" thing.

If this makes it clearer, what I asked for is an example of what an API that would replace a db_handle.Execute(string command) function with a functionally equivalent one guaranteed to be safe, which keeps the ability to make any kind of query to the DB and construct it from pieces, like you can with the string.

Obligatory xkcd

[tangent3]

http://xkcd.com/327/

Re:Obligatory xkcd

[Inda]

Oh, oh, oh, please let it be Bobby DropTables, please, please.

Re:Obligatory xkcd

[Anonymous Coward]

Select TOP 1 as bestever from t_XKCD where id = thisone

Re:Obligatory xkcd

[gmuslera]

My toughs when reading the summary is why he didnt show them that xkcd link just as an easy introduction before going saying to them "was funny, no? Now i will show how YOUR server was rooted in the same way"

Re:Obligatory xkcd

[Anonymous Coward]

You win.

SQL is the problem, really.

[MichaelSmith]

I have been doing a bit of work with sqlite lately and I am surprised to find that the C api is basically a way to pass in strings containing SQL commands. Now even in C I could imagine an API which allows you to build up queries to do everything SQL does without using commands in text strings.

With an OO language it should be dead easy.

Re:SQL is the problem, really.

[GvG]

Sounds like a problem with sqlite, not SQL in general.

Re:SQL is the problem, really.

[MichaelSmith]

Sounds like a problem with sqlite, not SQL in general.

So why can sql code ever be injected on other platforms?

Instead of execute_command("create table X")

I want to see create_table("X")

Re:SQL is the problem, really.

[GvG]

"CREATE TABLE" is probably a bad example, if your web code needs to create a table you're doing something wrong. However, for e.g. an INSERT statement you'd typically use bind variables, something like this:

long SomeNumericValue;
char SomeStringValue[SOME_SIZE];

StatementHandle Statement = Parse("INSERT INTO TableName (Col1, Col2) VALUES (?, ?)");
BindNumericVar(Statement, 0, &SomeNumericValue); // Binds SomeNumericValue to first "?" in statement
BindStringVar(Statement, 1, SomeStringValue, SOME_SIZE); // Binds SomeStringValue to second "?" in statement
SomeNumericValue = 42; // Set values you want to insert
strcpy(SomeStringValue, "Hello, world";
Execute(Statement); // Insert new row, setting Col1 to 42 and Col2 to "Hello, world"

Re:SQL is the problem, really.

[Anonymous Coward]

SQL is the "Structured Query Language", which defines a textual interface to a relational database. If you want an interface which doesn't involve parsing on the database side, it is not going to be SQL. There are workarounds in APIs to help the programmer construct safe SQL queries, but in the end it's the SQL which creates the pitfalls that must be worked around.

Re:SQL is the problem, really.

[Michael Kristopeit]

the mother and father of the programmer created the pitfall that must be worked around...

Re:SQL is the problem, really.

[Dragonslicer]

I have been doing a bit of work with sqlite lately and I am surprised to find that the C api is basically a way to pass in strings containing SQL commands. Now even in C I could imagine an API which allows you to build up queries to do everything SQL does without using commands in text strings.

With an OO language it should be dead easy.

Why should anyone ever need to type text in order to write a program? After all, we have point-and-click programming like Visual Basic. That should be good enough for everyone.

Lemme be the first to say

[bytesex]

Use perl. Because the support both in java and php for applying regexes and preparing SQL statements has been late, convoluted and lacking.

Re:Lemme be the first to say

[Max_W]

I used Perl in 90's. Then switched over to PHP.

I remember that Perl was not too good for web programming. It was unstable in a sense that variables sometimes got strange values inexplicably.

And also the architecture of the language was not suited for web pages. When I saw PHP3, I switched to it immediately and never looked back.

PHP also has got its minuses (why I cannot create RAR or ZIP archive locked by a password on a website?), but in general it is OK, if one pays attention to what he gets from users.

I found out that casting: page.php?id=123 , i.e. $id=(int)$id; is not enough. One has also check that $id is not greater than, say, 1000, and is not less or equal zero. Because I saw that some visitors try to insert huge numbers into $id for some reason. So basically value of incoming variables should be carefully checked before processing it further. If $id cannot be more than 1000 or negative, than we just enforce it.

Re:Lemme be the first to say

[pedestrian crossing]

I remember that Perl was not too good for web programming. It was unstable in a sense that variables sometimes got strange values inexplicably.

Funny, the thing I -like- about Perl is that it is very stable in the sense that variables never get strange values inexplicably. It is a very deterministic environment, set it up and it just works as promised.

And also the architecture of the language was not suited for web pages. When I saw PHP3, I switched to it immediately and never looked back.

There are packages that make it very well suited for web pages. OK, you can't really just sprinkle code into your html like you can with php (or maybe you can, but really, why the hell would you want to do that?) but it generates web pages just fine.

I totally agree with you about sanity checking in addition to using bound parameters. Never trust input.

Re:Lemme be the first to say

[ArsenneLupin]

Although there might be lots of reasons to use Perl rather than Java (and vice-versa), security against SQL injections is not one of them. Java JDBC has been supporting wildcards (parameters) (using statement.setObject(pos,value);) since day one.

Re:Lemme be the first to say

[pooh666]

Support for binding params in PHP has been there a LONG LONG time(5 years maybe more?), it is the culture that tends not to use it. I discovered it as a kind of odd hack sort of thing, not commonly documented when it first came out. One reason is it had to be adopted, it wasn't a part of PHP to begin with. WHY PHP didn't have it to BEGIN with, that is my issue with PHP. To hold true to its credo, I would think that binding params would be seemless and transparent with no need for a developer to make a choice. That didn't happen for some reason. I found the same to be true not too long ago with Ruby, it blew my mind when I read about its MySQL interface and oh that is coming soon. This was some time ago, but I dropped it right there. Rails could go stuff itself if basics like that were not in place. So I like Perl too, but you are not accurate in your statement or focus of blame.

Re:Lemme be the first to say

[pooh666]

Yeah totally agree on that. DBI and JDBC have a great deal in common, but I still think JDBC is beautiful. With either you have to work fairly hard to be an idiot, or else just not bother to learn the whole of their specs. I don't think that should take more than a couple of weeks they just make *sense*

Re:Lemme be the first to say

[pooh666]

I remember that Perl was not too good for web programming. It was unstable in a sense that variables sometimes got strange values inexplicably.

Perhaps less(or more) drinking would help?

Re:Lemme be the first to say

[Jimmy King]

In regards to your experience with inexplicable values in Perl, it sounds like at the time you had issues with some combo of not using the strict pragma and not understanding how Perl works. If you don't fully understand what is going on, it can be confusing. If you're not using strict, it can be an extra confusing clusterfuck. I think there were a lot of tutorials and such in the mid 90s not using strict.

Re:Lemme be the first to say

[ztransform]

It was unstable in a sense that variables sometimes got strange values inexplicably.

Perl doesn't stop you from programming like a rodeo clown (for those who don't even qualify as cowboys...).

If you're going to make zealous use of globals and then use mod_perl you will get hurt.

Universities teach about something called "coupling". Every professional programmer will talk about something called "use strict". If either of these concepts are too difficult you're better off with a language that does its best to help you from yourself (but be aware Java threads are not going to stop any determined doofus from causing real pain).

Re:Lemme be the first to say

[Max_W]

I know what is strict. I used strict in VBA, visual basic for applications. I do not remember if I used strict in Perl or not, but obviously I am not the only one who deserted from Perl to PHP. Just have a look at the popularity graph.

One can implement strict in PHP too. Just check that a variable is an integer, string of a certain length, bool, etc.

Re:Lemme be the first to say

[kill-1]

Use perl, but do it because it has a plethora of usable ORMs.

Re:Lemme be the first to say

[Doctor Faustus]

Universities teach about something called "coupling".
Not really. I learned that from my fellow students.

Re:Lemme be the first to say

[shutdown -p now]

Use perl. Because the support both in java and php for applying regexes and preparing SQL statements has been late, convoluted and lacking.

If you're using regexes to prepare SQL statements, you're part of the problem.

If you're not, then please kindly explain what your comment was all about.

Comment removed

[account_deleted]

Comment removed based on user account deletion

SQL Injections SHOULD NEVER WORK

[mcalwell]

If your code is running at the correct privilege level, SQL injections should be completely irrelevant.

If your user is connecting with the correct credentials, they should only be able to see public data and their own records, nothing else.

This is implemented by using views in the database, and only allowing users rights to views, not tables.

If your website user is connecting with credentials that allow a crafted SQL query to see priveleged data, you have set everything up wrong

If you have set everything up correctly, even a successful SQL injection will only return data the user can see

Re:SQL Injections SHOULD NEVER WORK

[will_die]

Couple of problems with this.
If the attacker can still input SQL commands they can display the views,tables, procedures,etc that the account accessing the database can access. Besides most current databases allow you to use views for update and insert.
That means you need to implement a solution using multiple database credentials that way they attempt to access something the account used to access the database has the least permissions needed for the specific page and the rights of that current user. There are very few tools that understand using multiple database credentials and those that do are expensive and a pain, been a few years so maybe they are better.
So that leaves you having to write your own code and adding alot of code to handle the switching of database credentials or having different area, including duplicate pages, that handle the different database credentials.

Re:SQL Injections SHOULD NEVER WORK

[Eivind]

Uhm. No.

Well, yes, but it don't help much. True, the web-sql-user should only have access to information it needs to see. But that doesn't help you at all against the fact that a single web-user shouldn't nessecarily be able to see everything and do everything the web-server as such can see and do.

To make a concrete example, if you're making a internet-bank, then the web-frontend need to be able to see the account-balance and movements of everyone who has internet-banking, it also needs to be able to put in new transactions.

But it doesn't follow that Joe should be able to see Janes balance, or to transfer Janes balance to himself.

No web-frontend I know of create and use one sql-connection, with the apropriate rights, for every user of the web-application. I suspect that'd be very unwieldy to do anyway.

Re:SQL Injections SHOULD NEVER WORK

[mcalwell]

The user can see the table structure, perhaps the view definition, but not the data they have no rights to.

You deny select on the table, and grant access to the view. The view contains a constraint that forces the view only to return the data the connecting user is allowed to see.

I have implemented this in Postgres/PHP.

You have a group role that has read access to the public tables (eg products). The webserver runs, by default, at this user level.

When a user logs in, they reconnect to the database. They are in two groups now, the same one the webserver runs in by default, and another, which gives them access to their view

To CREATE users, you have an insert trigger in a users table to which the webserver user has INSERT rights, which then creates a new role with the required credentials.

Is it more work than a simple users table and single sign on? Yes. Is it a more sound methodology than SSO? Yes.

You wouldn't have SSO in a corporate environment, why should you have it in a web environment?

Re:SQL Injections SHOULD NEVER WORK

[mcalwell]

of database credentials or having different area, including duplicate pages, that handle the different database credentials.

Why would you have duplicate pages? You store the database connection credentials in a session, or even the database connection object. When they login, they change credentials and run with those.

Re:SQL Injections SHOULD NEVER WORK

[ArsenneLupin]

If your code is running at the correct privilege level, SQL injections should be completely irrelevant.

True, if you run your web app at the correct privilige level, there is no way an SQL injection can be used to root the machine.

But it can still be used to corrupt the application itself, which is often more valuable that the system.

Example: a gaming application that wants to store a score per user. Even if the app uses a separate DB user per game user, and even if the DB only allows the user himself to update his score, this would not be good enough, because SQL injection might allow a player to assign himself an arbitrary score of his chosing.

Re:SQL Injections SHOULD NEVER WORK

[Random Person 1372]

Is it more work than a simple users table and single sign on? Yes. Is it a more sound methodology than SSO? Yes.

Single sign-on is not the same as using the same user account for everybody...

Re:SQL Injections SHOULD NEVER WORK

[Bengie]

how does your design method apply to blog/twitter style web sites where everyone about inserts to tables and about everyone reads everyone else inserts?

Your view method may work well in your Postgres DB setup, but in the MS world, a view causes a full table scan and cannot be indexed unless your fork out $25k per socket for enterprise ed. using views sounds bad to me.

Re:SQL Injections SHOULD NEVER WORK

[Qzukk]

The idea is that instead of creating a "users" table and filling it with your users, the user is created as a database user, and their username and password is handed straight to the database during the connection process. If it connects, the user had a valid username/password. If it doesn't connect, the user didn't. If you have a million users, then your database server would need to be able to handle having a million different users each with different levels of access on different tables/rows/columns/etc.

Aside from the problem of having the database wade through a million users to decide if you have permission to perform every last query, the process of finding out whether a user has permission to perform a query in advance is usually a hairy system-level (possibly db-version-specific, new system catalog next version) query, but if you don't do that, then you get users raging at you because they spent their time filling out a form and it threw an error at them when they hit save and now it's all gone and they want that 30 minutes of their life back.

The other problem is that for most database servers, db accounts are server/cluster-wide, meaning that on any kind of shared hosting, everyone's usernames have to be distinct across people you don't even know about.

Re:SQL Injections SHOULD NEVER WORK

[Anonymous Coward]

That is assuming that each web user has their own database account, and more importantly, their own set of views; this introduces a couple of problems.

1. No SQL database engine I'm aware of supports "generic views" taking the user as a parameter in a reasonable way. If they did, you might have a case, but the
2. One db-user per web-user? If your web-application has more than a few hundred users, your DBA will kill you for this.
3. Most web app servers use connection pooling; some DB engines support "switching user" on an open connection, but there are security implications there too. Without user-switching, you screw up performance as establishing a connection is very slow.

Since you DO have to prevent SQL injections anyway, the price for this strategy is generally too high for the bonuses it brings.

Re:SQL Injections SHOULD NEVER WORK

[Cytotoxic]

Also left out of this model is modern scalability tools like connection pooling. N-tiered apps these days use a single app level user login, and each instace of the app is only using a single connection to pipe all of the various requests from all of the users to the database. You have to build your own access controls into your app/database, but you can actually manage your huge user base this way, and your apps can actually scale without setting up and tearing down thousands of connections to the SQL server every second.

Re:SQL Injections SHOULD NEVER WORK

[nacturation]

So Facebook with hundreds of millions of users would need hundreds of millions of database credentials to ensure that the data user A could select from a view is isolated from the data user B could select?

Re:SQL Injections SHOULD NEVER WORK

[mcalwell]

Why not? What's the difference between code to manage users whether in a table or in the native RDBMS?

Re:SQL Injections SHOULD NEVER WORK

[Jaime2]

What about three tier applications? For connection pooling to work properly, the middle tier should always connect to the database as the same application user. Having the middle tier connect as the actual user running the application makes connections non-shareable. So, the application user needs access to everything that any user could potentially need access to. Also, the database isn't even aware of who the request is sent on behalf of.

Database-level permission also prohibits caching in the middel tier. Since the middle tier can't be trusted to make security determinations, it would always have to re-fetch data to ensure that proper security is applied. Caching is the single most powerful performance enhancement tool.

Re:SQL Injections SHOULD NEVER WORK

[Jaime2]

In the MS world, a view does not cause a table scan. A view cannot be indexed because it doesn't physically exist (the data, not the definition). When a user selects from a view, the query optimizer rewrites the query as a select on the underlying tables then optimizes it. This allows all of the indexes that exist on the underlying table to be used when a view is queried.

Other than that, I agree with you.

Re:SQL Injections SHOULD NEVER WORK

[hesaigo999ca]

Thank you for that, I had not even though of that, but I guess a greatly designed db, could also have a view per user for most info. Being that a terabyte now is 100$ at bestbuy, it would be cheap to make your db bigger, and use more views for users, which forces the record lines to be exactly their records none else. so instead of passing in any sort of parameter like ids etc...then you use the name of the view as per user name plus table, and voila, instant security, plus
super speed for being precompiled to exactly what the user needs nothing more or less. Going to have to try that sometime...!!!

Independent Programmers' No-Win Scenario

[Anonymous Coward]

Yesterday we read about the problems of individual developers who can't get a foothold in the industry due to a shift towards accountability for software bugs. Today we read about SQL injection vulnerabilities affecting many web sites. Can anybody else see how one is the cause of the other? It is very obvious that a lot of people who should not come anywhere close to a text editor are writing public-facing code. Is there a way to remove these people from the pool other than by making programmers responsible for failing to prevent at least well known attacks? How are serious programmers, who take the time to get it right, supposed to compete when any hack can get away with abysmal code quality?

PHP security object

[DeanLearner]

I create a security object that stores $_GET and $_POST as arrays and escapes all the contained details, once this is done i blitz both $_GET and $_POST so they cant accidentally be called within the programme.

From this point to call a get variable you need to call $security->get('name');

This object also checks for dodgy content like scripts and the like and further down the line, each input is checked for proper formatting.

Im wondering though, what else should I be doing?

Re:PHP security object

[Anonymous Coward]

So you made your own magic quotes object?!

What you should do is escape the data according to how you use it.
For SQL, use prepared statements.
For html, use htmlentities.
For javascript, make your own escape function.
For shell arguments, use escapeshellarg.

Re:PHP security object

[ArwynH]

Quick answer: A lot.

Long answer:

You are mistaking escaping with sanitising. These are two very different things.

Sanitising should occur as soon as possible, before the values are used. It involves validating and optionally filtering _each_ field, so that you know the data you are getting is exactly what you are expecting it to be. This is a lot of work, which is why a lot of people skip it, hence the large number of vulnerabilities in the wild. I suggest looking into libraries like Zend_Form to help with this.

Escaping on the other hand, is done just before the variable is used. This is because different output formats have different escape sequences. E.G for SQL you would use named variables and let the engine handle the escaping for you, but for HTML you would use something like htmlspecialchars().

Both sanitising and escaping are required for a secure application.

Re:PHP security object

[Trifthen]

This is actually one of the reasons I love Pylons. It has an extensive system for variable redaction based on human input. Besides the usual things like checking email addresses for an @ sign, it will also check that the domain resolves and has an MX record. And you can bolt on as many tests to any variable as you want, with warnings up the wazu to report back to the user when they're being naughty. The best part is that these filters run before it even hits your controller and template code, so if you do it right, you'll even accidentally handle unverified or invalid user input.

I wish all frameworks were built this way.

Re:PHP security object

[Anonymous Coward]

For javascript, make your own escape function.

json_encode() ring a bell?

Re:PHP security object

[DeanLearner]

Thanks for the response. By the sounds of it I am sanitising as well (though not good at explaining it!). I've made my own framework that expects an email address for a field marked as email, a date for a date field and so on.

limit the length and content of what you accept

[bl8n8r]

This isn't a new concept, just one that people have been removed from.

If ($QUERY_STRING > $MAX_QUERY)
{
    print "*Boom* Check server for smoke!"
    exit;
}

# only allow characters 0 through 9 and upper/lowercase a-z
$Input = $QUERY_STRING;
$Input =~ s/[^0-9a-zA-Z]//g;

Re:limit the length and content of what you accept

[pedestrian crossing]

Unless you are trying to put Chris O'Connor into your database, and his name must be spelled correctly...

Re:limit the length and content of what you accept

[Dunbal]

I agree. Just like any regular program, input must be reduced to an EXPECTED set of values. Bounds checking must be performed. Anything outside that strict set of values must be rejected offhand and an error message provided. This is programming 101.

Unfortunately when HTML, PHP and SQL went "mainstream", these core programming concepts didn't get passed along. Frankly I say let "evolution" take careof/teach sloppy web developers - the smarter ones will have backups and be able to fix their problems. What really gets me is when you see large, allegedly professional sites taken down by something as silly as this.

Re:limit the length and content of what you accept

[Anonymous Coward]

So, no names with accent ? No emails with "-" or "." or "+" or whatever freaking character is legal ? In many *Western* countries " ' " is a vaild character in names/surnames (O'Neill -> http://en.wikipedia.org/wiki/O%27Neill_%28surname%29). And let's not start to mention countries with non-Latin alphabets, which are where the majority of people in the world live.

Re:limit the length and content of what you accept

[TheSunborn]

But can you give me any kind of regexp that can validate a name?

And what about this very comment field i write in now. Any valid Iso-8859-1 string* can be a valid comment, and slashdot don't have any way to reject invalid input, simply because there is no such thing in general. (Well except html, they do filter that out, but that is not because they can't store it).

* Which really mean any collection of bytes I can send to Slashdot.

Re:limit the length and content of what you accept

[bl8n8r]

> So, no names with accent ?

Of course you can. You need to expand/adapt the example. One of the biggest holes in sql queries is allowing backtics (0x60) and/or semicolons (0x3B) to be passed straight-through. If you *need* those characters, then subject input containing those characters to extra scrutiny in your code. Eliminate potential input conflicts all together if you don't intend to be working with it.

It's better to get a call about intentional failure of your code, rather than that of *unintentional* failure of your code.
Just ask Nokia*

[*] - http://www.associatedcontent.com/article/135988/nokia_website_hacked_by_drjr7.html

Re:limit the length and content of what you accept

[shutdown -p now]

I agree. Just like any regular program, input must be reduced to an EXPECTED set of values.

This is a good advice, but not when applied to this example. You do not want to restrict what users type for their name in any way (as a bunch of replies already point out, GP has missed a few obvious things... and then, what if I'm Russian or Japanese, and want to use my original alphabet?). All that needs to be done is properly escaping any unsafe chars (and for those, the set is known and well-defined); leave everything else be.

USDA likes to put SQL strings in their URLS

[RaigetheFury]

If you look for a while you'll find them. The developers replied to me with "It's perfectly fine". While it seems they do parse this information isn't that screaming "Exploit me!"

Slash Dot Virus Sequel Injected in You

[h00manist]

You can't stop reading slashdot. Full of nonsensensical arguments, but you read on, your brain oozes, your eyes are red, dry and hurt. Still, you read on, and participate in the debate. You don't recognize your odd behavior. There's a sequel reply injected into your brain. It's a slash dot sequel brain virus injection. There's no cleaning utility, you will need to reformat your brain.

Re:Slash Dot Virus Sequel Injected in You

[Anonymous Coward]

There's no cleaning utility, you will need to reformat your brain.

S.M.A.R.T. status: Critical

Re:Slash Dot Virus Sequel Injected in You

[jDeepbeep]

you will need to reformat your brain.

Does this mean I have to download the internet again?

It is a sad world we live in.

[TaggartAleslayer]

I go through this all of the time. Though I call it laziness, it is actually a combination of ignorance, indignation, and laziness.

Here is a very, very, very simple and very, very, very standard way of keeping SQL injections out. Validate everything at every level. There you go. Done.

1) Client side matters. Check input, validate it and pass it through to the application layer.
2) Application layer matters. Check variable, strictly type it, validate it and pass it through to your data layer.
3) Data layer matters. Check argument against strict type, validate it, paramaterize it, and pass it off to the database.
4) Database matters. Check paramater against strict type, validate it, and run it.

You run into problems when someone only follows any one of the steps above. You could handle it with a medium level of confidence in areas 2 and 3 (and if you're asking why not 1 and 4, go sit in the corner while the grown-ups talk), but good practice for keeping it clean is validate it at every layer. That doesn't mean every time you touch the information you have to recheck the input, but every time it moves from one core area of the platform to another or hits an area it could be compromised, you do.

As I said above, the only reason for not following 1-4 is laziness, ignorance, or indignation. SQL injections aren't hard to keep out.

We're in an age where web development IS enterprise level programming and developers need to treat it as such.

There, I just saved your organization millions of dollars. Go get a raise on my behalf or something.

Re:It is a sad world we live in.

[asdf7890]

One to add to you list if we stray beyond just SQL injection and consider other attack vectors too:

5. Output matters. Check data from the layer below, ensuring any characters that might carry unintended meaning but need to be in the data are escaped as required.

Always check the data on the way out as well as on the way in, in case something malicious got in by any means (due to a failure in steps 1 through 4, or direct database access by other means). This is implied by your supplementary text, but I think it is worth explicitly adding to the list itself.

Re:It is a sad world we live in.

[Anonymous Coward]

Your post is quite good. In fact, I'm sitting in the corner, thinking that (1) enhances the user experience, but does not by any means decrease any chance of SQL injection. Anyone with Firebug can overcome client side validation. (4) Is the essential point where the data is actually persisted. You can perform as many contortions as you want with your data, but only what is stored there will be retrieved. So it makes sense to start with making sure (4) is correct, because any manipulation on the data has the ultimate objective of being persisted in the database.

Let's hear from a grown-up then what is the role of (2) and (3), please, please. I've not yet made mysellf a strong opinion on protecting these layers, but seems to me that they are much less important than (4). And from a security perspective, (1) is completely irrelevant.

Re:It is a sad world we live in.

[TaggartAleslayer]

Client side is irrelevant as you noted (1).

The job of your application, in the case where you retrieve information from an external source that has potential for SQL Injection, is to ensure the information passed to your database (4) is as clean and well typed as possible.

Remember that we are talking about web applications of the sort that there will be user interaction, manipulation of data, and then a hand-off to a database. If you have an application that does not follow this, then you probably aren't in the SQL Injection high threat zone.

Application layer (2) and data layer (3) are concepts, but can be implemented in any language, with level of built-in support dictated by the language or framework itself. They are important in web applications to handle the tasks they are designed for. Application layer or business layer (2) takes care of general logic and application level computation such as ensuring the field "UserID" isn't actually a bunch of Viagra spam. Once your business rules are satisfied, your data layer takes the information passed to it, ensures that "UserID" indeed is a GUID as anticipated and prepares it for the database, which means paramaterizing it as the type of data you want to pass.

The database (4) is then responsible for accepting the paramaterized data and checking type to ensure what was passed is actually the type of data expected for the procedure or execution initiated.

You'll have to keep in mind, once again, that we are talking about web applications. You can remove client side (1) completely for some back-end tasks, but you can not remove the application layer (2). Something is going to be done with that data. It is going to be used in some way. You also can not remove your data layer (3) even if it is so integrated or procedurally coded that there is no separation from the application layer, it exists. You are passing the data to the database in some way. Application layer and data layer are where you have the responsibility and opportunity to scrub the data and validate it before giving it over for execution. That is what I mean when I say you can have a medium level of confidence by relying on Application and Data layers. They are your work horses.

As stated before. You can not choose any one area and consider it good enough. Just because the database (4) recieves a GUID as verified and passed by the data layer (3), it doesn't mean it's the right GUID, as that is the responsibility of the application layer (2) which leaves client side (1) to do some minimal work to help users enter the right data in the first place.

I know I've said it twice before, but we are only talking about web applications, and more specifically, those vulnerable to SQL Injection in the first place. A web service consumed directly by a series of database executions does not count, but even in that case, the general rules apply. Your application, data, and database layers are simply integrated. Just because your application logic is in a DTS and several stored procedures, doesn't mean you don't have any application logic at all.
 

Re:It is a sad world we live in.

[ztransform]

In the early days of the Internet there was a mantra along the lines of "be strict in what you send, and liberal in what you receive".

The general idea is that you should expect all kinds of input. It's up to you to ensure that data is fit for purpose inside your program.

There's a whole new generation with little respect for the history of computing and the mistakes endured: data validation, software patents and licensing, open standards and compatibility, etc. But then there's a whole generation of business men who will employ the cheapest "developer" to deliver something that appears functional and brings in money - what incentive have they for fault tolerance, for allowing the fringe that use text browsers to access their site, for preventing distribution of exploits?

Re:It is a sad world we live in.

[DarkOx]

I am with you on thee through 4, and you probably should or are doing 1 because you want to be able to help the user put the right information in fields, check onblur an give some useful feedback but spending allot of time on careful input validation at the client level with web is pretty pointless. Anyone doing something malicious does not have to use your interface at all.

Re:It is a sad world we live in.

[Anonymous Coward]

Word to live by. For example, I only fuck girls between the ages of 23-28, C cups or larger, under 130 pounds, shaved or an occasional landing strip. But on the receiving side, my ass will take any cock you throw at it.

Re:It is a sad world we live in.

[FoolishOwl]

I found this post a relief, as it makes perfect sense to me.

I am, relatively late in life, studying for certification in system administration and LAMP administration. When some of the posts in here complain about the "pointy-clicky types" who ignorantly create security holes, I was afraid I'm setting myself up to be that sort of person.

But, I get the general concept of input validation. In fact, it's such a fundamental concept that I'm astonished that professional developers don't always get it. A few years ago, I decided I should go back to school and into IT when I used a shiny, new program developed in-house by my employers, that would crash if you pressed a letter key at a menu.

In fact, I thought the point of using Perl for Web development was the excellence of its its regexp features, facilitating input validation and manipulation.

Re:It is a sad world we live in.

[TaggartAleslayer]

Application Layer is meant to handle business logic and does indeed have a purpose.

Here is a basic example pulled off the top of my head;

Business Requirement:
===
First Name field should be under 51 characters, contain at least one vowel.
Last Name field should be under 51 characters, contain at least one vowel.
Address1 field should be under 251 characters, contain at least one vowel, and not match first or last name.
===

Client side is more about UX than enforceable data validation, so it is intentionally not covered but would go here.

Once submitted by the client you have to write logic to enforce the business logic and move on to proper typing.

Application Layer:
===
firstName is the First Name field, should be a STRING under 51 characters and contain at least one vowel.
lastName is the Last Name field, should be a STRING under 51 characters and contain at least one vowel.
address1 is the Address1 field, should be a STRING under 251 characters, contain at least one vowel and not match first or last name
===

That goes on to the data layer which doesn't necessarily know or care that First Name needs to have a vowel, or that Address1 shouldn't be the same as either name.

Data Layer:
===
String variable firstName is parameter @firstName and must be varchar(50)
String variable lastName is parameter @lastName and must be varchar(50)
String variable address1 is parameter @address1 and must be varchar(250)
===

Your data layer should know something is not right if firstName comes in typed as an int, or is a string over 50 characters. It should gracefully decline the request because that's not what it's there to package and ship out.

This leads to the database.

Database:
===
@firstName is arriving as varchar(50)
@lastName is arriving as varchar(50)
@address1 is arriving as varchar(250)
===

If the above isn't met, the Database will tell the Data Layer which will tell the Application Layer and it will either inform the Client or log it away for later reflection.

If you didn't have the Application Layer validation you'd not know that Address1 can't match First Name which has to have a vowel. If you didn't have the Data Layer, you'd not be able to take the result of that computation and pass it on to the Database. It all works together and is there whether you departmentalize it or not. You just may be skipping a step that you should be doing and not realize that you're intertwining areas that should be kept separate for the reasons stated above.

SQL is not always the answer ...

[oneiros27]

You're right -- because it's SQL, which has assumptions about how it's used.

LDAP, on the other hand, you can set up to bind as the individual user, and you adjust which attributes a user is allowed to see or modify in their own entry, and which entries they can see in other entries.

So, part of the solution is using the correct data store for the situation, and SQL isn't always it. (I haven't played with any of the "NoSQL" stuff yet, but much of the behaviour with replication and and flexibility of storage seem rather similar to the LDAP implementations I've worked with.

didn't you ever watch startrek?

[Colin Smith]

learn from Scotty. always double your estimates... Especially when they ask for an honest estimate.

I'm up to a multiple of 16 now.

 

Re:didn't you ever watch startrek?

[Tablizer]

learn from Scotty. always double your estimates... Especially when they ask for an honest estimate. I'm up to a multiple of 16 now.

Until the day Scotty's job was outsourced to Beta Prime, who lowball the estimate but write crappy software.
   

Just bind!

[Angvaw]

Just bind!

I produced a video on SQL injections -

[JRHelgeson]

I wanted it to be short, easy for management to understand (even non-technical). Definitely worth watching, IMHO.

http://www.youtube.com/watch?v=jMQ2wdOmMIA

Re:I produced a video on SQL injections -

[JRHelgeson]

Little Bobby Tables:
http://xkcd.com/327/

Re:I produced a video on SQL injections -

[Anonymous Coward]

Every time I view that video, I can hear email arriving... and then when I check, I haven't got any new emails.

Does that mean the video guy is hacking me?

Vulnerable Sites

[Anonymous Coward]

Thanks for leading us to a list of vulnerable sites!

List of vulnerable sites

RCOMX is the problem

[Anonymous Coward]

A quick google finds that the problem actually exists on hundreds of websites, all driven by RCOMX - http://www.linusinc.com/

Doubtful this will be fixed anytime soon!

Ouch - This just hit home

[Anonymous Coward]

Posting anon as I wouldn't want to expose our website. We had our company website designed and hosted by an outside company. Always assumed it would be secure, I check the sites I produce for this kind of thing. Parameterising, sanity checking etc. I looked over our company website for a similar avenue of attack added the tick. SQL Error! Added " or 1=1" and bingo a whole load of pages scrunched onto the one browser page. I had an interesting conversation with the producing company...

Use access control

[helixcode123]

When setting up a system I always set up both a readwrite and readonly database user, granting only SELECT for the readonly user. Many web apps are "SELECT-only" that grab info out of a database and display it. By requiring these apps to use the readonly user adds another layer of protection should the web programmer code unsafely. Note that a hacker can still get info out of the database using injection, but can't put stuff in, or delete your data.

Re:Use access control

[Anonymous Coward]

That's a good idea. But, IMO, it's of more value to the programmer that wants to be secure than it is to the DBA who doesn't trust his programmers. Because if you have both a ro and rw account, and the application has to use both, you might still end up with the programming just using only the rw account. Whether intentionally or accidentally.

The author should be more careful...

[joshuao3]

Simply searching on google fo the tail end of the URL shows exactly which sites are vulnerable and the provider of the sites... Now the entire database of restaurants is open to attack. If the author was trying to teach their client a lesson or two (or 50)--well, good job...

Re:The author should be more careful...

[cerberusss]

You're right.

On the other hand, Google hides nothing. Just google for 'client login' or 'customer login' plus maybe some random word such as 'enterprise', or 'sales' or what have you.

I can guarantee you that in the first fifty results, you are in. Just fill in as the username:

' or 'a'!='

Huh

[tthomas48]

No one's pointing out the smaller half of the problem. If you are displaying errors you're doing most of the hackers work for them.

Exactly. And more important than other options

[Kupfernigk]

Persistence and parametrisation limit the scope of what can be done on web pages to a certain extent. But in any case all fields coming back from the browser should be sanitised before use. That includes checks for field length, both upper and lower bounds, integer overflow, and all potentially malicious syntax.

I strongly disagree too with the idea (below) that libraries should include this kind of thing. This just encourages laziness and makes it harder to check the source code of the libraries. Personally I do not like Hibernate at all because it provides far too much intermediation between the database and the application, meaning that it becomes very hard to see where the boundaries are while practically guaranteeing a loss of efficiency.

In a properly written Java (or indeed any OO) application it is easy to create classes to handle database objects and encapsulate the necessary level of security and error checking. It takes a bit more work up front but it enables you to have confidence that what you are telling the SQL server to do is safe.

No, it is not (fool)

[elnyka]

I have been doing a bit of work with sqlite lately and I am surprised to find that the C api is basically a way to pass in strings containing SQL commands. Now even in C I could imagine an API which allows you to build up queries to do everything SQL does without using commands in text strings.

So your technical indictment of SQL is based on your initial bit of work with sqllite?

And what's wrong with passing SQL command/statements via the API. That's what it's supposed to do. SQL is not just a query language, it's a control language. This is like indicting a *nix shell for letting you do "rm -rf /".

The problem is not that SQL does what it's supposed to do - you tell it to modify, it will; you tell it to delete; it will. The problem is the programmer who doesn't filter the input and allow arbitrary execution of SQL statements. The problem is the DBA who doesn't forbid execution of raw SQL statements in production. The problem is not programming defensively.

It's not fucking rocket science.

With an OO language it should be dead easy.

How naive. I'm not sure you actually know what object-orientation is, what is for and what it does and does not.

Re:No, it is not (fool)

[MichaelSmith]

This is like indicting a *nix shell for letting you do "rm -rf /".

Unix has an API for that. You are not supposed to invoke shell commands to remove files.

This lovely programmer has sold his code around

[Trailer Trash]

Took me 2 minutes with Google to find other sites that are apparently using the same crappy code with the same vulnerabilities. "inurl:" does wonders.

List of vulnerable sites

[Anonymous Coward]

Thanks. For giving us a list of targets.

I am not sure if the author meant to give away this information, but I would be a little upset.

It isn't too far off to find this exact site, knowing the images and basic layout of the site.

Work harder?

[Anonymous Coward]

Just remind the, erm, "executive" that programming is like having a baby: the esact amount of work required to deliver one is 9 months, working harder may bring the delivery earlier but that makes it a risky situation for all parties involved.

Jesus Christ! No, it is not.

[elnyka]

Sounds like a problem with sqlite, not SQL in general.

So why can sql code ever be injected on other platforms?

Instead of execute_command("create table X")

I want to see create_table("X")

In a well-defined system, you see create_table("X") in the application layer that gets translated in to the SQL statement "CREATE TABLE X" (which in the sqllite API tha'ts what happen but in a more procedural manner.).

In fact, using your example, proper usage of the sqllite API would entail having an application-specific API (be it procedural or object-oriented), with an application-specific block of code like (I'll use some c-like pseudocode to illustrate):

SomeUserStruc *user = getCurrentUser();
char * command = getCommandFromRequest();

if( ( strcmp(command,"CREATE_THINGIES_TABLE") == 0 )
&& isAuthorized(&user, "CREATE_THINGIES_TABLE") ){
errno = createThingieTable();
}

...


/* application-specific api */
int createTableThingie()
{

/* call to low-level sqllite api, which gets translated to */
/* the SQL statement "CREATE TABLE THINGIE..." which might fail */
/* in other databases like Oracle if there were other security policies */
/* in place at the db physical server, the db server itself, at the db schema */
/* or at the db table level. Capisce? */

create_table("THINGIE");
return someErrno;

}

Same principles would apply whether you are accessing a DB (be it with SQL or a low-level API). Your gripe about sqllite and SQL is ignorant at best, convoluting defensive programming and plain common sense with OO methodology.

Why would you expect SQL or the sqllite API to provide application-specific security mechanisms. That's your job as an application developer to implement application-specific security to protect resources at lower layers.

Separation of concerns. Does that ring a bell? Since you mentioned OO languages, I would have imagine that you'd realize you could create an app-specific procedural layer in C (or whatever language you are using to access the raw sqllite api), with encapsulation, information hiding and all that good stuff that preceded the arrival of OO languages and stuff...

Who? The customer.

[SmallFurryCreature]

As long as websites continue to be build for the lowest amount possible, security is going to take a backseat. I have seen more then my fair share of chinese/indian/east european hack jobs where they use code straight from "Hello world" examples. Web development has always been about "deploy now, fix later", but with the coming of outsourcing this already cut throath industry has lost all sense of quality.

Really, even the most basic security measures are not there, code deployed for thousands of dollars that is a complete mess with old versions littered around, root mysql with no password open to the world, anyting to deploy quickly to satisfy a customer whose only measure of quality is the price.

There is good money to be made in fixing these messes (because by now the site has been earning money and the fixes have to be made to ensure the revenue stream) but god it is soul destroying.

So? Use SQLAlchemy

[Krischi]

SQLAlchemy (and SQLObject for that matter) allow you to build SQL queries from primitives, if it is really necessary, with a consistent syntax across all DB modules.

And sometimes...

[ratboy666]

A client wanted me to size a migration job. VMS, running Oracle, very old. A lot of the application was running in DCL scripts. With embedded SQL.

In my report I noted the possibility of SQL injection attacks. The client chose not to bother "Yes, we know about that already". This was a government branch. Nearly made me cry.

Mostly because I am too ethical to exploit it to make money.

Just a matter of the right way of programming...

[Anonymous Coward]

I've got in touch with a few programmers during the time of my ICT period as manager and have noticed a few interesting things:

• There are not a lot of guys in the PHP world not thinking about the implications of lax security. They program their code in a few hours/days and consider it done; time is money so why care about security anyways? These guys create a hell for shared hosting; since their projects tend to be the bridge towards a new security hell of other customers on the same server. It's easy to find all the domains attached to an IP for someone with malicious purposes. A sysadmin can prevent by sandboxing/chrooting as much as possible; but cannot prevent an account (or more) to be hacked if the code is programmed bad.
• There are a few who really think security as necessity; these applications are mostly hardened towards the latest exploits. Their projects won't bring any clients in danger in a later stage; unless the server isn't properly updated; which is a duty for the sysadmin.
• Then you got some managers not thinking about security at all; not motivating their programmers enough to think about the implications of bad code, even when told about it; which does add a big deal to the equation. Such projects got a short lifespan till they get discovered and abused/defaced to some 3l1ghT s1t3.

I've been in both sides of the camp, sysadmin and programmer and have seen a few zombie corpses on the servers I've managed; ready to be hacked if not fixed.

I've read some replies in this thread where people tell everything which is considered harming should be denied. I think a better approach is sanitizing input towards a harmless string. Remove all harming characters instead of denying input is a real charm for the user and still a safety for the programmer. If special strings are used, encode. Instead of using raw SQL; use functions sanitizing input all the times.

When these actions are common, it's nothing but normal security is taken into consideration; anyone creating value in their applications should be prepared against the unknown; the Internet is a warzone and it's not getting better.

It's all a matter of routine; create the good routine and most problems will already be solved in advance.

First post!

[Anonymous Coward]

'; update comments set date='2010-02-26 05:02:00' where id=(select max(id) from comments where user_id is null and sid=1563946); /*

from the depths of the heart of another webdev

[Anonymous Coward]

My prince charming, can I marry you ? :-D

Re: Web Developer/Programmer/Designer

[Phrogman]

Here in Victoria, BC, Canada, most companies seem to want a Web Developer/Programmer who also a Web Developer and Photoshop/Flash expert. They want LAMP experts who also know .NET and Java plus probably Oracle. They are offering positions on contract and with no benefits. Its no wonder why its taking them a while to fill the positions, but it also sucks when you are looking for work and they want the Moon for qualification, but offer Uranus for compensation.

An ASP site with a querystring for id? C'mon

[shivamib]

Hmm I wonder how one could prevent this kind of mischief? Let's see... using Rails, you could:

In your Controller:

Student.find(:first, :conditions => params[:student])

In your View:

<%= h @student.html_summary %>

TFA shows an ASP site with some clear querystring id tied to a WHERE clause? Ack! You lost experience!

Rake + Capistrano

[shivamib]

As GP, I use Rails migrations, and they work for most part. Unless you're changing some data in a batch, structural changes should be able to do / undo. Rake automations helps a lot http://api.rubyonrails.org/classes/ActiveRecord/Migration.html

But for SPs I'd create a separate file for each and add to SVN, then force my migration to recreate them every time.

I also find it handy to keep your dev / test / production databases up to date. Or at least a test one (with a sample data set), so you won't screw up badly if you mess up.

Altering production databases is great responsability!