SQL Injection Attacks Increasing

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

Comments

Another web developer pain...

[Anonymous Coward]

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

Re:Another web developer pain...

[utopianfiat]

At least these news sites don't send SQL injection atta+++ELECT accountnumber,pin FROM accounts ORDER BY date DESC+++

Re:Another web developer pain...

[ksjfhdsalf]

Are you kidding I love traffic.

If you're creating a webpage, dont you want people to see it?

Re:Another web developer pain...

[professorfalcon]

It's a Slashdot injection.

How difficult is it.

[El_Muerte_TDS]

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

Re:How difficult is it.

[Goaway]

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

Re:How difficult is it.

[eggoeater]

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

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

Re:How difficult is it.

[eggoeater]

Yes, but most SQL injection attacks happen via text boxes which gets put into a String type. What the parent is suggesting is recasting them into numbers (e.g. SSN, account number, etc) or date/time.
If the user is entering a name or password, then you have to manually screen the input for bad data.

Re:How difficult is it.

[aymanh]

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

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

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

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

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

Re:How difficult is it.

[Goaway]

"Numerous" database layers, meaning nobody knows about them or has them installed. As opposed to languages like Perl, which just has one, and it does the right thing from the start. Face it, SQL injection is mainly a problem because PHP gets it all wrong by default.

Re:How difficult is it.

[Goaway]

You're missing the point. If your database interface doesn't suck completely, like PHP's default one, you don't need to manually screen for bad data in the first place.

Re:How difficult is it.

[arivanov]

This is mostly correct.

You can program incorrectly in any language. Plenty of people still sprintf into SQL statements and directly execute them with no params instead of prepare-ing them first and doing an execute with parameters. They do it with perl, python and other languages.

A large portion of the programmers out there simply do not understand and do not care about the difference in security and performance between sprintf used with static-like SQL and proper dynamic SQL with parameter replacements. In addition to that, there are libraries and servers out there that simply do not support dynamic SQL. The DBI driver for Freetb which is used to access MSSQL and Sybase is a prime example. This DBI driver has no dynamic/prepare-with-params support so most of the code written with is likely to have at least one SQL injection problem. There are other drivers which suffer from the same problem.

So while I agree with you that PHP is a language whose security is b0rken by design, you can write insecure and unstable code in any language.

Re:How difficult is it.

[CastrTroy]

Can you please tell me how to manage 600 stored procedures in a sane manner? You get this giant list of stored procedures which are not categorized in any way. There's no intellisense stuff when you're trying to use them in your code either. So, every time you need to call one, you have to search through your docs to figure out what it is. Also, I don't think that any of sql databases really handle source control very well. Sure you could have files with all your stored procedures in them, but then you have to have 2 copies of everything. One in the database, and one in the file.

Re:How difficult is it.

[aymanh]

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

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

Re:How difficult is it.

[(trb001)]

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

--trb

Re:How difficult is it.

[plague3106]

Ya, I found it easier to use named parameters on Ado.Net. Don't need to worry about Sql injection attacks when you code this way.

Re:How difficult is it.

[beavis88]

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

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

Re:How difficult is it.

[plague3106]

I agree with you, but PHP seems to encourage bad programming principals, much like everyone said VB6 did (and it does).

Re:How difficult is it.

[Anonymous Coward]

You have a single class which acts as the data access layer. One method for every stored procedure. You do not make any calls to SQL or SQL-related classes except through that one class.

Re:How difficult is it.

[Spliffster]

I couldn't agree more with you, pear DB is a really neat solution, accepted, well documented and ships with php.

Re:How difficult is it.

[plague3106]

Its easy. You write a DAL which abstracts away any sql you need to write. You then create a code generator, which not only creates a DAL class for each table, but generates the procs automatically. It works quite nicely for me.

Re:How difficult is it.

[CaptainZapp]

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

Bingo!

but then you have to have 2 copies of everything.

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

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

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

Re:How difficult is it.

[Anonymous Coward]

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

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

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

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

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

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

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

Re:How difficult is it.

[SQLGuru]

Stored procedures alone won't solve the problem. Any procedure which makes use of string building dynamic SQL (front end or back end) could just as easily fall prey to this sort of attack. The real solution is not to treat input as code but as data. Same for the RFID story from the other day. Building SQL (concatenation) is the leading cause of injection problems. Make all of your queries parameterized, not a lot of extra work, but well worth it (not only does it virtually eliminate SQL Injection problems, but you get better performance due to statement caching).

Layne

Re:How difficult is it.

[PuddyT]

Thats exactly the problem we had at a company I contracted for. I did manage to write one sp with about 15 parameters but it was a royal pain as you had to put together the SQL dynamically anyway. For cases where it is manageable I put together the dynamic string and used sp_executesql instead of exec. I prefer to use stored procedures where possible, but found in some places, like the huge parameterized queries its easier (and sometimes faster performance wise) to just put together the SQL like you said above.

Re:How difficult is it.

[hey!]

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

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

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

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

Re:How difficult is it.

[julesh]

Or you could just use stored procedures.

For most web applications, stored procedures are overkill and just too time-consuming to implement. If you have a relatively uncomplicated database (which is the case with 90% of web applications, I'd guess), then ad-hoc queries allow much faster development time. Even if you do make the effort to properly quote all your strings, and convert your numbers to numbers rather than using them directly.

Re:How difficult is it.

[eggoeater]

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

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

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

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

Re:How difficult is it.

[eggoeater]

It depends on what db you're using. Most of my work is with SQL Server and Oracle. They both compile the stored proc when you save it, any SQL injection doesn't work. i.e. you can't comment out the rest of the stored proc if it's already been compiled.

Re:How difficult is it.

[julesh]

Your examples show exactly why web developers don't tend to use prepared statements / data binding for their DB queries: it is much *easier* to do it the other way.

Frankly, this will continue until doing it the "right" way is almost as easy as doing it the quick hack way. The api should be:

bind_query('SELECT value FROM REGISTRY WHERE name = ":name"', name=$name);

I don't think this is possible in PHP, unfortunately, but the language ought to be extended to allow it. Many other languages can do it. This one is possible in PHP, though:

bind_query('SELECT value FROM REGISTRY WHERE name = ":1"', $name);

Re:How difficult is it.

[ubergenius]

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

Re:How difficult is it.

[countach]

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

Re:How difficult is it.

[eggoeater]

Just what I was going to suggest with one more:

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

Re:How difficult is it.

[(trb001)]

If you have 600 SPs, you're going to need 600 functions/classes that do the same thing. SPs are just as easy to name as functions/classes. Someone already mentioned naming conventions and source control, in addition, put all your SPs in text files and use a build script to install them. Much, much easier looking through systematically named SPs than code. We use something along the lines of __. Very easy to find SPs within seconds in Explorer.

--trb

Re:How difficult is it.

[mdboyd]

I would say that a lot of people don't grasp the idea of writing secure code. This is especially true with smaller websites paying someone as little as they can for web presence. The programmer might not know what an SQL injection is, doesn't understand the concept, or doesn't think it could ever happen to this site and so he/she doesn't write injection-proof code..

I have a friend who was writing a web application with an SQL database. I advised him to make his code injection proof. He sort of ignored me because he wasn't conscious of security. Finally, I showed him firsthand how I could exploit his code and it was sort of a wakeup call to him.

Re:How difficult is it.

[Tony Hoyle]

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

Re:How difficult is it.

[Goaway]

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

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

Re:How difficult is it.

[mdboyd]

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

Re:How difficult is it.

[Anonymous Coward]

http://pear.php.net/manual/en/package.database.db. db-common.query.php

$rs = $db->query('SELECT foo FROM bar WHERE barId = ?', array($barId));

The problem is (most) people rather spend a week fixing stuff instead of doing proper research for a day to prevent that.

Re:How difficult is it.

[00lmz]

Or at least something like this?

Call execute_query(conn, "insert into users (user_name, user_password, user_email) values (?, ?, ?)", Array(uname, pw, email))

That was using Classic ASP/VBScript with ADO. The statement is definitely not "prepared" but there's no need to escape strings. When I tried PHP I can't believe that PHP (or rather the mysql database connection layer) has no concept of bind parameters.

Re:How difficult is it.

[IMarvinTPA]

I have three copies, at least, of mine in 3 different databases. The only way I know how to make sure which one is the one that is important to me is to keep a version in a text file, edit it, and apply it to the servers in turn. (One to three development databases, One test database, and finally production.) It doesn't help that I have no direct way to change or view stored procedures on the Production system, so that Whiz-bang GUI tool is dead in the water with regards to it.

Fan of Notepad++, I just wish its Function completion feature would include functions from the included files of the php/asp page you're editing.

IMarv

Re:How difficult is it.

[Anonymous Coward]

ISNULL allows the paramaterized SP and gives you all the flexibility you need for your "dynamic" query.

create procedure...
@a = null
@b = null ...
as

select x,y,z
from someTable
where a = isnull(@a,a)
and b = isnull(@b,b)
and c = isnull(@c,c) ...

no need for dynamic sql here.

Re:How difficult is it.

[InsertCleverUsername]

IMHO stored procedures aren't overkill, they're just good coding. I wouldn't do anything but the most quick-n-dirty demo using inline SQL. If you don't like coding them, the solution is simple: CodeSmith (or one of dozens of other great code generation tools. Productivity is all about using the right tools for the job.

Re:How difficult is it.

[Mr. Shiny And New]

You can't alter the code of the stored proc, but you can (in Java, anyway) alter the CALL to the stored proc. This is trickier but it means you can still run arbitrary code on the DB.

Re:How difficult is it.

[andrewman327]

As Monday's story about RFID tags also indicated, developers need to do a better job of checking input. Assume that every part of your program will have an error and act accordingly. Computer science is often Murphy's Law at work.

Re:How difficult is it.

[Anonymous Coward]

YES! Bind variables are a lot more performant than string concatanation and they make SQL injection attacks irrelevant. It's a win/win situation!

Re:How difficult is it.

[Anonymous Coward]

Anyone that asks not to be asked about something is really dying to be asked, so why are you using sybase and not oracle?

Re:How difficult is it.

[SQLGuru]

My example was for dynamic SQL.

function CheckPassword_or_InjectMe( @id varhcar(200), @password_or_injection varchar(200) ) returning number(1) is
      @sql_statement varchar(4000) := '';
      @password_checked number(1) := 0;
begin
      @sql_statement := 'select 1 from user_list where id = ''' + @id + ''' and password = ''' + @password_or_injection + ''''; .....etc.

      return @password_checked;
end;

Not a real world example, but illustrates how SQL Injection is not a problem of a specific layer but of a specific technique: string building to make SQL. Change it to parameterized SQL and the problem goes away. Of course, in this case, direct SQL would be much more efficient.

Layne

Re:How difficult is it.

[Anonymous Coward]

"Why not write lightning-fast code in C instead? If you're trading speed for convenience by using a high-level language, why wouldn't you want to use something that is even more convenient?"

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

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

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

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

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

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

Re:How difficult is it.

[jsight]

And when January 1, 2050 rolls around, everyone will wonder why their query returns everything in the db. :-)

Re:How difficult is it.

[Goaway]

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

It is both easier, and much more secure.

Re:How difficult is it.

[(trb001)]

The problem with including everything and OR'ing statements together is that when you have 30 parameters and a well normalized database, that means joining a large number of tables. If I don't care what the field value is in tableXYZ, which has 1,000,000 rows and the column I'm checking isn't indexed, then I shouldn't include a join to it. Obvious statements:

1) Reduce the number of tables. Not possible, we have a lot of data and have normalized our table structure. Even with a normalized structure, we have millions of records in some tables.

2) Index everything. Also, not possible. We're optimized for inserting, not querying, so that stuff coming in is processed fast. Indexing would also add more overhead to disk storage and memory.

3) Optimize for querying. We're looking at this approach, sorta the 'datamart' vs 'datawarehouse' approach. It would result in a delay between when something comes in and when it could be retrieved, and so far that hasn't been acceptable.

--trb

Re:How difficult is it.

[misleb]

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

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

-matthew

Re:How difficult is it.

[SatanicPuppy]

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

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

Re:How difficult is it.

[Anonymous Coward]

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

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

Re:How difficult is it.

[Aceticon]

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

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

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

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

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

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

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

Re:How difficult is it.

[bmalia]

Java has similar functionality in its PreparedStatement class.

Re:How difficult is it.

[misleb]

God, that is ugly. 4 lines just to prepare and excecute a simple INSERT query with a new line for every variable passed to it? No wonder the typical PHP app is full of security holes. Doing things the Right Way is so much work. Shouldn't a database abstraction make things easier?

The equivielent in Rails:

Registry.create(:name => name, :value => value)

I'm glad I gave up PHP for lent.

-matthew

-matthew

Re:How difficult is it.

[ehrichweiss]

I was about to make a point along those same lines though I don't know if I'd say it's the programmers or SQL in this case. I mean by now SQL should have had a function that could sanitize data automagically; set a flag that data for such-and-such field is possibly going to be user input and then let it do the work for you. I've done some coding creating my own database software that could do all that sql, postgre, etc. are capable of and it never presented such problems no matter how hard I tried but I thought about the security first then worried about efficiency and while I'm sure that mine wouldn't handle large loads at this point, it also wouldn't compromise my system.

Re:How difficult is it.

[KIFulgore]

That actually happened to me once. I was *just* learning SQL and walked away from my computer for a few minutes and, upon returning, every row for every field in every table simply had the string "poop". A coworker, a veteran SQL programmer, laughed and said "here's a backup of your stuff."

Thankfully I was designing something simple and only for an intranet, but it was a wakeup call. Scary, really. I would never try to program SQL for a commercial website w/o reading up seriouly on injection attacks and binding and parameterizing.

Re:How difficult is it.

[ftsf]

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

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

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

Re:How difficult is it.

[Anonymous Coward]

You can't say (not familiar with Sybase) in a stored proc?

For example:
IF var1 IS NOT NULL THEN var1=' and field1='||var1;
sql_string = 'select * from tab1 where 1=1 '||var1||var2...;
execute immediate sql_string;

Oracle could do this with "decodes"

Re:How difficult is it.

[JoeCoder7]

Forgive me if someone has posted something similar already, but below is the code I sometimes user for sanitizing user input for PHP/MySQL.  It should  allow a lazy programmer to do things like $_POST = sanitize($_POST) and everything will be taken care of recursively.  Constructive criticism is welcome.

// Sanitize form input for database input as well as any html tags.  Recursively processes arrays.
// IMPORTANT!!! You must already be connected to a mysql database for this function to work.
function sanitize($text, $clean_html=false)
{    if (is_array($text))
    {    foreach ($text as $key=> $value)
            $text[$key] = sanitize($text[$key], $clean_html);
    }else
    {    if (get_magic_quotes_gpc())            // Manually remove any
            $text = stripslashes($text);        // escape characters
        if ($clean_html)
            $text = htmlentities($text, ENT_QUOTES);// Replace HTML characters with sanitized versions
        $text = mysql_real_escape_string($text);    // Clean up anything else left over.
    }
    return $text;
}

Re:How difficult is it.

[greenrd]

The problem with including everything and OR'ing statements together is that when you have 30 parameters and a well normalized database, that means joining a large number of tables.

But isn't the whole point of using a RDBMS, as opposed to something simpler like Berkely DB, that it can optimise your queries? If it's incapable of optimising queries, why are you using it?

Re:How difficult is it.

[arodland]

Forget escaping... never make user input a part of your SQL. There's never any reason for it. Hell, even PHP supports prepared statements and binding (never mind the fact that it took them over a decade to get there).

Re:How difficult is it.

[ttfkam]

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

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

As for your comment:

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

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

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

It's not.

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

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

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

Re:How difficult is it.

[ednopantz]

Security: You can grant privs on the sproc and not on the underlying table.

Testability: You can test your sproc separate from your application logic.

DB roundtrips: Need to update ten tables? Do it in a sproc and call it in 1 step as opposed to 10 steps.

Re:How difficult is it.

[Skreems]

Even using sprocs, a single case where you take a variable straight from user input to sproc input without checking it for injection characters gives a hacker enough access to retrieve the entire contents of your database. I've seen it. Scary stuff.

Re:How difficult is it.

[masklinn]

More like PDO is only available out of the box since PHP5, and the old methods have neither been deprecated nor been removed...

Re:How difficult is it.

[Richard_J_N]

PHP protects you by default from any SQL injection attack by using magic_quotes.

You can write things like this:
  $sql="SELECT value FROM REGISTRY WHERE name='$name'";
perfectly safely.

Even better, it saves you having to bother with string escaping, and if you make a mistake, it is guaranteed to fail safe. The only downside is that you must remember to use stripslashes() if your user-data does NOT go via the database; however the worst that can happen here is some harmless user-facing ugliness like this:
  Hello again, Mr O\'Neil
Which is ugly to read, but will never create an attack risk.

You do still have to be careful if you data uses the DB *twice*, eg:
      User Input ->PHP -> Database -> PHP(*) -> Database -> PHP -> User output
In this case, the PHP script marked (*) must re-do the addslashes().

Re:How difficult is it.

[orthogonal]

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

I've got an even better idea: your front-end shouldn't be synthesizing SQL statements. Pass parameters to a middle-tier business objects in a language that allows for strict typing. Or call database stored procedures. Or use an Object-Relational Mapper (yeah, used to be these sucked, but Hibernate is actually pretty good)..

But whatever you do, your front-end (and except in exceptional cases your middle-tier) shouldn't be catenating strings of SQL statements. Yes, doing it that way may make your development cycle faster, but in the long run it's a disservice to your clients and it'll destroy your reputation.

Re:How difficult is it.

[Ender Ryan]

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

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

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

PHP - training wheels without the bike indeed.

Re:How difficult is it.

[skroll82]

Exactly. You would never use string building in a stored procedure, as it just doesn't make sense (nor does it lead to good looking code). Now I don't have as much experience with MySQL as I do with MS SQL Server 2000, but I can tell you that with stored procedures, using the sproc makes a nice 'blackbox' type procedure. You simply call the sproc, and when using parameters, even if the user does anything to jump over your interface-level protection, the server will not have anything to do with improper values.

Re:How difficult is it.

[eddy]

>perl has been the only language I've decided to entirely skip after realizing how bad it really was and the fact it was designed solely to appease geeks that wanted to repell the opposite sex.

I once made that general point here, and got the "5, Insightful" (or whatever) comment that "Perl owned me".

Re:How difficult is it.

[Firehed]

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

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

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

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

Re:How difficult is it.

[Goaway]

That's how all language except PHP (or at least PHP's default interface) work, right now, and have worked for years and years.

Re:How difficult is it.

[kill-1]

I basically agree with your post, but your rant on Perl somehow contradicts your previous statements. I've been programming for 15 years in C, C++, Java, Perl, PHP and Javascript and I find it's no problem to write completely readable and maintainable Perl code. The prejudice against Perl being a write-only language or resembling line noise is stupid no matter how often it is repeated. Perl like any other scripting language is used by a lot of novice programmers and novices can write bad code in any language. I still think Perl is the best choice for small to medium web projects. The comprehensiveness of the CPAN library is unparalleled and with mod_perl you get real integration with the Apache server.

Re:How difficult is it.

[Goaway]

No, the problem is that PHP puts an API that is insecure by design right in front of developers, and then hides the ones that actually work like they're supposed to way out of sight. Meaning hardly anybody ever sees past what is right in front of them, and that works. It's insecure, but they don't know that, and they have no reason to distrust the language and tutorials they have been given.

The only way to get PHP secure is to rip out the mysql functions completely, and offer no way to enable them again. Force people to go out and find the real database interfaces.

Not going to happen, though.

Re:How difficult is it.

[Proteus]

Frankly, this will continue until doing it the "right" way is almost as easy as doing it the quick hack way

I'm not sure about PHP, but the interface in the GP seems to be very close to Perl's DBI. If that's the case, then the right way can be (for most common, simple cases):

my $sth = $dbh->prepare('SELECT value FROM REGISTRY WHERE name=?');
$sth->execute($name);

The question mark is converted to a binding placeholder, and the value is properly escaped when passed during the execute phase. There are complex cases where one needs to provide hints about data types (e.g. where it won't be clear to DBI how to pass the bound value) -- then you fall back to more complex syntax.

Re:How difficult is it.

[Anonymous Coward]

In PHP 5.1, you could use PDO's prepared statements to do the same with other databases, I believe.

Re:How difficult is it.

[jZnat]

Or how about this way?

# ($db instanceof PDO)
$query = $db->prepare('select foo from bar where baz = ? and biz = ?');
$query->execute(array('some value', "another ' or 1 --"));

You can even use the placeholders like ":baz" and ":biz" instead of the question marks so that you can make it less confusing (or streamline it with forms via $_GET/$_POST, although you'd have to prepend all the GP keys with a colon first). PDO makes it that much easier, and that's not even a high-level DBI.

$query = $db->prepare('select foo from bar where baz = :baz and biz = :biz');
$query->execute($_GET);

Re:How difficult is it.

[Ash Vince]

What moron marked this as informative? I am an ASP developer and I still have to be careful about users entering SQL injection attacks into password boxes.

I have now learn't my lesson in this regard, but one of my sites has been done in the past.

Re:How difficult is it.

[Anonymous Coward]

So you are saying that, even though I have a very good understanding of PHP and I have been able to use it to accomplish everything that I have been asked to do, since I don't have any experience with PERL, all of my experience with PHP is somehow invalidated? PERL is not a pre-requisite for PHP. PHP is a standalone language, and there's nothing wrong with learning PHP and not learning PERL. Pardon me, but your attitude is elitist bullshit. If you know PERL, great, good for you, but that doesn't take away from anyone who went directly to PHP, and it doesn't mean that PHP is worth any less if someone has no experience with PERL. Again, PHP is a standalone language, and regardless of the features and benefits of using PERL, PHP will continue to be very powerful and more than enough for the vast majority of web applications. 5.8 million Apache servers agree. I should point out that while 5.8 million Apache servers are running PHP, only 1.4 million servers are running perl. So apparently there are 4.4 million Apache servers that run PHP but not perl, and, somehow, miraculously, they are still running! Apparently perl is not needed for PHP after all.

Re:How difficult is it.

[exKingZog]

Scenario: You move from SQL Server to Oracle. You use sprocs, so the only communication you do with the database is to pass parameters too/from said sprocs.

Now, if you'd been using SQL from your object layer, you'd have had to re-write all the SQL code in the application. If, however, you used sprocs, you simply re-write them all, then change your data access layer's setting to use Oracle. Your application is now completely unaware of the change and requires minimal re-writing.

You're also ignoring what happens when a business object (say, a customer order) is split over 3 or 4 tables in the database: using a sproc can give you a nice, simple interface to those tables so your developers don't have to worry about the underlying database structure.

Also, SECURITY. If you use SQL, then you give your application blanket rights to execute arbitrary code. Assuming it's compromised, an attacker could run any code they wanted on your database. Using sprocs, they can only run those procedures which the application is allowed to run.

Re:How difficult is it.

[julesh]

When I tried PHP I can't believe that PHP (or rather the mysql database connection layer) has no concept of bind parameters.

This is because the MySQL API itself has no such concept. Other database engines do, and the PHP implementations of their APIs support it just fine. But PHP include PEAR::DB which provides an implementation as a layer on top of standard functions.

Re:How difficult is it.

[Anonymous Coward]

Yet you still can't spell "separate". If you are a native english speaker then go die in a fire, if not, I apologize.

Re:How difficult is it.

[tha_mink]

I still think Perl is the best choice for small to medium web projects. The comprehensiveness of the CPAN library is unparalleled and with mod_perl you get real integration with the Apache server.

I've been programming for nearly as long as you in the same languages and I disagree with you a bit. For me, working with PERL has always been like sorting the the explosion at the punctuation factory.(yeah, that old gag) It's really ugly to look at and work with and that makes a difference to me, enough so that I try and avoid it when I can.

I agree with your point about novice programmers, and I feel like that is why so many people give PHP a bad rap. Simply because it's so easy to use, anyone can use it. There is MUCH more bad PHP out there than bad perl because a monkey can learn PHP fairly quickly, while PERL takes a bit more practice. That's why I expect to see this same problem pop up with RUBY soon. It's so easy, there's gonna be a shit-ton of lousy code out there.

Re:How difficult is it.

[kthejoker]

Uh, Rails is a framework, not a language. Not quite the same thing.

Go check out Synfony, the PHP5 framework. Exact same concept.

Re:How difficult is it.

[Ash Vince]

The ability to spell and the ability to write decent code do not go hand in hand.

I used to work for a company where one of the coders was an English graduate (Hi Phil), and boy did he create some illogical shit.

Re:How difficult is it.

[Ender Ryan]

Anyone with a reasonable amount of experience with Perl would know not to call it "PERL," so I find your post highly dubious. OTOH, your citicism is entirely aesthetic in nature, so maybe that's fair enough. But, that's hardly an argument worth paying any attention from a practical/logical point of view.

Re:How difficult is it.

[mattyrobinson69]

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

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

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

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

To attack that code, you would do this:

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

that would run this code:

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

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

if you ran this:

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

the query being executed would be this:

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

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

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

Re:How difficult is it.

[Not The Real Me]

Nice point except most banking/financial institutions I've seen are running IIS/ASP/MSSQL Server. Not only that these same firms are hell bent on going Dot Net/ASP.NET

Re:How difficult is it.

[hoppo]

Have you thought about replicating data to another server for querying? You can build indexes on the subscriber that are optimized for selection, and on the publisher that are optimized for insertion. Even in a high-volume environment, it should be almost instantaneous.

Re:How difficult is it.

[Bitsy Boffin]

bind_query('SELECT value FROM REGISTRY WHERE name = ":1"', $name);

My own database abstraction classes have a pretty similar syntax, after years of PHP work I find that this works remarkably well and easily.

$mydb->query("SELECT foo from bar where some_string = :0 and some_int = i:1 and some_date = d:2 and some_real = r:3", 'hello', 12, time(), 37.5);

becomes

******_query("SELECT foo from bar where some_string = 'hello' and some_int = 12 and some_date = '2006-07-19 21:32:48' and some_real = 37.5")

the parameters are type-forced, escaped and converted at the time of substitution, it's quick and easy. Numbered parameters work well for queries with few parameters, for longer ones you can also stick an associative array in the paramter list and use it's members as named parameters ( i:foobar ) to make it easier to keep track of them.

There really is no excuse for a developer not to make thier SQL safe.

Re:How difficult is it.

[brunson]

Tha Mink +1

Re:How difficult is it.

[palantir0]

Trying to make a language or api or whatever more intelligent doesn't help, it will create other issues. Spreading knowledge of how to do security for a language, api, or whatever is much more useful. The problem is that there are so many crap examples that people just copy and think they are safe.

Trying to protect people from themselves is useless. They'll always find a good way to screw themselves dispite you. :)

Cheers

Re:How difficult is it.

[Jaime2]

"Use stored procedures" is not the answer. SPs do nothing to prevent SQL Injection. Example:

Before SPs:

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

After SPs:

mysql_query('EXEC GetValueFromRegistry "' . $name . '"');

Notice, the second bit of code has the exact same vulnerability as the first. SPs do not cure SQL injection. Using parameters does cure SQL Injection. However, parameters can be used with or without stored procedures. Therefore, there is zero correlation between SPs and SQL Injection prevention, and perfect correlation between parameters and SQL Injection prevention. It annoys me to no end when people present a proper parameterized solution to SQL Injection which happens to use SPs and then claim that the use of SPs fixed the problem.

Summary: Poorly implemented stored procedures are just as bad as poorly implemented raw queries. Fix the implementation.

P.S. I am not in any way saying that SPs are bad or worthless. Every time I bring this up I get all sorts of arguments that don't address my point and bash me for not advocating SPs. SPs are good, but the lack of SPs is NOT the cause of the SQL Injection problem.

Re:How difficult is it.

[dcam]

It isn't just unescaped strings. You need to force variables to their correct types. Suppose you expect an int input, which someone sends in a string. Say your code looks like this:
$sql = "SELECT * FROM MyTable WHERE MyID = " . $var;

if $var contains "1 OR 1=1" then you will have problems.

Re:How difficult is it.

[suricatta]

A good quick and easy resource for coders who aren't seurity expects (myself included) is the Open Web Application Security Project, or OWASP for short. It's an open project aimed at documenting security risks in web applications and providing guidelines and examples on how to protect against them. Check it out: http://www.owasp.org/index.php/Category:OWASP_Guid e_Project

Although most of the examples are java, there's a really good PHP section in the guide. I always recommend web coders check that out and just take it all in, so coding secure websites becomes the "normal" way they code, so they do it without having to think about it.

Re:How difficult is it.

[dcam]

We have one query where the user can input around 30 pieces of data and they're all optional.

Not particularly. I won't say that it performs fantastically but you can certainly do it. Simple example:

CREATE PROCEDURE MyProc
@optVar Int
AS

SET NOCOUNT ON

SELECT Foo
FROM Bar
WHERE (@optVar = Bar.opt OR @optVar IS NULL)

You can also add custom sorting to procs, but that can get messier. I'd still argue that this is no less messy than generating the SQL, although it tends to perform less well. You'd probably want a WITH RECOMPILE on the proc.

Re:How difficult is it.

[Anonymous Coward]

...and then you turned sixteen and you thought you were an adult.

Re:How difficult is it.

[Anonymous Coward]

This remembers me the old adaggio "for a man with hammer...". You are the one with just a hammer.

"- It requires developers with a good level of expertise in both the language used to develop the core of the application and the one used for the stored procedures"

What makes you think that the ones developing the application are the ones writing the stored procedures? One *BIG* advantage of the sp's (while a non-looked for one) is that you won't need your whole bunch of developers to be security-aware about how write proper db-accessing code (you see, the very core of this article); only the people in charge of the db part of the project will have to know about db intricacies (not only security but performance too: no more "select * from..." nor wild table joins, nor 'where' clauses that return decamillions of rows on non-indexed columns... just to take ten of them out of a subselect).

On the other hand, it seems only natural protecting data access where it naturally belongs: the data manager. Tommorrow some security officer won't find that some script developed by some obscure IT member from a second class department its leaking data just because all authentication/authorization was made on the big application which the script just bypasses by accessing directly to the database (the same can be said about any logical data constraint you added to your bussiness logic: well, Dept. A's budget limit was tightly controlled on the proper Java servlet. Who would imagine somebody would develop a little Ms Access app that bypasses it querying the database by ODBC?). Or... Shit! nobody told the new junior developer that all data access must be done through the BL classes? Now Dept. B manager knows he didn't got a rise because we bought a new sports car to Dept. C Director!

"It makes an application tightly couple to the database"

It makes the application NOT coupled to the database AT ALL. After all, any particularities about the db engine are well encapsulated within the db engine itself instead of leaking into the application code. People that haven't work on migration projects seem to think that having db access within stored procedures makes for a migration nightmare in case you have to change the rbdm. People that *do* work on these kinds of migrations know it is much worse the case you have to worry about it higher in the application logics.

" It increases the likellyhood of having version conflict problems between the core application and the database application components"

That's not an specific problem about stored procedures. Any tiered development is open to that kind of problems, and all of them need proper source/change management in place, not only in order to avoid these problems but for proper accountability of the developing effort and achievements. All the versioning problems stored procs might arise can arise one a "high level code" tiered DAL/bussiness/presention structure too.

"Beyond this, the only good reason i can see..."

For a man with a hammer...

"A "job protection" measure by locking the application to the specific skillset combination of a specific developer"

In contrast with a "job protection" measure of having such an intermingled code/data access that only you can manage it? In real world, change the rbdm and the people in charge of that part may go home; change say, Java with C# and the same can be told about the logic programmers; go from the "all style is a complex table and some invisible dots" to the "current AJAX buzzword" and your web artists may better look for a new job.

"Because the developer prefers-to/is-more-confortable-with developing code in the database that in the core application
Hardly good reasons IMHO"

Because the application analist knows better and architects the app the proper way seems to me a better reason.

Re:How difficult is it.

[kpharmer]

> Its easy. You write a DAL which abstracts away any sql you need to write. You then create a code generator, which not
> only creates a DAL class for each table, but generates the procs automatically. It works quite nicely for me.

Can you describe that in more detail?

What I think you're saying is that you generate a set of generic select, insert, update, delete procedures for each table based upon metadata in the database catalog.

If this is the case:
    - how do you handle reporting queries?
    - how do you handle query tuning around performance and concurrency?
    - how do you handle joins?
    - how do you handle updates? set all columns?

Thanks

Re:How difficult is it.

[tpv]

The bigger issue (for Sybase, but not for some other DBs) is handling lists.

Sybase doesn't allow you to pass an array/list/collection (whatever you want to call it) to your proc, so queries like "type IN (1,2,3)" are much easier to do with dynamic SQL than with procs.

Re:How difficult is it.

[Aceticon]

Security: You can grant privs on the sproc and not on the underlying table.

And yet anything the application can do, anybody with the application's password on the database can do too. Unless your application can't write to the database (in which case making the tables read-only would be easier), the procedures will still provide a gateway to the database - in which case you didn't really added any security, at most you've made sure that security breaches will keep data consistency (assuming the stored procs are designed in such a way as to guarantee that - although if they are, they likelly encode business logic).

Testability: You can test your sproc separate from your application logic.

Testing of application components separated from the rest of the application logic (aka unit testing) is already common without the need of spreading the application accross 2 different platforms and 2 different languages. If people design an application with separate Data Access Component (be they objects, components or methods) they can already test them separately from the rest - no need to move that code to the database.

DB roundtrips: Need to update ten tables? Do it in a sproc and call it in 1 step as opposed to 10 steps.

That's what i meant with "solve high-impact IO bottlenecks between the application and the database". My point being you do this as an exception, not as a rule. When choosing the path of also coding in the database, you should keep in mind the side-effects of it. I've personally more than once refrained from doing this because i was aware that nobody else in the company would've been able to maintain the database code when i left (i'm a freelancer), though on other occasions i've used this because other members of the team could maintain that code too.

Splitting the code into multiple tiers - specifically in this case, by putting part of it in the database - is not a decision to take lightly. There is a number of side-effects that might negate any expected beneficts from such a move, for example:
- Two languages, meaning people with expertise in both are needed
- Application upgrades will much more often include database updates. This means your upgrade scripts will have to do both most of the time and that you have twice as much chance that the upgrade will fail. Rolling back failed upgrades accross two different platforms is much more complex that for only one platform.
- Version control on both the database and the application itself is much more important. If you don't control the production environment (for example it's hosted by a customer) both the application and the database should have some sort of version number, with the application checking the one in the database and only running if it matches the expected one. You can get away with not doing it when all that's on the database is a data model, since it rarelly changes (in mature applications, it hardly ever changes).
- All sorts of border problems (in the core application - database border) in developing the application, such as for example not being able to properly take advantage of certain core application side frameworks/libraries for persistant datastore access (since they're not optimized to calling procedures on the database), silent databased side updating of data contained in the core application side caches, meta-definition issues ("what happens if i pass a negative number in this parameter of this stored procedure") when there are two devs one on each side and much more.

If the upsides don't outweight the donwsides and the extra risks of doing it in some other way, then it's beter not to do it this way.

Certainly it's not worth using stored procedures if the only upside is not having to escape SQL input strings.

Re:How difficult is it.

[Aceticon]

Scenario: You move from SQL Server to Oracle. You use sprocs, so the only communication you do with the database is to pass parameters too/from said sprocs.

Now, if you'd been using SQL from your object layer, you'd have had to re-write all the SQL code in the application. If, however, you used sprocs, you simply re-write them all, then change your data access layer's setting to use Oracle. Your application is now completely unaware of the change and requires minimal re-writing.

If you used SQL92 (as in, standard SQL) and were not relying on vendor specific database access libraries you won't have to change a single piece of database access code in your app when changing databases. This is of course dependent on the language you're using for the core application and the database libraries available in that language.
For example, in Java, using the standard libraries all you would have to do is load a different driver class and use a different connection URL.

Even if some queries are database specific, only those queries have to be changed when changing databases, while with stored procs it is 100% guaranteed that the whole database-bound data access layer would need to be redone from scratch.

You're also ignoring what happens when a business object (say, a customer order) is split over 3 or 4 tables in the database: using a sproc can give you a nice, simple interface to those tables so your developers don't have to worry about the underlying database structure.

You can just as easilly create said simple interface in the application side - it's called a method.

Also, SECURITY. If you use SQL, then you give your application blanket rights to execute arbitrary code. Assuming it's compromised, an attacker could run any code they wanted on your database. Using sprocs, they can only run those procedures which the application is allowed to run.

It depends:
- If your stored procedures are low level enough so that that don't contain much business logic, then they will likelly be very close to basic operations and cover a great deal of the universe of all such operations. In that case they're just a non-standard interface for doing most of what could otherwise be done via the standard interface and can be combined to do the vast majority of the possible operations on data.
- If on the other hand they are functions of a high enough level of complexity that combining then still results in pretty much only being able to do changes to the data as the application would (eg, a transfer_money method which "checks the debitors balance, takes money from the debitor's account and places it on the creditors account"), then you've just moved you business logic to the database thus openning a whole new Pandora's box.

Re:How difficult is it.

[plague3106]

You just create a view, and an object in your DAL which reprsents that view. Your DAL would have a 'searcher' object, which you can give it selection and order criteria and returns the rows (as instances of the DAL class).

Query tuning isn't really necessary, since the database supports only the most basic operations. Concurency is handled by your business layer, by some kidn of locking scheme you define.

Joins are the same as reporting; your DAL supports "joining" across classes it or you create a view and the DAL treats it as a readonly table.

Updates do could always set all columns; this is pretty standard if you're already doing data modifications through stored procedures (do you really need 100 different update procedures, each one only updating a subset of the columns). If you're really obsessed and don't need procedures, there's no reason the DAL couldn't figure out that column X wasn't explicity set, and therefore should not be touched.

Check out NHibernate (or hibernate, its Java father). There are tons of articles giving pointers on building your own DAL as well.

Re:How difficult is it.

[exKingZog]

If on the other hand they are functions of a high enough level of complexity that combining then still results in pretty much only being able to do changes to the data as the application would (eg, a transfer_money method which "checks the debitors balance, takes money from the debitor's account and places it on the creditors account"), then you've just moved you business logic to the database thus openning a whole new Pandora's box.

I would have defined that as data logic, personally. Business logic might define, for example, alerts on money transfers, whereas actually performing the transfer (send X amount from A's account to B's account) sounds like data logic - additionally, if working in a large team, the programmers can safely use this method without knowing all the database complexity (in a financial application such as your example they may not have access rights to the tables either).

If you used SQL92 (as in, standard SQL) and were not relying on vendor specific database access libraries you won't have to change a single piece of database access code in your app when changing databases. This is of course dependent on the language you're using for the core application and the database libraries available in that language. For example, in Java, using the standard libraries all you would have to do is load a different driver class and use a different connection URL.

Fair point - but what if you changed the database structure, but not the data logic? If you have your transfer_money sproc, then your database devs can safely modify this to take account of the new data structure without having to modify the business objects.

Re:How difficult is it.

[(trb001)]

Yes, we have, and we're looking to going to a datawarehouse/mart solution, but a) the customer is slow to approve it, and b) there are still some concerns with throughput. I'm not a DBA by nature, but from what we've seen the query-optimized products would have trouble being fed and indexing our incoming data, leading to an increasing delta. We could possibly make up that delta after hours, but the customer doesn't really want to do that, and they *really* don't want the answer "well, we think for XYZ amount of data we'll lag at an increasing rate of ABC records/minute, meaning we'll catch up an hour after COB".

--trb

Re:How difficult is it.

[(trb001)]

Yes and no. A RDBMS is capable of optimizing queries OR inserts, but not necessarily both. It's sort of a sliding scale; the more you favor one side, the slower the other side gets. Add an index here to optimize querying, slow down your inserts. Remove indexes and normalize for inserts, slow down queries. We've been told to optimize for inserts, but then get complaints because queries take too long.

--trb

Re:How difficult is it.

[Anonymous Coward]

PHP's mysql_real_escape_string() will still work on this, and MySQL will still accept it. I've done it.

Maybe it's a hackish way of doing it, but really, you're looking at semantics there. Why force type when you don't have to? It's not necessarily more secure.

Or you could just make every column of a text type and have PHP convert strings to numbers when needed. *shrugs*

Re:How difficult is it.

[Anonymous Coward]

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

I'm the original AC, so let me first and formost extend a warm Fuck You, You Don't Know What You Are Talking About.

I've had students like you and I've have people that have worked for me that have acted this way. They sure do knows them something about programming -- but I've never seen them write a damn thing for anyone but other geeks. To do what? Write more software for geeks. Write for every day people who's goals are not to be a nerd? Nah, never possible for them because they were too focused on "LEXICALLY SCOPED VARIABLES" and "UNICODE SUPPORT" all the while the rest of us seemed to be able to get the job done with variables that didn't need strong typing nor tied down to a single area.

Why? Because unless you are a nerd that is paralysed by choice, IT JUST DOESN'T MATTER.

Skipping Perl for PHP means nothing. I also didn't use A or B but that didn't stop me from using C. I can program in Perl and I can debug it as I've had to before. I refused to use it for any starting project of my own because its horrible.

I also didn't use PHP until it was a compiled language and well established. The problems of PHP are not the same ones with Perl. PHP is human readable. PHP makes sense from a semantic point of view. It, however, doesn't make nerds cream their panties and for that, idiotboys like you will never understand. At the same time, I get my work done in it...and many other languages. Ever use Fortral, Cobal or Pascal? I haven't in several years, but I have. Want to program AI, Scheme was a killer language for this that I dropped C++ for as it fit the bill. Not as fast, not as efficient, but for this type of work...

You really have no clue. Admit it and stop being a nerd simply because it gives you the excuse not to get the girl.

Re:How difficult is it.

[Anonymous Coward]

>> Folks that make blind statements about folks who suck generally are the ones wiping their lips afterwards from the sweet juice of man-gina.

Like you just did?

Re:How difficult is it.

[dcam]

PHP's mysql_real_escape_string() won't do squat.

According to the documentation:
mysql_real_escape_string() calls MySQL's library function mysql_real_escape_string, which prepends backslashes to the following characters: \x00, \n, \r, \, ', " and \x1a.

This does nothing against the attack I described. If you called mysql_real_escape_string() on the code I gave you, you will still end up with an SQL string of:

"SELECT * FROM MyTable WHERE MyID = 1 OR 1 = 1"

As for your other suggestion about forcing everything to string in the database ...

I am reaching the opinion that most people who work with MySQL seriously need to spend some time with a real database. It seems to retard understanding of basic database concepts.

Re:How difficult is it.

[lonecrow]

How many apps were written for mySQL before it had stored procedure support? Didn't that force everyone to use dynamic SQL in code?

I use ASP with MSSQL and my baseline security habit for web apps starts with cleansing user inputs for cross-site scripting before populating my ado command object which then executes a stored procedure. The sql account used from the web site is only ever granted execute permissions for the stored procedures and never to any direct access to any underlying table or view. I especially like this last feature ('permission chaining' in MSSQL). Even if someone managed to compromise the site and retreive my db connection string, the account they harvest is only able to run my prepared statements.

I beleive this provides a reasonable starting place for a secure web app. If there is some other foundation step I am missing, please enlighten me. Developing an app with dynamic sql in code seems like a very bad thing to do. At one point I searched for two days looking for a comprehensive report of how to escape user input for dynamic sql and leave no holes and was unable to find any. They all missed one or two tricks that you can find on sites exposing SQL injection attacks.

Re:How difficult is it.

[jbplou]

If you need to do both you need two databases. A OLTP and OLAP database. Use the OLTP for data entry use the OLAP for datawarehouse. OLTP mainly optimized for fast inserting OLAP for fast querying.

Re:How difficult is it.

[Anonymous Coward]

Or even better, don't directly talk to the DB from your web tier. push this code where it belongs, into an app layer that is dealt with by senior staff. couple that with good security audits and automated unit tests and you have a manageable solution. hell i just canned a guy who embedded sql into a web page in our last revision. this is a non issue for professionals.

Re:How difficult is it.

[tha_mink]

But, that's hardly an argument worth paying any attention from a practical/logical point of view.
Isn't it though? Is the ability to easily read code a bonus for the language or not? I think it is. The more readable, the more easily written and refactored. I guess if you never have to look at anyone else's code and nobody has to look at yours then it doesn't matter. I HATE to deal with OPP (yeah, I went there) simply because there are so many ways to do the same thing. I'd much rather look at bad PHP than bad PERL.

Injection preventation doesn't need input checking

[Killeri]

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

Qualifications

[Chris Graham]

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

Re:Qualifications

[cnettel]

Politicians don't.

Re:Qualifications

[sshutt]

I agree a qualification to work on databases or any other programming project should be a requierment, but would any computer science degree be enough or would a developer be expected to go to specific courses and get specific certification?

I'd go with the later, as I really have no real database programming training from my degree.

You could go asfar as to say that any part of a system you work on you should be certified to work on especially if they could potentially be open to abuse, and extra certificates always looks good

so Cirtificate of Safe Database Access Coding (CSDAC)
Cirtificate of Safe Networking Coding (CSNC)
Cirtificate of Safe Input Handling (CSIH)

but maybe its not just a problem for programmers using an api, cant the database's api be made more secure so SQL injection cant happen?

Re:Qualifications

[Chris Graham]

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

Re:Qualifications

[cnettel]

I suppose you're satirical, as the electorate should be able to decide that quite easily. If certain qualifications are desired, they/we have to vote for those who present those qualifications. My point (aside from providing a punchline) is simply that the amount of power/possible accumulated damage is not as relevant as the question of whether an immediate loss of life is a possible outcome. Certificate to hell for specific subsets of medical devices and embedded systems. Few websites are as critical, although the economical damage can certainly be significant.

Re:Qualifications

[plague3106]

Whoa, but there are alot of programmers out there whose code can be insecure, because it doesn't really matter. Like thier person website, or some fan forum. Is it a big deal if those kinds of sites are compromised?

Re:Qualifications

[P3NIS_CLEAVER]

This is web 101, I can't imagine that people are deploying websites for banks that can be attacked this way. We have a few simple rules that our developers follow:

Stored procedures only
Use of a data wrapper that won't allow special characters in the paramaters

It is possible to inject sql into stored procedures through the paramaters if they do certain times of opertations. My memory is crusty now or I would post specifics here.

Re:Qualifications

[tbannist]

And look at the quality of today's politicans...

Re:Qualifications

[Vlad2.0]

You have an interesting concept of "serious danger." While I might be in "serious danger" of my boss if our site was compromised by such an injection, for the vast, vast, vast majority of sites out there calling it "serious danger" is way overkill.

When builders fuck up a building, people can die. When electricians fuck up your wiring, people can die. When gas installers fuck up your heater...people can die. When you fuck up your blog, no one really gives a shit. Determining where Amateur ends and Professional begins is a bit too much of a grey area.

Making (professional) programmers liable for their code is the job of their employer. If I hire you as a programmer and find out later you've been writing sloppy, insecure code (shame on me for not having someone else check it) and it's discovered that your sloppy code cost us a non-trivial amount of cash, I'd certainly consider firing you or at the very least not giving you a raise/promotion/whatever any time soon.

Re:Qualifications

[Duhavid]

Yeah, great, another certification for the clueless to go
pick up study books so they can pass the tests without knowing
anything about subject matter, giving clueless PBH's another
"gosh, dont hire *him/her/it*, no certification on security".

Re:Qualifications

[dodobh]

Except that security is relative and a risk management function. Secure against what threats? At what cost? Earlier we had buffer overflows. Now the holes have just moved up the stack.

Re:Qualifications

[runcible]

Uh huh, and all those other folks can be sued when their shit fails...read your software licenses.

Hold companies liable for their crap code and this, like many, many other security problems, will evaporate.

Re:Qualifications

[Anonymous Coward]

And have good hair. Don't forget the hair.

Re:Qualifications

[ladadadada]

The primary difference would be that if a builder, electrician or gas installer screw up, people's lives are in jeopardy. I'd go as far as to say that people's lives are likely to be lost. If a coder screws up, people's details and company secrets are likely to be lost (well, they still have the details and secrets but so do the bad guys) but no one's lives are lost.

A qualification is not a bad thing, in fact, a goverment recognised qualification could certainly aid businesses in finding suitable security coders and qualified security coders in finding employment, but mandating it is just a bit over the top.

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

[fractalus]

The only people who consider it a pain in the ass are people who are (a) lazy, (b) not adequately security-conscious, (c) programming without a framework that provides good tools to do this. The reason we have so many SQL injections is because we have legions of web programmers who were never taught how to write code in a hostile environment. Web programming is never presented in that light; it's always, "here's a quick little script that fetches twenty records from a database and displays them." Security is far too often a footnote or an appendix that beginning programmers never get to. Building apps for the web is not like doing your Data Structures I homework. You need a different mindset. It's a lot more like designing locks--for prisons full of inmates eager to get out.

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

[Bogtha]

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

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

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

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

[tgd]

To be more generic, the reason we have so many web security compromises at all is because we have legions of web programmers who have never been taught how to write code.

90% of the friends I have who are web developers have no formal engineering training.

It shows. (No offense to any of them who may read this... but seriously, your code sucks.)

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

[gutnor]

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

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

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

[oyenstikker]

The problem is compounded by the fact that one manager/boss/owner who does not value or understand security and 100 underlings that do means that the product is going to be insecure.

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

[Reality Master 101]

It shows. (No offense to any of them who may read this... but seriously, your code sucks.)

It's not just web developers. Look at the average OSS application. Hell, look at 'ssh', one of the fundamental tools sometime. It's unbelievably bad uncommented spaghetti crap.

The problem with software is that it's easy for anybody to jump in and do it... and it shows.

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

[ubergenius]

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

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

[Xanthis]

It's a lot more like designing locks--for prisons full of inmates eager to get out.

Or rather like designing locks--for jewelry stores full of thieves eager to get in.

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

[Anonymous Coward]

If the store is already full of jewlery thieves why would they be trying to get in?

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

[Aceticon]

My very recent experience working for a company that makes web sites (i'm a freelance software designer/developer) is that most developers working there have almost no notion of data integrity (transactions, what's that?).

Interestingly enough the place suffered from most of the illnesses of our industry:
- Clueless managers
- Disorganised development process (if we can call THAT a process)
- Ad-hoc tracking of requirements
- Undocumented requirements, no design, undocumented code
- Feature creep
- Overworking
- Lack of professionalism in the contacts with the customer, including on delivery
- Arbitrary choice of frameworks, libraries and even languages to implement new applications, the choice being mostly made on the basis of the "how much fun it would be to use this", "this is new and sounds cool" and "how good it will look on my CV" criteria.

From what i've read here on Slashdot i'm starting to get a feeling that this a wider problem with this part of the industry (ie web development) rather than just a problem with a specific company.

If that is so, it's hardly surprising to find widespread input checking related security problems with web sites...

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

[Dracolytch]

What makes you think that these are problems with just the web segment of application development?

~D

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

[jZnat]

OpenSSH is well documented and quite clean. Maybe the non-OpenBSD version is messy like you say, but Theo takes pride in OpenBSD's documentation, so watch out.

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

[Reality Master 101]

OpenSSH is well documented and quite clean.

As far as I'm aware, there is no non-OpenBSD version. But have you looked at it, personally? I have. If Theo takes pride in that piece of crap, then he should take a look sometime at what well documented source code looks like. Hint: It's not a short comment at the top of each subroutine.

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

[dcam]

No offence but given OpenBSD's security record I think I'd take Theo's views over yours.

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

[Reality Master 101]

No offence but given OpenBSD's security record I think I'd take Theo's views over yours.

No one said it was insecure. I said OpenSSH was horribly written and poorly documented. Don't take my word for it, go look for yourself and see how many comments are in it.

Of course, that doesn't mean that it doesn't work well. All it proves is that Theo took enough care to make it secure. But it's obvious that he doesn't give a damn about anyone else being able to understand it.

Hard for Devs?

[CHR1S]

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

Re:Hard for Devs?

[Goaway]

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

Re:Hard for Devs?

[Anonymous Coward]

I seriously doubt I'm going to read anything funnier than this comment today. Thank you so much.

Re:Hard for Devs?

[CHR1S]

I agree that the mysql_real_escape_string() is a pathetic function name; however, I am curious what language automatically checks your users input for any attempt at SQL Injection. I would also like to know how this impacts input when you expect to receive a quote, single quote, backslash, or semi-colon. This may simply be a lack of knowledge on my part so I am really asking.. please don't read it as sarcasm.

Re:Hard for Devs?

[Goaway]

Perl does. Python does. I'm pretty sure Ruby does.

Pretty much every language except PHP does. PHP has several modules to do this the right way too, I'm told, but nobody even knows about them.

SQL injection is only a problem because PHP does things in the worst way possible.

Re:Hard for Devs?

[Bogtha]

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

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

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

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

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

Re:Hard for Devs?

[CHR1S]

Python does? Hm, I never realized that.. I always verified my input anyways. I completely agree that PHP needs to change a lot of things, such as getting rid of depricated functions, but I still like the language for performing many web applications. I actually thought ASP had more problems with SQL injection than PHP, as I said before though I could be wrong. Also, it doesn't surprise me that Perl has protection for this.

Re:Hard for Devs?

[phasm42]

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

Here's an example of parameterized queries in Java:

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

Need to insert more? Reuse the prepared statement

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

Re:Hard for Devs?

[Anonymous Coward]

FYI, I've found, and used a library that creates prepared statements in PHP. It's called Creole, and it is based on JDBC. Ignorance isn't an excuse to bash a language.

Re:Hard for Devs?

[masklinn]

Yep, Python's DBAPI2 standards requires prepared statements which do all the escaping for you. Works out like that:

cur.execute('SELECT foo FROM bar WHERE baz=%s;", (bazvalue,))

Does all the required SQL escaping on your values and safely injects them in your query.

Makes it actually harder to do it wrong than to do it right. Perl's or Ruby's DBI APIs (Perl's DBI is the oldest one, Ruby's DBI comes from it) play the same role.

Re:Hard for Devs?

[masklinn]

The point of his rant is that the language offers insecure DB access methods by design and by default.

Had it offered no BD access libs out of the box, requiring the users to use 3rd party libs, no one would've cared. Had it provided prepared statements, everyone would've been happy. It doesn't do either, and just fucks everything up.

Re:Hard for Devs?

[jZnat]

PHP does, stop spreading FUD.

Re:Hard for Devs?

[Anonymous Coward]

"PHP does, stop spreading FUD."

PHP *5.1* does. Yes, only the latest stable version from PHP, the one almost nobody uses nor properly know how to work with does.

Stop saying nonsenses.

A pain for who exactly?

[MosesJones]

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

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

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

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

Interesting...

[aymanh]

Interesting, given that SQL injection is one of the easiest attacks to protect against, by making all database access through an abstraction layer that escapes input, many web frameworks have support for database abstraction layers and prepared statements, like PEAR::DB for PHP, developers just need to make use of them.

Re:Interesting...

[RalphSleigh]

No good when your shitty web host wont let you install pear though... (personal problem came up a few months back)

Re:Interesting...

[aymanh]

Well, you can still roll your own layer, or manually install one that is implemented in pure PHP, check Drupal's DB layer for example.

Re:Interesting...

[BalanceOfJudgement]

You can actually download PEAR and toss it in any directory in your hosting account and then include the PEAR.php file. It will do the rest, and you don't need it to be bundled with PHP. I've resorted to that approach a few times.

Re:Interesting...

[baadger]

So...change your web host?

Re:Interesting...

[ajs318]

Just unpack the PEAR tarball and scp/ftp the relevant parts up to your web host. Place them in the same directory as the calling script. PHP looks there first for includes.

Unless your web host has a real BOFH and has not only changed the include order, but also created empty files with the same names as all the wanted includes in some directory which has a higher priority than the working directory .....

Re:Interesting...

[AcornWeb]

Use Dreamhost. They work really well

Re:Injection preventation doesn't need input check

[Gr8Apes]

You're absolutely right - SQL injection attacks are extremely simple to avoid (note - avoid, as in never being able to do them) Mostly it is incompetent and/or lazy code monkeys that are at fault.

Parameters and Stored Procedures

[Anonymous Coward]

You mean everyone doesn't use parameters and stored procedures. Sheesh.

Input checking is a half-assed solution.

[cduffy]

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

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

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

[celotil]

But I can't imagine that it would be much more difficult to protect a database, that is accessable through the web at large, than by ensuring that the only thing that goes through the form is alphanumeric numbers, and possibly the @ symbol and periods in email spaces, and then you can employ format checking to make sure it's something like blah@foo.com.

I don't think I've ever had a need to enter anything that isn't as simple as my name, email address, phone number, or numbers with periods (for banking), so why would putting a check for this be such a difficult prospect?

I'd imagine that the only circumstances where someone might have to input contrary data to plain alphanumerics might be inside corporate sites, but then wouldn't there be security in place to ensure that only valid users have logged in, again with nothing more than alphanumeric characters?

Don't tell me that there's a growing number of web sites that aren't doing format checking (on the server side of course, to prevent people working around javascript checks locally) on their login inputs and other form fields before processing the input further.

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

[celotil]

When I started typing the comment above there were no comments on this article. I wasn't trying to be redundant. :/

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

[hawkinspeter]

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

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

[celotil]

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

You sort of answered the question for me. Just have the format checker insert the escaping, or error on too much junk, i.e. "Ed O'Neil d'ah bl'ah" .

{Shrug}

Like I said, I'm not experienced with this stuff, certainly not enough to give a discertation in an online forum, and I'll have to look up what you mean exactly by parameters, but I suspect it's what I'm meaning but not articulating properly.

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

[LeRandy]

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

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

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

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

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

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

[Virak]

I don't think I've ever had a need to enter anything that isn't as simple as my name, email address, phone number, or numbers with periods

What about your post?

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

[phasm42]

If you think parameters means escaping, you're dead wrong. If you interact with a database, you really need to know this.

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

[celotil]

What about your post?

Didn't think of that, but your question and the person above who pointed out foreign languages has made me think a little more.

Okay, so we've got a form, here for example, and I have a foreign name, like René, and I wish to mention it during my post.

Well, I suppose the database isn't using the fields of name, subject, and comment as something to be executed, like a program, so wouldn't that make entering the following string perfectly safe and innocuous?

??S="?*112+¼õ"

Apologies to anyone who has sudden issues with their browser. The above string is just a few characters pulled from the special character palette.

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

[Virak]

The DB doesn't know or care about the source. If you use concatenation and improperly escaped strings, something like

foo'; DROP TABLE users; --

will have the same effect no matter whether it's in the comment field or some other field. You should *always* use paramaterized queries. If your language doesn't have those, use the string escape function. If it doesn't have *that*, then you're fucked.

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

[celotil]

The DB doesn't know or care about the source. If you use concatenation and improperly escaped strings, something like

foo'; DROP TABLE users; --

will have the same effect no matter whether it's in the comment field or some other field. You should *always* use paramaterized queries. If your language doesn't have those, use the string escape function. If it doesn't have *that*, then you're fucked.

Thanks, I'll keep this stuff in mind cause it will most likely come in handy one day - I dabble in things, some I probably shouldn't. :)

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

[GroinWeasel]

You're not fucked as such.

You just have to write your own string escaping function (or, better, write your own statement parameterising code).

If the language doesn't even have a string replace function, then you're fucked. (or rather messing around with character arrays or pointers).

Its just levels of hassle, but its hassle that a thinking professional would only have to go through ONCE with any given language...

If the wheel doesn't exist; invent it before trying to make a car...

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

[Just Some Guy]

I'd imagine that the only circumstances where someone might have to input contrary data to plain alphanumerics might be inside corporate sites, but then wouldn't there be security in place to ensure that only valid users have logged in, again with nothing more than alphanumeric characters?

No offense, but that's insane. If I'm sloppy with a toy website at home, then some random visitor can get access to all the pictures of my kids playing in the front yard, not just the ones I actually published. If I'm sloppy with our corporate website, then some random employee can get access to the company financials, payroll, customer lists, etc.

I work in a relatively small company where I trust everyone, but I'm still in full paranoid mode whenever I allow them to enter data.

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

[KIFulgore]

"DROP TABLE users;"

Was that a thinly veiled injection attack? Should it be chalked up in the statistics? Should my reply count too?

I don't think I've used a language or DB that doesn't support parameterized queries. Then again I've really only used ASP and ADO.NET. Once I learned that simply "concatenating strings and executing is bad, m'kay?" then I was pretty much fine.

Use PreparedStatements with Java

[sbrown123]

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

Re:Use PreparedStatements with Java

[CastrTroy]

Yeah. I never really got how injection attacks were really that much of a problem. Just use prepared statements. Not only will you system be more secure, it will run faster. You don't even have to worry about injection attacks because it's impossible. Failing that, you could just make sure that you do your own verification of the string before doing it, but really it's not worth the hassle. It much easier to write prepared statements.

Re:Use PreparedStatements with Java

[IPFreely]

PreparedStatements can be immune to SQL Injection based attacks.

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

Re:Use PreparedStatements with Java

[tedgyz]

Amen, brother! PreparedStatements can mean the difference between a scalable app, and a thrash-monster.

Re:Use PreparedStatements with Java

[(trb001)]

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

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

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

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

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

--trb

Re:Use PreparedStatements with Java

[phasm42]

String SQL = new String("select * from user where username = ?");

This is wasteful -- it generates a new String object every time when it's just a literal. Just use

String SQL = "select * from user where username = ?";

Re:Use PreparedStatements with Java

[dreamt]

There are similar ways to do this in any "real" language. There are parameters available in ADO, ADO.Net, ODBC, JDBC, and just about whatever other database interface that exist.

Working in the QA department for a database vendor ( -- shameless plug -- Intersystems Cache --) I can tell you that working with parameters is not all that much more difficult than blind SQL. Most languages can create bound parameters in a single extra line of code.

Note that the statement about being much faster really does depend on the architecture of the backend database -- Most databases will parse the SQL input and have an internal statement cache that handles them all the same. Some of the client drivers (xDBC, ADO, etc) have their own pre-parsers as well (which would not be immune to SQL Injection) which will internally parameterize the incoming SQL

Re:Use PreparedStatements with Java

[(trb001)]

I believe that the literal is turned into a new String object by the compmiler. While it may be unnecessary to add the new, it's functionally identical and will consume the same amount of memory. From Sun's website:

All string literals in Java programs, such as "abc", are implemented as instances of this class.

--trb

Re:Use PreparedStatements with Java

[eluusive]

So you're declaring two string instances then yes? new String( "Foo" -- #1 ) -- #2

Re:Use PreparedStatements with Java

[(trb001)]

From what Sun's website is saying, I don't think so. It sounds like the compiler processes your source and essentially adds a new String( ... ) around your literals. So in effect...

String s = "asdf"; ...becomes...

String s = new String("asdf"); ...in the compiler's eyes. Whereas...

String s = new String("asdf"); ...does NOT become...

String s = new String(new String("asdf")); ...because it's already assumed to have instantiated a new String object.

That would be relatively simple compiler optimization, and my reading is that the javac compiler does it.

--trb

Re:Use PreparedStatements with Java

[phasm42]

It is not functionally identical. String literals are turned into instances of String yes, but saying "new String("X")" creates two Strings, the original literal embedded in the class, and a new one generated at runtime. The code does this:

String s = "foo";
String s2 = new String(s);

as opposed to this:

String s = "foo";
String s2 = s;

Re:Use PreparedStatements with Java

[phasm42]

This should not be optimized, because it is functionally different. These code snippets will work differently:

String s = "foo";
String s2 = new String(s);
System.out.println(s == s2);

Prints "false"

String s = "foo";
String s2 = s;
System.out.println(s == s2);

Prints "true"
Furthermore, if you do the "new String(x)" inside a loop, you are creating an object with every pass through the loop. Although you may want a separate object for some purposes, this is generally not the case because strings are immutable.

Re:Use PreparedStatements with Java

[phasm42]

A better example:

String s = "foo";
String s2 = "foo";
System.out.println(s == s2);

Will generally print "true" since the compiler can intern the "foo" literal.

String s = "foo";
String s2 = new String("foo");
System.out.println(s == s2);

Will print false since s2 is a new String.

Re:Use PreparedStatements with Java

[Anonymous Coward]

Anyone who uses Java for web forms should be shot.

Re:Use PreparedStatements with Java

[icklemichael]

Identical string literals in a java source file are all references to one String. So in my class if I have two occurrences of "foo", then they are equals (in the == sense).

The moment you call new String("foo") you create a new String object.

eg.

[michael@mira stringtest]$ javac StringOne.java
[michael@mira stringtest]$ cat StringOne.java
public class StringOne {

public static void main(String[] args) {
System.out.println("foo" == "foo");
System.out.println("foo" == new String("foo"));
System.out.println("foo" == new String("foo").intern());
}
}
[michael@mira stringtest]$ javac StringOne.java
[michael@mira stringtest]$ java StringOne
true
false
true

intern() returns a reference to a String in the shared pool. In this case it's the one which loaded with the class.

And just in case you don't believe me, look at the bytecode generated by this little class:

[michael@mira stringtest]$ cat StringTwo.java
public class StringTwo {
public String sane() {
return "foo";
}
public String insane() {
return new String("foo");
}
}
[michael@mira stringtest]$ javap -c StringTwo
Compiled from "StringTwo.java"
public class StringTwo extends java.lang.Object{
public StringTwo()
Code:
0: aload_0
1: invokespecial #1; //Method java/lang/Object."":()V
4: return

public java.lang.String sane();
Code:
0: ldc #2; //String foo
2: areturn

public java.lang.String insane();
Code:
0: new #3; //class String
3: dup
4: ldc #2; //String foo
6: invokespecial #4; //Method java/lang/String."":(Ljava/lang/String;)V
9: areturn

}

[michael@mira stringtest]$

The compiler cannot make the optimisation you refer to because you have explicitly asked it for two different Strings and so the optimisation would not preserve the semantics of your code.

So, in summary, don't ever write new String("foo"), new String(String string) has one good use, and that's to minimise the size of the backing char[]. This can become vastly over sized because StringBuffer.toString() will share arrays until another modification is made to the StringBuffer (for performance reasons), but if your StringBuffer was big and is now small...

Re:Use PreparedStatements with Java

[bnenning]

The "new" operator in Java always creates a new object. It's an unnecessary misfeature adopted so C++ guys would be more comfortable.

Re:Use PreparedStatements with Java

[Anonymous Coward]

There are similar ways to do this in any "real" language.

Gawd forbid someone use those "fake" laguages like....um....what exactly is a fake programming language? I mean, if there are real programming languages, that must mean there are fake programming ones too?

shameless plug -- Intersystems Cache

Don't be ashamed. People who work at Intersystems plug the company name and products on most boards out there.

Re:Use PreparedStatements with Java

[phasm42]

...because StringBuffer.toString() will share arrays until another modification is made to the StringBuffer...

Wow, I had no idea. However, this is no longer true in Java 5 -- see this bug report. The complaint was slow performance due to removal of the sharing. The gist of it seemed to be that sharing was dependant on synchronization, and StringBuilder was introduced in 1.5, which is an unsynchronized class. Furthermore, the compiler now uses StringBuilder for concatenations instead of StringBuffer as before. And lastly, they claim to see a slight slowdown using array sharing in their benchmarks, and believe the submitter is having a performance problem under an odd set of circumstances.

Anyway, very interesting to know. I typically re-use StringBuffers (now StringBuilders for my new stuff), so the array sharing didn't do much good for me, and may have actually wasted space if the String sizes varied greatly. Here's the 1.5 code if you're interested:

StringBuffer:

public synchronized String toString() {
return new String(value, 0, count);
}

StringBuilder:

public String toString() {
// Create a copy, don't share the array
return new String(value, 0, count);
}

Re:Use PreparedStatements with Java

[icklemichael]

Thanks for the link, hadn't realised they'd changed the behaviour in 5. Probably for the best as I've seen loads of memory leaks caused because of it!

One day I hope to get to use Java 5 at work, but still, at least I'm not still using 1.3!

Re:Use PreparedStatements with Java

[phasm42]

That's too bad, I love 1.5. We switched to it as soon as it came out. The new enums are great -- your enums can be full classes with methods and multiple bits of data associated with each constant, and you can even give each constant distinct implementations of methods. It does wonders for organizing code. I also love the ability to get the stack trace of other threads -- before 1.5, you could only get the stack trace of the current thread or of a Throwable. Now I can enumerate all threads, get their stack traces, and easily see what's going on. Generics are okay, but it's not that big of a deal. I'm starting to get into annotations. Under the right circumstances (an ORM), they're very useful. If you deal with a lot of concurrency, the java.util.concurrent package and subpackages are very nice implementations of locking, queueing, and atomic operations that you've probably had to implement at some time.

I don't know what's holding your work back from 1.5, but if possible you should push for it. It was a really big improvement. The one thing that caused us trouble was some jakarta code that declared variables named "enum" -- this is now a keyword in 1.5.

Re:Use PreparedStatements with Java

[vr]

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

Actually, they're not automatically immune to SQL injection, even if you use them right. Whether they're immune or not, depends on the implementation of the database driver. There have actually been cases where bugs or implementation flaws have left PreparedStatements open for SQL injection.

Pain for web developers?

[kjart]

Sure, if I'm putting together a blog for myself security may not be my top priority and in a situation like this the "I'm too lazy and this is a pain in the ass" excuse is fine (just dont complain if things go wrong).

However, it's an alltogether different story if you're doing professional web development - the "I'm lazy" excuse doesn't cut it when you're developing something commercially. It is your job to make a functional application and a (large) part of that is making it resilient towards exploits. How pretty your app is doesn't mean squat if every 14 year old script kiddie can have his way with it.

Re:Pain for web developers?

[baadger]

Sure, if I'm putting together a blog for myself security may not be my top priority and in a situation like this the "I'm too lazy and this is a pain in the ass" excuse is fine

This is great. I never knew people had blogs just for themselves. But may I ask, on the off chance that you and your insights were actually worth reading about would you give a thought to those commenting on your weblog? Their email addresses? usernames, passwords? How many of them will use the same password for their email as they will for commenting on your blog (if you have registration). How about planting some nasty javascript into your pages? Hell even if it doesn't effect others so directly, somebody who compromises your blog could possibly cause offense to those you know... at your expense.

Neglecting good practice just because doing so foreseeably only effects you is a mistake, or atleast you chose a bad example.

Re:Hooray for PHP!

[Goaway]

Furhtermore, note:

http://www.google.com/search?q=sql+perl
"Results 1 - 10 of about 69,700,000 for sql perl"

http://www.google.com/search?q=sql+php
"Results 1 - 10 of about 151,000,000 for sql php"

http://www.google.com/search?q=%22sql+injection%22 +vulnerability+perl
"Results 1 - 10 of about 243,000 for "sql injection" vulnerability perl"

http://www.google.com/search?q=%22sql+injection%22 +vulnerability+php
"Results 1 - 10 of about 2,170,000 for "sql injection" vulnerability php"

The ration of pages about SQL in general on PHP and Perl is about 1:2. The ration of pages on SQL injection is about 1:9.

SQL injection is mainly a problem with PHP, because of the useless design of the database interfaces.

serious question

[CDPatten]

I thought if you just parse out the ' in user input you are immune. No?

With all my Web Apps I create a function called SafeChar, and have it replace the ' with &#39 ;.

How else is SQL injection done? It's an embarrassing questions to ask, and fortunately I write software for small companies internal use only... but if you don't ask I guess you don't learn.

Re:serious question

[cnettel]

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

Re:serious question

[TubeSteak]

With all my Web Apps I create a function called SafeChar, and have it replace the ' with &#39 ;.

How else is SQL injection done?

It's done in base2, base8, base16, and base64

There's also fun games you can play within/amongst the various encodings.

It depends on what step of the input parsing your SafeChar function is.

Re:serious question

[Bogtha]

I thought if you just parse out the ' in user input you are immune. No?

No. For example, an attacker could use a backslash as the last character in a string, and it would escape the quote you provide to delimit the string.

Trying to roll your own escape function is insanity. Don't do it. Every database API in existence provides well-tested escaping functions. Use them.

With all my Web Apps I create a function called SafeChar, and have it replace the ' with &#39 ;.

Sounds to me like your web applications are vulnerable to an XSS attack - if you really store quotes in this way, they'd fall apart once you escape them when you output them - as the & would be replaced with &. If your applications aren't breaking in this way, then you probably aren't escaping properly.

Re:serious question

[Anonymous Coward]

"select * from account where id = " + SafeChar(Request["id"])

Your function accomplishes nothing. A malicious query string would look like: ...?id=1%20delete%20from%20account

There's been plenty of posts that explain how to avoid this amateur mistake.

Re:serious question

[Anonymous Coward]

Also bear in mind that replacing single quotes (aside from the risks posted above) with their HTML literal is going to seriously bite you in the ass as soon as someone asks you to output a CSV for someone to mail-merge with...

If the user's input a literal single quote then the DBMS should be storing a literal single quote. Escaping it correctly is, of course, the right solution. Replacing it is not.

Re:serious question

[Anonymous Coward]

I think your response shows you as the amateur.

newValue = request.querrystring("id")

"select * from account where id = " + SafeChar(NewValue)

I know nothing about the topic, but even I know that your example is "lacking" at best.

Try again...

Re:serious question

[Krimszon]

You need to do a lot more to prevent SQL injection, it's not just the ', there are many strategies voor injection. Do yourself a favour and start using a library that supports parameters.

Re:serious question

[Anonymous Coward]

As someone pointed out above, some common attacks might include:

If your script looks like this:

Set myRS = OpenRecordSet( _
"SELECT * FROM table WHERE idfield = " & Request("id"))

And someone sends your script a querystring that looks like this:
script.asp?id=345 or 1=1
- or -
script.asp?id=345 OR id IN (delete from users)
- or -
script.asp?id=345 OR id IN (insert into users (username, password) values ('hacker','secret'))

Granted - some languages (like classic ASP via ADO) don't offer much in the way of SQL Injection protection. In this case you might write a function like this:

Function ToInt( ByVal MyNumber)
Dim Reg : Set Reg = New RegExp
Reg.Pattern = "^\d$"
If Reg.Test( Trim(MyNumber) ) Then
ToInt = Trim(MyNumber)
Else
Err.Raise 123, "ToInt()", "Invalid number value: '" & MyNumber & "'"
End If
End Function

Then your database call might be updated to:

Set MyRS = OpenRecordSet( _
"SELECT * FROM table WHERE idfield = " & ToInt(Request("id")) )

It's better than nothing!

Re:serious question

[Anonymous Coward]

Request["id"] works fine in asp. PFO.

Re:Hooray for PHP!

[Anonymous Coward]

Oh and what does? ASP-Visual Studio.NET Framework 3.14159265358979323846 Express Enterprise Edition?

Solution for PHP programmers

[ylikone]

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

Re:Solution for PHP programmers

[Bogtha]

Make sure you use strip_tags() on all incoming data that is headed for output on your page (to get rid of cross-site scripting).

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

Re:Solution for PHP programmers

[shaneh0]

For what it's worth, the $_SESSION array is only going to hold data that YOU put in there. Session data is stored on the server, not the client. It wouldn't be a horrible practice to run session data thru your normal "Cleaning" routine but it's not vital. If someone has the ability to stuff session variables then they're executing code on your server and you've got a lot more problems then injection attacks.

Re:Solution for PHP programmers

[metarox]

htmlentities() is another alternative that parses all HTML characters that are meant to be represented as and it supports the encoding of your choice.

Re:Solution for PHP programmers

[metarox]

That's why some people will override PHP's default session handling and implement their own layer with possibly the addition of encryption to it so tampering can't be done. With a simple CRC check added to it before encryption you can prevent (if you are paranoid) nasty things especially if you are on shared hosting where someone could try to alter the data.

Re:Injection preventation doesn't need input check

[lbmouse]

Well, better to light a candle rather than curse the darkness.

Here is an article for beginners about SQL injection hacks.

No no No no No no NO

[IPFreely]

You don't need to escape strings.

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

Re:No no No no No no NO

[mcvos]

You don't need to escape strings. Just don't build your query on the fly. Bind ALL parameters to placeholders in a prebuilt query.

I agree. Escaping works and is certainly a good idea if nothing else does the job, but in most decent programming languages, there is something much more appropriate to do the job.

Re:No no No no No no NO

[archeopterix]

You don't need to escape strings.

I'd even say that you shouldn't. Escaping strings is enumerating badness. You put your backslashes before your single quotes. Secure? Ummmm... sorry, you also have to put backslashes before backslashes. Secure? Nope, your db interprets 0xd inside the sql as "empty the buffer, start new sql", even inside an open string, so you have to escape that also. (Of course you can "enumerate goodness" - only alphanumeric chars, but there is no reason to, if you have param binding)

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

Paremeter binding doesn't exclude building your query on the fly. Having many optional filters that filter on joined tables practically forces you to use dynamic sql but there's no reason not to use param binding in that too.

Re:No no No no No no NO

[wandernotlost]

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

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

Re:No no No no No no NO

[telbij]

Why do programmers think that it's a good idea to write their own escape routines when every database has a facility for denoting what is variable data and what is not?

Yeah, but not every API does. Enter ext/mysql (PHP).

Re:No no No no No no NO

[Anonymous Coward]

Then only incompetents use mysql/php.

Re:No no No no No no NO

[DanQuixote]

Woah! Hey, easy there killer! I'm open to the idea, always looking for a better way to do things. How about instead of accusing me of stupid, you just give some sample code and/or a URL to a howto on binding variables?

Re:Hooray for PHP!

[baadger]

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

Stored procs

[Anonymous Coward]

This advertisement for stored procedures has been brought to you by Slashdot!

Re:Stored procs

[wetelectric]

Not really, if an id (for example a user id) is passed via a url directly into a stored procedure then it still allows someone to input random user ids till a decent one (one with enough privileges) is hit. There is no quick fix or substitute for well written code.

Re:Hooray for PHP!

[nxtw]

ADO.NET has had parameterized queries since 1.0 was released in 2002...

The old ADO also had paramaterized query support, although they weren't as easy to use as in .NET.

mod parent up

[Anonymous Coward]

this approach seems more correct than escaping strings.

Re:Hooray for PHP!

[Goaway]

Perl does. Python does. I'm pretty sure Ruby does. I'm told there are several modules for PHP that do.

SQL injection is only a problem in PHP because PHP does it all wrong by default.

Re:Hooray for PHP!

[Goaway]

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

Re:Hooray for PHP!

[Anonymous Coward]

u twatfaced fuck-wuppit.

Re:Injection preventation doesn't need input check

[Otter Escaping North]

Well, better to light a candle rather than curse the darkness.

You must be new here.

As a code monkey who, in my ever-dwindling spare time, is helping a friend set up a small business website - and learning PHP as I go, I appreciate the link.

Some of us are not lazy or even stupid - just inexperienced.

It's worth mentioning...

[flibuste]

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

I think it is worth mentioning that SQL injection is a real pain for poorly developped web applications. A simple paying-attention and good design of the application layers makes sure SQL injection cannot happen at a cheap cheapo price. 3-tiers anyone?

Re:It's worth mentioning...

[julesh]

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

It's also worth mentioning that it is NO HARDER for web developers to avoid SQL injection than it is for any other kind of developer. Targeting a web platform isn't an excuse.

Re:Hooray for PHP!

[naich]

There is the mysql-real-escape-string to prevent injection in MySQL along with pg_escape_string for PostgreSQL.

Egad, Ads!

[cakefart]

Was anyone else bothered by the ads on that site? That has to be one of the worst content/ad ratios I've ever seen. Each article had to have maybe 2 sentences of code, and the rest was covered in blinking ads or google ads.

"Careful" vs. "correct"

[jc42]

You have to be very careful ...

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

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

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

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

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

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

Re:"Careful" vs. "correct"

[lawpoop]

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

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

Care != time.

No.

[iabervon]

If you just remove or escape any ' characters, you're depending on there not being any byte sequences that the database interprets as ' characters that your function doesn't. This has often turned out not to be the case, particularly with respect to invalid UTF-8 strings. The only safe method to avoid injection attacks is to make sure that no database code parses a statement including user input, because you never know exactly how the database parser will handle statements that programmers wouldn't send intentionally, and so you can never be sure that you're cleaning the input enough. (You can clean the input enough that the standards say it can't end the string, but then you're depending on the database's parser to be bug-free in this area, which has not historically been the case.)

mysql_real_escape_string

[The MAZZTer]

Just stick it around any non-constants you pass in to MySQL (especially ALL user input or user-influenceable input) and you should be good.

Of course, to minimize the risk that you miss one, you might want to use functions or classes to wrap mysql_query. EX I might make a "function selectFromTableX" that takes one field name and one value to compare for equality in the WHERE clause (assuming that's all I ever use SELECT on that table for). Or you can make a class for every table and wrap up ALL queries for that table you'll need.

Re:mysql_real_escape_string

[Anonymous Coward]

PHP automatically escapes strings if magic quotes is on - which it is by default.

if(!get_magic_quotes_gpc()){ ...mysql_real_escape_string(..)...
}

Multi-tiered approach

[Billosaur]

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

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

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

Re:Multi-tiered approach

[MagicMerlin]

first rule of database programming: never trust the application. It is easy (even trivial) to do constraint checking on the database that is absoltely infallable. It is much easier to secure a database than an appliation in fact. The problem is that people focus security in the wrong place, the application.

Re:Multi-tiered approach

[Billosaur]

It is easy (even trivial) to do constraint checking on the database that is absoltely infallable. It is much easier to secure a database than an appliation in fact. The problem is that people focus security in the wrong place, the application.

1. I'm not sure about trivial, but adding constraint checks to the DB just makes everything more secure. You have to figure that if you've set up checks at every stage, by the time data reaches the database it's been looked over at least three times, possibly more depending on your paranoia level.
2. Security has to be focused on all parts of the system. I would no more trust a system where my only data checks lay with my database than I would one where my only data checks rested with the application.

Re:Multi-tiered approach

[Anonymous Coward]

I treat all the data I receive as if it were dog crap. I massage it

Dude, why oh why do you massage dog crap? That's downright odd behaviour to do and even stranger to post about in public.

Re:Multi-tiered approach

[Anonymous Coward]

First rule of writing CGI: never trust the data! I work in Perl

Wow. CGI. I haven't heard that term since 1997.

Oh. You work in PERL. How quaint.

Thanks!

[DahGhostfacedFiddlah]

I've been writing my code on a paranoid basis for a long time - all my queries are built by a class that does its own escaping. I wish I'd known about this earlier.

Dynamic 'WHERE' clauses

[TheRealBurKaZoiD]

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

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

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

Re:Dynamic 'WHERE' clauses

[eluusive]

Stored procedures are not the end all be-all if you have to execute any dynamic sql inside of them. For example. in T-SQL I have to do this occasionally. DECLARE @sql VARCHAR(8000) SET @sql = 'SELECT * FROM ' + @tabName EXEC @sql There is a method for parameterizing SQL via EXECSQL or somsuch, can't remember off the top of my head. But if for some reason a column or table name needs to be dynamically added to a query: obviously you can't use those. While I don't like this, I didn't decide the database so don't blame me for the stupidity.

Re:Dynamic 'WHERE' clauses

[alph0ns3]

You seem knowledgeable. Can you recommend a book? Reading a spec isn't easy. What about this one?

Re:Dynamic 'WHERE' clauses

[TheRealBurKaZoiD]

I've been in a similar situation to what you describe. The dynamic where clause had the potential to be arbitrarily large number of conditions. I had no other choice but to construct an inline query in my database access layer, but fortunately I could structure it as a parameterized query and still avoid sql injection. I can imagine there are situations where you really just have very little choice, but I try as hard as I can to stay away from dynamic SQL on the server side (via EXEC).

I suppose in that situation it would boil down to database security and who has the ability to do what. Well, we do what we can, and try to catch the rest with auditing.

Re:Dynamic 'WHERE' clauses

[TheRealBurKaZoiD]

Eh, it might. Based on the blurb on Amazon I'm curious to read it myself. However, if you're the kind of developer that needs to be highly productive it might not be the best place to look toward for a reference or for code examples. First, I'd probably track down a copy of this at the local Barnes and Noble, or the local University library, and give it a read through for a couple hours, and then decide whether or not I'd spend my money on it. There are other books, like SQL For Mere Mortals, that might give you quicker answers, although I don't know when the last edition of that one was published.

Re:Dynamic 'WHERE' clauses

[Psychotext]

I would also recommend "SQL For Mere Mortals". Shouldn't really matter when the latest edition was, database tech hasn't really moved on a great deal in the last ten years. :) Might seem a little basic, but some of the things it teaches you are gold if you want to be a good DBA.

Re:Dynamic 'WHERE' clauses

[Psychotext]

Actually, damn. I was thinking about "Database design for mere mortals" which was written by the same author. I can't comment on the SQL one as I haven't read it.

Re:Dynamic 'WHERE' clauses

[Duncan3]

Yeap, there you have the cause... MySQL.

When they start training people to develop web applications using a DATABSAE, then they will know how to use all those things you mentioned.

MySQL 5 is the first version even "close" to a database.

this doesn't match my anecdotal evidence...

[JeanBaptiste]

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

Re:Injection preventation doesn't need input check

[Anonymous Coward]

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

that would be PHP.

see the problem now?

Have to be very careful about checking user input?

[mrjb]

A better solution is to define all input fields by means of a framework that properly escapes apostrophes and other unwanted characters. This will effectively make SQL injection impossible with a minimum of fuss.

SQL Euphoria

[digitaldc]

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

How to make SQL injection impossible

[hypersql]

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

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

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

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

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

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

Re:How to make SQL injection impossible

[hypersql]

Replying to myself... Not that you guys think 'but I will not migrate to your stupid H2 database'. This could be done in a small JDBC (or whatever) driver that sits between the 'real' database driver and the application. This driver would just relay all calls to the underlying driver. Except, it would check for literals in SQL statements.

I write this partially because I don't want that anybody patents this idea - Does posting in Slashdot create prior art?

Re:How to make SQL injection impossible

[Anonymous Coward]

> There is a way to solve SQL injection problems: Disallow text literals in the database engine.

What are you talking about? Imagine we have the query

SELECT * FROM users WHERE name='$name'

, while $name is set to

'; DROP TABLE users; SELECT * FROM othertable WHERE something='

. The resulting string your database gets is

SELECT * FROM users WHERE something='';DROP TABLE users; SELECT * FROM othertable WHERE name=''

How does your feature stop this SQL injection?

Re:How to make SQL injection impossible

[eluusive]

That is still a text literal _IN THE DATABASE_ He's referring to forcing you to use paramterized queries EVERYWHERE: @name = '; DROP TABLE users; SELECT * FROM othertable WHERE something=' SELECT * FROM users WHERE name=@name ^-- This does stop sql injection, because the database doesn't consider @name to be part of the SQL.

Re:How to make SQL injection impossible

[hypersql]

If your code is like this (say JDBC):

stmt.executeQuery("SELECT * FROM users WHERE name='" + name +"'");

Then the database engine would throw the following exception:

Text literal not allowed in SQL statement: SELECT * FROM users WHERE something='';DROP TABLE users; SELECT * FROM othertable WHERE name=''

No matter what is in 'name' (that means even if name="Test"). Because it would detect that the SQL statement contains one (or more) text literals. So you would find insecure programming early in development. But if you do this:

PreparedStatement prep;
prep = conn.prepareStatement("SELECT * FROM users WHERE name=?");
prep.setString(1, name);

Then it would work. Because there are no text literals, only a parameter. And you can't do SQL injection with parameters. Magic, isn't it? I'm not sure how to do that with other languages than Java / JDBC, but I'm sure it's possible.

Re:How to make SQL injection impossible

[MntlChaos]

What if I want to actually have a non-user-based literal in a query: i.e. in a check for whether a user has access to a function. SELECT ... FROM users where username=? and userlevel in('admin','moderator') or something like that? Your idea makes coding that a lot more annoying. Here's another example: display active admins: select ... from online_users,users where online_users.last_activity > (current timestamp - 10 minutes) and online_users.userid=users.userid and users.userlevel='admin' Nothing input-driven, but still a text literal.

Re:How to make SQL injection impossible

[hypersql]

> Your idea makes coding that a lot more annoying.

Yes, it does. Unfortunately. But I don't think it would be that bad, many problems could be solved using views. And maybe database defined constants (like using constants in a regular programming language, anyway a good idea):

SELECT ... FROM users where username=?
and userlevel in (CONST.ADMIN, CONST.MODERATOR)

You would need to define the constants in the database somehow (not sure if there is a SQL standard). You would need to do that only once:

SET CONST.ADMIN = 'admin';
SET CONST.MODERATOR = 'moderator';

Reminds me, I need to implement constants in my H2 database engine as well...

Sorry?

[Anonymous Coward]

Where does the article state that PHP is the cause for it to turn into a anti PHP forum? Im really getting bored of slashdot now where every geek and their dog just posts a rant about the Microsoft, Sony, PHP, spelling.. sorry did i forget any?

Do people here still really not have girlfriends? Or is this place really the geekiest of the geeks. Seriously, this is not meant to be flamebait..

Re:Sorry?

[Anonymous Coward]

Im really getting bored of slashdot now where every geek and their dog just posts a rant about the Microsoft, Sony, PHP, spelling.. sorry did i forget any?

Yep. Ironically (or intentionally), you neglected to include ranting about slashdot itself (which I believe is #2 on the list).

Do people here still really not have girlfriends?
Posted "by Anonymous Coward on Wednesday July 19, @09:26AM". (Not everyone has the fortune of having a girlfriend at work)

Good book

[ylikone]

I should have also mentioned that Pro PHP Security is a good book on the subject of creating secure PHP code.

Re:Hooray for PHP!

[hey!]

No, you don't, unless you are either using an utterly shitty language like PHP that doesn't have built-in protection from SQL injection, or you are going out of your way to make your program insecure by using string interpolation in your queries.

I'm not a PHP guy, but from what I can see it's not a language issue. Or, at least it's not primarily a language issue, although admittedly loose type checking contributes to the problem. It's a library/API issue.

IIRC PHP 5 has the equivalent of prepared statements. The problem is the vast body of code in PHP4 and earlier, or which has been ported to 5. The older APIs should be deprected or removed. This would be a minor pain; however, having installed a few php programs on a later interpreter, it's clear to me the PHP folks haven't scrupled to make pain inducing changes in the past.

Three Words.

[RingDev]

Data Abstraction Layer.

I have a couple hundred SP's in a few SQLServer databases on two servers (test/prod) along with entirely separate Sybase and OMD databases and servers with their own SP and (compile time)SQL. All of that work is taken care of in my DAL. I don't screw around with assembling SQL in code. If I want customer information, I create a customer object and I use objCustomer.Find(CustomerID) or objCustomer.FindByName(CustomerName), etc... I don't have to remember stored proc names, or even complex field names (like the Sybase database where all tables are 3 letter acronyms starting with the letter 'r', it's freaking insane.)

-Rick

Were any attempts successful?

[trevdak]

My department was repsponsible for a maintaining major portion of content on my university's server. Some idiot decided to put phpBB up on his personal site, and our whole data root was deleted and replaced with an "I 0wn j00" message. Hack attempts, which we had been monitoring, went from 2-3 attempts per day up into the thousands.

If any attempts on their servers were successful, attacks would increase a thousandfold. Thus, the number of hackers could be staying the same, but their earlier successes could be focusing their work.

So, anyone know if any of these credit unions have been breached?

Re:Were any attempts successful?

[Anonymous Coward]

chmod +755

noob ;)

Re:Injection preventation doesn't need input check

[julesh]

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

that would be PHP.

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

Re:Hooray for PHP!

[baadger]

SQL isn't code. It has absolutely nothing to do with PHP in any integral sense, it's entirely provided by a library. I've already pointed out that there are better libraries and methods available to PHP users as part of the standard package. I am not seeing your point, just random PHP bashing...

Re: Stupid

[EddyPearson]

No shit! As more people use SQL, and as more bad devs can't test user input properly, THERE WILL BE MORE SQL INJECTION ATTACKS!! Slow news day?

I Do Web Programming For A Major University

[CyborgWarrior]

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

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

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

Re:I Do Web Programming For A Major University

[Anonymous Coward]

And if you haven't documented this problem and memoed the department chair and the head of information services, keeping a notarized copy for yourself, you're a scapegoat waiting to happen.

Re:I Do Web Programming For A Major University

[CyborgWarrior]

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

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

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

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

Re:I Do Web Programming For A Major University

[f1055man]

Would you please provide a link to your website?

Re:I Do Web Programming For A Major University

[Qbertino]

If what you say is true you have a very good chance of getting at least part of your tuition refunded for the semester you've been dealing with. In court definitely.

Re:I Do Web Programming For A Major University

[h4ck7h3p14n37]

I've worked in organizations like this; they know something's a problem, but they think they can continue delaying remediation because they've not experienced any pain. Typically things get fixed pretty quickly once there's a problem, but it takes an incident to force change.

In my case it was a development team that was not using revision control for their web based application. I had been complaining for six months that it was a disaster waiting to happen and we needed to install a revision control system. Nothing happened. Eventually the lead developer got married, went on his honeymoon and scheduled an update to the production application without telling anyone. This update was a disaster; he had mixed up old and new versions of various files (big surprise with no RCS) and there was no known good version to revert to. Various execs were angry, the department head was reprimanded and I was told to install a solution post-haste.

The moral of the story? Sometimes it takes a disaster and at times it can be very tempting to create one.

Re:I Do Web Programming For A Major University

[Anonymous Coward]

FYI: there is no expression "point in case". It has no meaning in the English language. And "case in point", which is what you probably we trying for, means, roughly, "as an example", which doesn't make any sense here, since what follows in not even remotely an example of a previous general statement.

Re:I Do Web Programming For A Major University

[CyborgWarrior]

Thanks for the tip, haha I was just being dyslexic ;-). I meant to say case in point. Just FYI: there's no expression "probably we trying for" either.. I'm guessing u meant "probably were trying for" ;-). Haha.

Re:I Do Web Programming For A Major University

[Anonymous Coward]

The point is that even if you'd said it right you'd still be using the expression completely incorrectly.

you NEED that half of your ass

[oni]

Input checking is a half-assed solution.

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

Re:you NEED that half of your ass

[Bogtha]

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

Only if you can't express that with a database constraint or if users can submit out-of-bounds data legitimately. If it's just somebody trying to cause trouble, you don't need to give them a friendly error message. For example, if the only form where they submit the data has a <select> with the values 1-3 for the options, legitimate users aren't going to submit anything else and attackers will just run up against the database constraint. There's no need to write application-level code to check the values.

Re:you NEED that half of your ass

[_xeno_]

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

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

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

Runs off to check latest MySQL documentation

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

Re:you NEED that half of your ass

[MagikSlinger]

I like the fact you attributed a made up quote to the parent post. You're talking about something completely different than he was. He was talking about input checking to check for injection attacks via escape attempts. Not about good old fashioned constraint checking. Do you actually read & understand posts before slagging them?

Re:you NEED that half of your ass

[cduffy]

Like MagikSlinger said, I wasn't talking about out-of-bounds inputs. The topic of discussion, after all, is SQL injection attacks.

Paper Scmaper

[SQLGuru]

You might as well call it an MCDBA or some other stupid acronym.

I know plenty of people with letters after their name (MCSE, PMP, etc.) that fall into one of two categories:
1. Capable and knowledgeable about their area of expertise.
2. Complete idiots.

I know plenty of people WITHOUT letters after their name that fall into one of two categories:
1. Capable and knowledgeable about their area of expertise.
2. Complete idiots.

Certifications are pretty meaningless to me. As for whether a CS degree would help, I'd almost say no (The "lesser" MIS might actually be better in terms of requiring DB courses). I finished my CS degree in '94 without having had a single database class. It was an elective, but not a required class. I didn't think I'd need it. My first job, I had to learn database stuff on my own. And in fact, even without taking a database class until I had been working with them for over 7 years, I was considered the database expert at my first company and now second company (both large Fortune 500's).

BTW, the class I took was the one on Oracle tuning...and it was a joke: they recommended using hints in your SQL. You should tune the statement without hints first. Hints should be a last resort. Why do programmers constantly think that they can do a better job tuning than a large team of programmers who's sole job is to tune a database engine?

Layne

Re:Hooray for PHP!

[Goaway]

You are arguing semantics. The end result of the current state of PHP is: There are a huge amount of scripts out there that are vulenarable to SQL injection, that would not be vulnerable if they were written in another language, because that other language would provide safer mechanisms and encourage their use.

Simple Solution

[Anonymous Coward]

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

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

[Opportunist]

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

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

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

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

Re:Hooray for PHP!

[Goaway]

The API is the language. Claiming otherwise is silly semantic games with no connection to the reality of the situation, which is: There are a million scripts out there with SQL injection vulnerabilities, and most of them will be written in PHP, because PHP encourages (by not actively discouraging) you to write insecure code.

Coldfusion's 2c worth

[A3gis]

I like ColdFusion's method for helping coders protect against this issue -and quite a few other things too: select blah from blah where field = by setting the cfsqltype correctly it also gets around those sodding issues with databases that cant figure out WHICH field you've referenced incorrectly. How many times have you written a 30+ long insert or update statement, go to test it, and receive a message about incorrect data type with no other info... tells you which field broke your query! So the crux of my comment is, I dont know much about PHP or ASP, but ColdFusion developers have ZERO excuse for making the mistake of NOT using cfqueryparam to protect against injection attacks.

Re:Coldfusion's 2c worth

[A3gis]

hah and next time I'll even preview my comment to make sure it goes in properly

Anyone thought of the "save page as"

[hcob$]

I'm thinking if it's a simple form, you can download it. Then you can see how the (very stupid) devs structured their tests and/or edit the form to remove those checks and have the modified form submit the info for you.

Re:Anyone thought of the "save page as"

[ibjhb]

That is why the processing should be done on the server side and not on the client side.

Re:Anyone thought of the "save page as"

[plague3106]

A good user experience though dictates that client side validation be done. The result is that you should do it both places.

Karma to burn: Why Slashdot is OK

[ursabear]

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

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

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

Re:Hooray for PHP!

[husker shiznit]

Those statistics are pretty meaningless when you look at this.

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

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

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

Escaping is a "pain in the ass"?!?

[Anonymous Coward]

"You need a different mindset. It's a lot more like designing locks--for prisons full of inmates eager to get out."

Fortunately slashdot doesn't have that problem.

Stored Procedures are not 100% safe either

[freejamesbrown]

It's really easy to write a stored proc that builds dynamic sql and calls "EXEC" or "EXECUTE" etc on that statement.

This powerpoint presentation is about the best I have read:

http://www.owasp.org/images/7/74/Advanced_SQL_Inje ction.ppt#369,93,Advanced%20SQL%20Injection

Remember folks, you have to do more than parameterize your queries.
m.

White paper on the subject - Worth reading

[TheUnknownCoder]

I came accross this PDF a while ago, and though it was very informative, even providing examples of a few injection ways I hadn't thought before. Their White Papers section is overall pretty good, should be kept in your bookmarks for one of those slow news day.

I for one....

[HeadlessNotAHorseman]

I for one welcome our new SQL-injecting script-kiddie overlords!

What about magic_quotes_gpc

[ManoSinistra]

For PHP, there is an option in your php.ini file called "magic_quotes_gpc" and it should be turned on. That automatically assigns slashes to every single or double quote.

Is this enough to stop most SQL injection attacks? Can anyone give more input on this?

Re:What about magic_quotes_gpc

[eluusive]

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

Re:What about magic_quotes_gpc

[Anonymous Coward]

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

Use your database vendor's string escape functionality.

In conclusion, fuck magic quotes.

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

[pookemon]

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

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

Checking unknown data

[illuminatedwax]

Checking strange and unknown data should not be a "hassle" - it should be something that every programmer does without thinking, like breathing. If you are getting input that the user of the software has control over, you have to treat it like a spiky poison radioactive pirhana. This should be second nature to everyone - the fact that this vulernability exists so much shows how poor the state of software is these days.Programmers should have this sort of thing drilled into their brains as far as it can go.

Re:Hooray for PHP!

[Goaway]

That's why I included the "sql php" and "sql perl" searches at the top. Searching for just "php" will include every page which is written in PHP!

Look again at my original post. There are double the amount of pages talking about SQL in PHP than talking about SQL in Perl. However, there are NINE times the pages talking about SQL injection vulnerabilities in PHP than those talking about SQL injection vulnerabilities in Perl.

No! Not stored procedures!

[ttfkam]

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

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

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

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

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

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

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

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

Re:No! Not stored procedures!

[CastrTroy]

The person I was responding to was talking about using stored procedures specifically, I know a lot of people in this store are talking about prepared statements, but I was referring specifically to his comment about stored procedures. I think prepared statements are definitely the way to go. I never said that manually trying to escape everything was a good idea. In fact, it's a terrible Idea.

Re:Hooray for PHP!

[hey!]

The API is the language. Claiming otherwise is silly semantic games with no connection to the reality of the situation

I won't argue that most PHP programs I've seen are utter crap. But in software, semantics isn't a game. It's engineering. If you don't have words for distinctions, you don't have tools for fixing situations like this.

There are language oriented things that could be done in PHP to improve this situation, but short of incorporating the offending APIs into the language (e.g. so that SQL had to be compiled before interpretation), the simplest thing is to drop the offending APIs. It would break nearly every program written in PHP, but in a trivial way. And all those programs are broken anyway.

WTF?

[Anonymous Coward]

This is basic stuff that people learn 2 weeks into their first database driven website. Anybody who paid good money for a site that is susceptable to input validation flaws should be looking for a refund.

As a banking internet security guy . . .

[Kope]

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

God lord!

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

Not doing this SHOULD be criminal in my mind.

Stored procedures - happy scaling

[phooka.de]

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

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

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

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

Re:Stored procedures - happy scaling

[Anonymous Coward]

Unfourtunately, the SQL99-PSM store procedures standard isn't used by any DBMS yet. So, you're right that migration is work. The SQL99 standard will only gain ground *if* people use store procedures (the market at work). I agree here.

I do not agree, or understand, that SQL in a host application scales better than store procedures. Every SQL statement: SQL-text, prepared SQL, or a stored procedure *is* run on the DBMS server. N application servers talking to *one* DBMS server always has one SQL-engine doing the work. Increase N to your heart's desire, and it will still have only one SQL-engine to prepare, plan, and execute your SQL.

You gain SQL perfomance by reducing the network communication between the application and DBMS. Use prepared SQL to save that step on repeated items; only SELECT the needed columns or rows to save bandwidth; use stored procedures to modify data *without* passing it over the network. This last one is why I like stored procedures!

Re:Stored procedures - happy scaling

[wildBoar]

Insightful ? Which idiot modded this up.

It's just as likely that a company merges and finds that all the J2EE is redundant as the new company uses .NET .... BUT all that SQL99 compliant sql is still useful - or that both systems run Oracle or MySQL.

1 database ? Which planet are you from ? Why on earth do you think they invented 2 phase commit in 1990 or so ?

At the end of the day, if you need to write SQL and do anything remotely complex with it, then the best place (not to mention the easiest) to do that is the database.

Pity the stae-less nature of the web makes DB transactions almost impossible, as the db handles these much better than any apps server.

J2EE seems to be about manually cranking what Dbs have been doing automatically for years.

Once upon a time.

[sgt scrub]

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

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

Wanna know why there are so many SQL Injections?

[hobo sapiens]

Like I said, I'm not experienced with this stuff

No offense meant here, but if you are not experienced with this stuff, then do the following:
1) stop writing /. posts
2) stop writing code NOW
3) get a book from a reputable distributor like Wrox or O'Reilly
4) understand how to write code properly. Note my emphasis of the word "understand". This is more than simply hacking code together.
5) PROFIT!!! (ok, couldn't resist that one)

I am not a /. snob telling you what a hoosier you are for not having a CS degree. Just learn how to do things properly. This means taking time and forgoing instant the gratification of kludging together a website with little investment in your skills. This also means that you learn PHP or whatever you are using from reputable sources and not some "OMG!!~! Write PHP!" tutorial on happyhacker. Invest in yourself, and learn how to code correctly. Otherwise, you are one of the reasons for all of the SQL Injection attacks.

--
Why yes, I am a genius!

Not Everybody uses MySQL with PHP

[HighOrbit]

mysql_real_escape_string()

Everywhere I go to look about PHP security, I see mysql_real_escape_string() as a panacea. But I don't use MySQL, I use Oracle and PostgreSQL. So, mysql_real_escape_string() is not a fix-all for all php programers.

A few years ago (circa 2002 - php v4.x) , I did a rapid application in PHP4 for a demo. It was never really meant to be deployed but was just to give an idea to our managers about what we could do with our data. The backend was Oracle and I used the OCI8 API with no abstraction and it was strictly procedural. Now, several years latter, I've decided to make it deployable in php 5 and PEAR::DB. (As an aside, try reading your own spagetti code several years latter, luckily). So I am busy rewritting the thing by encapsulating some of the logic in PHP5 classes and thinking about security (which was not a part of the orgininal demo). I've scoured the web and O'Reilly safari for sources on php security. From what I've gleaned from various sources, it fighting SQL injection basically seems to come down to the following:

• Never accept data directly from $_POST or $_GET. Filter all input and allow only expected data, and then place the validated data into a "trusted" varible if it passes the validation filter. Nothing gets into the trusted variable unless it matches your filter criteria - if you are looking for a 5 character zipcode, then only allow a strlen of 5 with ctype_digit, otherwise raise an exception, discard the data, and kick the user back to the form with an error message.
• do not attempt to sanitize data that fails the validation, reject it outright.
• become expert at regex. It's the only effective way to filter for complex strings.
• Only insert/update into the DB with prepared statements, because a prepared statement is pre-compiled and does not allow the db interpreter to expand or evaluate the variable (which is the mechanism that is exploited by the sql injection).
• as you mentioned above, filter your output to prevent XSS. strip_tags() is useful but there are others like htmlentities()

I've checked out some of the validation classes such as pear::validate but I haven't found anything that really meets my needs. There are thousands of classes to validate email addresses, but fewer for specialized things like a National Motor Freight Conference Commodity, so I tend to fall back on writting my own functions with regex and ctypes and strlen.

Re:Not Everybody uses MySQL with PHP

[shaneh0]

The Ctype_ first-order functions leave much to be desired. For example, in PHP 5.0, ctype_digit("123456") != ctype_digit(123456) ... these issues led me to integrating my own validation code within my error handler classes. Just my $.02

Or just use prepared statements...

[psulonen]

...with the user inputs passed as input parameters.

No surprises - just an ad?

[Cre8ivEMasteR]

While it was an interesting read, the referenced articles did not present any earth shaking 'new' information other than overall numbers so...

Is it me or is the referenced article just an advertisement for how well they can tweak Network Intrusion Prevention Systems (N.I.P.S.?), or increase how tightly secured Host Intrusion Prevention Systems (H.I.P.S.) are?. ...and that both of these are necessarily a good thing? ;)

Not that 'techies' are in any way interested in that stuff...

Comment removed

[account_deleted]

Comment removed based on user account deletion

Editors?

[Anonymous Coward]

Seriously, if I had mod points today, I would have simply modded every post of yours down today,

I never get mod points anymore, but if ever there was a case for bitchslapping someone it's this. I've been mod-bombed before and its dumb because it usually does not have permanent effects and when it does "who cares?". Basically, modbombers waste the mod points they've been giving.

Check the IP address and strip this user now.

Concrete syntax idiocy

[l33td00d42]

It has always amazed me that people programmaticly build SQL queries using strings of concrete syntax! What the hell? Leverage your type system! It's there for a reason! Build an abstract syntax tree, and then let some library take care of communicating it to the database. Look, Mommy! No injections! (And it's not by "being careful".)

Re:Concrete syntax idiocy

[ErikZ]

The what system?

Re:Concrete syntax idiocy

[big dumb dog]

I agree.

I think that as people start to move away from the "data-driven" conscpt and begin to think in terms of "responsibility driven" design you will see more of this.

Re:Concrete syntax idiocy

[JetScootr]

I accidentally stumbled on a small town's website where the login HTML had the SQL itself in a javascript snippet. That's right, the entire "Select * from Users where userid=$forminput1 and password=$forminput2".
I considered sending them email, or even snailmail, advising them just how wide open their system was, but realized that people that ignorant would probably have me thrown in jail for "hacking" their website.
I agree that SQL injections seldom work against even a moderately competent programmer. Any bank that admits that hackers have suddenly been drawn to their site for this tells me that bank probably had/has some vulnerable spots that were discovered, and the hackers are trying to find more.

Re:Concrete syntax idiocy

[big dumb dog]

Ya, that's a tough dilemma. How much time and effort do you spend trying to educate someone who probibly doesn't care anyway?

Re:Concrete syntax idiocy

[JetScootr]

And it wasn't just one website, actually. I was trying to find non-Microsoft info (i.e., bug workaround/fixes) for the MS Access "dbconnect" or "connect" (i fergit) type parameters. I was trying to connect to an Access database from a webpage, and googled the parameter name I was having trouble with (DSN=, IIRC), and all these thousands of actual connect strings embedded in HTML came up, from thousands of websites.
It turns out some HTML generator (frontpage, maybe, or some other cheap tool) would generate the string when the user/"website developer" would "design" (clickdrag) an access database to a webpage being built.
And since the password in the connect strings to Access are unencrypted....
hoooh boy. "Trustworthy computing", indeed.

Re:Wanna know why there are so many SQL Injections

[KIFulgore]

Good insights. I remember my jaw hitting the floor when I picked up an actual book on C++ and not learning from Joe Script Kiddie website #90210. Funny how sites never tell you about important stuff like implicit copy constructor invocation which, by the way, seriously tears up jack if you're dynamically allocating memory.

The difference in books vs. websites is night-and-day, most sites assume a certain level of proficiency. Books teach things, well, by the book. From the beginning.

Also, it's not hard to find PDF versions of very good SQL books online.

Do your job.

[generationxyu]

It's not C's job to protect you from using strcpy(buffer_on_stack, untrusted_input), and it's not PHP, Perl, MySQL, or your database layer's job to protect you from "SELECT * FROM table WHERE name = " . $name. You're a programmer. Do your job. If you don't know what something does, don't do it.

PHP/Python/Perl is one thing

[Anonymous Coward]

But in Classic Visual Basic ASP, other than HTMLEncode() and custom string parsing, what other methods are there to protect your data from SQL Injection Attacks?

Testing for SQL Injection

[alfredw]

Hey /.,

I'm a tester, and we check all of our applications for SQL Injection as a matter of course. I've written an in-house crawler/injector that does a blind attack against a site (no, I will not give it to you, script kiddies). This is OK, but I would prefer an open source / free tool of some kind that is a bit less "user unfriendly" and a bit better maintained.

Does anyone know of one?

Alf

Re:Injection preventation doesn't need input check

[wickning1]

PHP's mysqli extension supports paramaterized SQL, but it is still not in wide use. The majority of web servers still don't even have mysqli installed, and most programmers stick with the old extension out of habit anyway. Not to mention the huge base of code already written that's being exploited every day.

Even in mysqli, the process is not very elegant. I work in PHP but the first thing I ever did in the language was write a database framework that works similarly to DBI (as implemented in Perl), I would totally hate PHP if I didn't have it.

The PHP devs also decided to create the craptacular "magic quotes" feature, which I'm sure seemed to be a good idea at the time, but in retrospect stalled real web security awareness for years. So yeah, PHP is a big culprit here.

Mod parent up

[uqbar]

These are all good points. Add in the mess your code becomes when, rather than using good OOAD, you instead do everything in proceedural code (even Java stored procs affect a proceedural style).

Neat separation of layers for UI, Business and Data Access is pretty much manditory for any large scale application - anything else and you are creating unmaintainable, unreusable, spagetti that will not work well in an SOA. Stored Procs won't get you there.

Learn to bind your variables or use Hibernate or use any one of a zillion other techniques besides using stored procs. Don't use them for anything except simple CRUD operations.

Perl DBI is pretty good, actually

[Anonymous Coward]

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

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

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

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

Re:Perl DBI is pretty good, actually

[brunson]

So does any decent API, and it is the API, not the language. Perl still sucks for so many other reasons.

Re:Injection preventation doesn't need input check

[KingMotley]

While I agree PEAR is a good way to prevent such attacks, your statement "PHP supports parameterized SQL just as well as any other language I've worked with" is a bit misleading. PEAR is not part of PHP, it's a class library that runs on top of the PHP framework. As such, the mysql interface class (The one that supports PHP 3.x), does NOT have parameterized queries at all.

That is not FUD, it's the truth, and yes, there are workarounds, and fixes, but that's still a big hole unless you use the magic_quotes_runtime, which causes it's own issues.

Just use mod_security!

[wickning1]

For all your anti-script kiddie needs! Just don't EVER type the words "insert" and "select" in your forum post. Or "insert" and "into". Or "update" and "set".

True story. Took me hours to figure out why people were complaining about missing posts.

Wow I hope this gets through /.'s mod_security rules. :P

using simple

[geekoid]

software techniques prevents this from happening.

Any "webmaster" that creats a page that allows SQL injection should be terminated on the spot.

well

[geekoid]

one would assume 'carefull' means researching what you are doing, and then implementing it. AS opposed to gently pressing on the keys trying to avoind repetitive stress injury.

Re:well

[jc42]

one would assume 'carefull' means researching what you are doing, ...

One might assume that, but one would often be wrong.

I've found that, when a management type asks why something is taking so long, "We're trying to be very careful" is invariably accepted as an explanation. This implies that "careful" indeed implies time to at least a good many people.

Problems with computer security are almost always due to users' (and admins') lack of knowledge of potential problems. The only real solution to such problems is research and study of what is known of the problem.

Unfortunatelly, there are a lot of roadblocks to achieving true enlightenment. And most of the roadblocks are intentionally placed there by those with knowledge. You can see this by reading just about any discussion of how to deal with new security exploits. Inevitably, a good part of the discussion is over how much information should be released to the general public. And the people who have the information usually argue "If I released my information, all the evil hackers in the world would use it."

So they keep the information secret, and they do so intentionally. The main effect of this on you and me is that our own research is stymied. We can't determine whether our computers are susceptible, or if they are, what we should be doing to block exploits.

Very often we hear the advice to "Be careful" but we don't find the information that we need to do anything carefully. Such advice is a form of blaming the victim: "I told you to be careful, but your machine was infected. You dummy; you should have been more careful. It's not my fault, because I warned you."

But warning people of a vague, unspecified problem without including information on what to look out for isn't usually very helpful at all. Your response to "Be careful" should be something like "You turkey; at least give me a clue as to what I should be looking out for."

When someone tells you "Be careful", you should listen carefully to see what useful information they include. If there's none, they you should understand that their warning was merely at attempt to shift the blame to you.

Re:Injection preventation doesn't need input check

[geekoid]

Misinformation does not equal FUD.

Parameterized SQL

[greenkite71]

Enough said. Jon

Web Application Firewalls

[Anonymous Coward]

While it's no cover for good app design, I don't worry to much about this attack after implementing a Web App firewall from Teros/Citrix. :)

And I know several banks in town have too.

Re:Injection preventation doesn't need input check

[julesh]

I think misinformation that causes people to believe that using a particular type of system will make it harder to have a secure system is FUD: it will instill fear, create uncertainty and cause doubt. Whether it was intended to do so or not, I don't know. Given the anonymous nature of the original poster, I'm not inclined to give him the benefit of the doubt.

P*ssing contest

[Anonymous Coward]

I hate these topics on slashdot. Typically Im a lurker, hence no account. But i have to say, that it amuses me how each one of you has "The Best Solution"(TM). There are many ways to do something, and not all of them are bad. I will only agree that consideration for injection attacks should be taken when writing code, but clamouring on about whats the best method just proves to me that the developers on slashdot are ego maniacs and can usually be proven wrong by someone else.

Re:Hooray for PHP!

[Anonymous Coward]

And if you were using PostgreSQL, you'd probably use pg_(send_)query_params() instead...

Re:Injection preventation doesn't need input check

[julesh]

PEAR is not part of PHP, it's a class library that runs on top of the PHP framework.

PEAR is distributed with PHP, and is installed along with it unless you specifically run ./configure --without-pear. You might as well argue that C++ doesn't support parameterized queries, cause, you know, ODBC et al aren't part of the core language. Or C doesn't, because the API library that's supplied with MySQL doesn't. Besides: if you're running any database other than MySQL, you get parameterized queries in the standard library. The reason for this is because the standard library consists mostly of direct conversions of vendor-provided APIs to the nearest possible PHP equivalent. The MySQL C API doesn't support the feature, so the MySQL PHP API doesn't. The Oracle one does. The Postgres one does. The Sybase one does. And so on.

The fact is that there is a well known, commonly-used, officially sanctioned solution to this. The fact that it's an object oriented solution implemented in PHP rather than a procedural one implemented in C like all the stuff in the standard library doesn't matter.

As such, the mysql interface class (The one that supports PHP 3.x)

PHP 4 was released over six years ago. PHP 3 was the current version for only two years prior to that. Support for PHP 3 is no longer available; security fixes are no longer applied to it by the PHP developers, who haven't been accepting new bug reports for that version for over a year now. Using it would probably be suicidal, as it included a large number of design decisions that are now widely believed to lead to insecure applications in many situations. Why is compatibility with PHP 3 such an issue?

Article is really a big Sales ad

[jroysdon]

From the article:

"SecureWorks announced ..." Including a link to SecureWorks.

A Network Intrusion Prevention System and Host Intrusion Prevention System can offer many of these protections, especially if they are being monitored by a 24x7x365 security team that can stay on top of the newest types of SQL Injection attacks, as there are new variances being released all the time."

Interesting that this is SecureWorks business - doing 24/7 monitoring. So, you can skip fixing your apps so long as you hire SecureWorks, is basically what the last paragraph is saying.

Try mod_security

[kap1]

Sometimes its not feasible to shut down an application because its vulnerable to spam or sql injection. The Apache mod_security module can provide a stopgap until you can fix the app. Probably a good idea to have it in place in any case.

What moron modded this up?

[Anonymous Coward]

It is completely wrong. The database bindings for a language should do this for you. Just because PHP is garbage and makes you manually *_escape_string() everything, doesn't mean that's ok. Decent languages let you pass params and have them auto-escaped.

That said

[Vexorian]

It is also hard to believe that XSS vulnerabilities are so frequent, even google had a couple of them (solved the same day they were announced though)

mysqli

[Anonymous Coward]

Hmm did some research on this and turns out PHP 6 is doing away with gpc_magic_quotes() so you should switch to mysqli_* instead of mysql_* it was introduced in PHP 5 and requires mysql 4.1 or better. Kinda sucks I'll have to redo all my database code but escape quotes wasn't cross database compatible anyway since postgres apparently uses a single quote instead of a backslash to escape.

I didn't say that!

[Ender Ryan]

Don't put words in my mouth. I never said you should use/not use PHP, or Perl for that matter.

What I DID say, however, is that anyone who disparages Perl, while at the same time using PHP, doesn't have any idea what they're talking about.

It has NOTHING to do with elitism, and everything to do with using tools properly. Most PHP devs don't know what they're doing, but that doesn't mean there aren't competent devs using PHP too.

Re:I didn't say that!

[Anonymous Coward]

What I DID say, however, is that anyone who disparages Perl, while at the same time using PHP, doesn't have any idea what they're talking about.

That's not what you said either. It might be what you meant, but it's not what you said. This is the comment I took exception to (for the record, I'm not the author of the rant that you originally replied to):

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

I have skipped PERL entirely (not exactly "skipped", because I never planned on it in the first place, and it definately wasn't a part of the college curriculum), but I'm completely competent with PHP, and with programming in general. I've simply never studied PERL, I've never had a need to. I might some day, but I haven't had the need. And so, I took exception to your comment, because that, combined with your disparaging remarks about PHP being composed of only the bad parts of PERL, made it sound elitist, as if PHP developers who have not studied PERL don't know what they're doing. It's true that a lot of PHP developers don't know what they're doing, but that's true with any language, and with programming in general.

Re:I didn't say that!

[Ender Ryan]

Well I thought it would be understood in the context of what I was replying to. And for the record, all languages have problems, mostly very serious. And I'm not even a "language bigot." There's no denying it, no matter what languages you know and love.

And for the love of Dog, it is not an acronym! Stop saying PERL! Perl is the language, and perl is the interpreter. From perlfaq1. :)

Cheers.

You think you are safe, with your function? Wrong!

[xsuchy]

For all my fields I use a simple function to ensure that the data being put into the query is safe for the query (Replace(foobar, "'", "''") - for SQL Server).

And what about this input?:
foo\'; delete from user;--
which in query
SELECT id FROM user WHERE name='$input'
your function turn into:
SELECT id FROM user WHERE name='foo\''; delete from user; --'

Do you sleep well now?

I thought it had something to do with MySQL...

[lightspawn]

since I speed-misread the title as "SQL injunction attacks".

Re:You think you are safe, with your function? Wro

[pookemon]

I sleep very well. That query doesn't do anything - Unless I have a user who's name is 'foo\''; delete from user; --'

Try typing the query into Query Analyser and see what it does (like I said - nothing).

Maybe this'd work on Oracle or mySQL but on SQL Server (as I said in my original post) it's perfectly safe. If you're using a database that allows you to interupt a string like that then you should change you're database. I suspect, though that the correct output for your example would have been:

select id from user WHERE name='foo\\'; delete from users; --'

And the function you would be using on your database would be:

REPLACE(foobar, ''', '\'')

ie. Your escape sequence is different to the SQL Server escape sequence.

== IT competence decreasing

[runcible]

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

' or 1 = 1 --

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

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

_shakes head_

Its a pity...

[dcam]

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

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

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

Perl MVC Frameworks

[holy zarquon's singi]

and with products like Catalyst and Jifty getting even easier and flexibler.

never trust the data

[Monkier]

yes.. never trust the data. and know all the data that needs to be validated. form fields, cookies, referer, useragent, and one I _bet_ web developers are ignoring - Accept-Language (in IE - Tools > Options > Languages > Add.. > type whatever you want).

if you are using any of these to build up some dynamic SQL - VALIDATE YOUR INPUT! or as other posters have mentioned don't use dynamic SQL, use Stored Procedures and Parameterized Queries...

Re:Injection preventation doesn't need input check

[KingMotley]

My bad, I meant MySQL 3.x not PHP 3.x. A lot of web hosting providers STILL haven't moved to MySQL 4.x yet.

A large number of hosting providers also don't give you access to PEAR either. If you want it, you have to go and get it yourself. As for C and C++ supporting parameterized queries, that's a different argument. Neither of those are really a web framework, and neither of those include database support at all in the core. However, on the windows platform, ODBC is the standard and most accessable way of accessing databases from those languages, and as such is what is most commonly used, and most likely the way a beginner would use. In addition, many (All 2 of the ones I've worked on) projects out there don't use PEAR, and they have their own database abstraction layer, which is something the other frameworks don't need. Most of the big frameworks have a default database implementation that abstracts atleast some of the differences between different database backends, or atleast implement APIs that are similiar (although maybe different classes) for different backends, which PHP doesn't.

The most common scenario for PHP with a database is accessing MySQL 3.x, and on a shared hosting provider. And in that scenario, PHP does not allow parameterized queries. Yes, you can use someone's database abstraction layer to emulate that functionality for you, but then again the same thing could be said about ANY language. It's a workaround to add a feature that other web frameworks don't suffer from.

ASP.NET supports parameterized queries to SQL Server, which is it's most common database backend (It also supports it for MySQL including 3.x versions, Oracle, and any ADO/ODBC database).

I do believe java has support natively as well.

To be quite honest, I would love to see the baseline PEAR packages moved over to the PHP core. But even that won't solve the problem today in trying to help beginners not make insecure web applications. And you can still circumvent the parameterized query API calls by doing string concatenation for your queries, but that's a different subject.

For those too lazy to read tfa

[uptoeleven]

For those too lazy to read the article, here's what you gotta do to secure your sql:

Principle: Never trust user input
Implementation Validate all textbox entries using validation controls, regular expressions, code, and so on

Principle: Never use dynamic SQL
Implementation: Use parameterized SQL or stored procedures

Principle: Never connect to a database using an admin-level account
Implementation: Use a limited access account to connect to the database

Principle: Don't store secrets in plain text
Implementation: Encrypt or hash passwords and other sensitive data; you should also encrypt connection strings

Principle: Exceptions should divulge minimal information
Implementation: Don't reveal too much information in error messages; display minimal information in the event of unhandled error; set debug to false

I thought there'd be some crazy-mad stuff you'd need to do to protect against SQL injection but it seems that decent programming techniques and the application of sanity is all that's required, panic over then?

Using COALESCE function in MSSQL (cleaner code)

[aw00d]

Instead of this:

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

Try COALESCE, it is cleaner, and you don't need to worry what happens if your code is still in use 44 years from now.

create procedure myTestProc @someDateTime datetime = NULL as
--put insert, delete, update here....
select * from someTable
where (@someDateTime >= COALESCE(@someDateTime, someTable.someDate)

Docs are here:
http://msdn.microsoft.com/library/default.asp?url= /library/en-us/tsqlref/ts_ca-co_9dph.asp