Beta

Slashdot: News for Nerds

×

Welcome to the Slashdot Beta site -- learn more here. Use the link in the footer or click here to return to the Classic version of Slashdot.

Thank you!

Before you choose to head back to the Classic look of the site, we'd appreciate it if you share your thoughts on the Beta; your feedback is what drives our ongoing development.

Beta is different and we value you taking the time to try it out. Please take a look at the changes we've made in Beta and  learn more about it. Thanks for reading, and for making the site better!

SQL Injection Attacks Increasing

CmdrTaco posted about 8 years ago | from the so-easy-to-miss dept.

384

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

cancel ×

384 comments

Another web developer pain... (5, Funny)

Anonymous Coward | about 8 years ago | (#15742456)

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

Article text, server already slow (-1)

Anonymous Coward | about 8 years ago | (#15742461)

SQL injection attacks against banks on the rise
Posted on 19 July 2006.

SecureWorks announced that it has seen a 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. "From January through March, we blocked anywhere from 100 to 200 SQL Injection attacks per day," said SecureWorks CTO Jon Ramsey. "As of April, we have seen that number jump from 1,000 to 4,000 to 8,000 per day," said Ramsey.

"The majority of the attacks are coming from overseas," said Ramsey. "And although we certainly see a higher volume with other types of attacks, what makes the SQL Injection exploits so worrisome is that they are often indicative of a targeted attack." This is a type of attack where the hacker has targeted a particular organization, versus a worm which spreads indiscriminately.

"Depending on the sophistication of the attacker, the online criminal can potentially gain access to a bank or utility company's key customer databases containing social security numbers, account numbers, credit card numbers, email addresses, etc," continued Ramsey.

SQL Injection is a type of security exploit in which the attacker adds Structured Query Language (SQL) code to a Web form input box to gain access to an organization's resources or to make changes to data. Using this technique, hackers can determine the structure and location of key databases and can download the database or compromise the database server. "What makes this vulnerability so pervasive is that SQL Injection attacks can prey on all types of Web applications - even those as simple as a monthly loan payment calculator or a 'signup for our customer newsletter' form," said Ramsey.

"SQL Injection is successful only when the web application is not sufficiently secured," said Ramsey. "Unfortunately, the majority of websites and web applications are not secure. Thus, we are advising all organizations to use 'input validation' for any form to ensure that only the type of input that is expected is accepted."

Additionally, it is important to note that protecting against a SQL Injection attack also requires organizations to not only protect their web applications but also the web server on which the web application is running, the database from which the web application is retrieving information, and the operating systems upon which the web servers, applications and database reside.

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.

How difficult is it. (4, Informative)

El_Muerte_TDS (592157) | about 8 years ago | (#15742465)

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. (2, Informative)

Goaway (82658) | about 8 years ago | (#15742489)

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. (0, Redundant)

eggoeater (704775) | about 8 years ago | (#15742520)

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. (1, Troll)

Goaway (82658) | about 8 years ago | (#15742559)

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. (4, Insightful)

Anonymous Coward | about 8 years ago | (#15742649)

"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. (4, Informative)

aymanh (892834) | about 8 years ago | (#15742526)

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

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

Goaway (82658) | about 8 years ago | (#15742550)

"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. (2, Informative)

aymanh (892834) | about 8 years ago | (#15742609)

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 [slashdot.org] (Or a similar book/online reference) before using PHP, otherwise there is a very good chance they'd end up with vulnerable code.

Re:How difficult is it. (1)

Spliffster (755587) | about 8 years ago | (#15742637)

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

arivanov (12034) | about 8 years ago | (#15742573)

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

plague3106 (71849) | about 8 years ago | (#15742631)

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

plague3106 (71849) | about 8 years ago | (#15742619)

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. (4, Informative)

eggoeater (704775) | about 8 years ago | (#15742499)

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

CastrTroy (595695) | about 8 years ago | (#15742599)

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. (4, Informative)

beavis88 (25983) | about 8 years ago | (#15742630)

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

Anonymous Coward | about 8 years ago | (#15742636)

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

plague3106 (71849) | about 8 years ago | (#15742646)

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. (4, Informative)

CaptainZapp (182233) | about 8 years ago | (#15742648)

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. (2, Informative)

(trb001) (224998) | about 8 years ago | (#15742615)

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

PuddyT (853459) | about 8 years ago | (#15742673)

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 [sommarskog.se] 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. (1)

SQLGuru (980662) | about 8 years ago | (#15742659)

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

No no No no No no NO (5, Informative)

IPFreely (47576) | about 8 years ago | (#15742552)

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.

mod parent up (0)

Anonymous Coward | about 8 years ago | (#15742581)

this approach seems more correct than escaping strings.

Thanks! (1)

DahGhostfacedFiddlah (470393) | about 8 years ago | (#15742677)

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.

Re:How difficult is it. (2, Informative)

hey! (33014) | about 8 years ago | (#15742708)

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

Injection preventation doesn't need input checking (2, Insightful)

Killeri (238792) | about 8 years ago | (#15742472)

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

Re:Injection preventation doesn't need input check (1)

Gr8Apes (679165) | about 8 years ago | (#15742497)

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.

Re:Injection preventation doesn't need input check (1)

lbmouse (473316) | about 8 years ago | (#15742549)

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

Here is an article for beginners [securiteam.com] about SQL injection hacks.

Re:Injection preventation doesn't need input check (1)

Otter Escaping North (945051) | about 8 years ago | (#15742612)

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.

Re:Injection preventation doesn't need input check (0)

Anonymous Coward | about 8 years ago | (#15742686)

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

that would be PHP.

see the problem now?

Qualifications (5, Interesting)

Chris Graham (942108) | about 8 years ago | (#15742474)

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 (1)

cnettel (836611) | about 8 years ago | (#15742482)

Politicians don't.

Re:Qualifications (2, Insightful)

Chris Graham (942108) | about 8 years ago | (#15742590)

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 (1)

cnettel (836611) | about 8 years ago | (#15742663)

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 (1)

sshutt (785646) | about 8 years ago | (#15742553)

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 (0)

plague3106 (71849) | about 8 years ago | (#15742694)

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?

Checking input is a "pain in the ass"?!? (5, Insightful)

fractalus (322043) | about 8 years ago | (#15742478)

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"?!? (2, Informative)

Bogtha (906264) | about 8 years ago | (#15742548)

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"?!? (1, Insightful)

tgd (2822) | about 8 years ago | (#15742588)

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"?!? (1)

oyenstikker (536040) | about 8 years ago | (#15742707)

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"?!? (2, Interesting)

gutnor (872759) | about 8 years ago | (#15742616)

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.

Hooray for PHP! (-1, Troll)

Goaway (82658) | about 8 years ago | (#15742480)

You have to be very careful about checking user input.

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.

Re:Hooray for PHP! (1, Offtopic)

Goaway (82658) | about 8 years ago | (#15742530)

Furhtermore, note:

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

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

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

http://www.google.com/search?q=%22sql+injection%22 +vulnerability+php [google.com]
"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.

Re:Hooray for PHP! (0)

Anonymous Coward | about 8 years ago | (#15742533)

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

Re:Hooray for PHP! (1)

nxtw (866177) | about 8 years ago | (#15742576)

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.

Re:Hooray for PHP! (1)

Goaway (82658) | about 8 years ago | (#15742591)

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! (3, Insightful)

baadger (764884) | about 8 years ago | (#15742555)

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

Re:Hooray for PHP! (2, Interesting)

Goaway (82658) | about 8 years ago | (#15742603)

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! (0)

Anonymous Coward | about 8 years ago | (#15742604)

u twatfaced fuck-wuppit.

Re:Hooray for PHP! (1)

naich (781425) | about 8 years ago | (#15742632)

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

Hard for Devs? (3, Insightful)

CHR1S (694833) | about 8 years ago | (#15742484)

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? (5, Interesting)

Goaway (82658) | about 8 years ago | (#15742507)

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? (0)

Anonymous Coward | about 8 years ago | (#15742545)

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

Re:Hard for Devs? (1)

CHR1S (694833) | about 8 years ago | (#15742546)

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? (1)

Goaway (82658) | about 8 years ago | (#15742578)

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? (1)

CHR1S (694833) | about 8 years ago | (#15742620)

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? (3, Informative)

Bogtha (906264) | about 8 years ago | (#15742611)

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? (2, Informative)

phasm42 (588479) | about 8 years ago | (#15742623)

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

Here's an example of parameterized queries in Java:
PreparedStatement ps = connection.prepareStatement("insert into USERS_LIST (USER_ID, USER_NAME) values (USER_ID_SEQ.nextval, ?)";
ps.setString (1, userName);
boolean status = (ps.executeUpdate() == 1);
Need to insert more? Reuse the prepared statement
for (String userName : users)
{
ps.setString(1, userName);
numBad += (ps.executeUpdate() == 1)?0:1;
}

A pain for who exactly? (2, Interesting)

MosesJones (55544) | about 8 years ago | (#15742487)

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

aymanh (892834) | about 8 years ago | (#15742491)

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 [php.net] for PHP, developers just need to make use of them.

Re:Interesting... (1)

RalphSleigh (899929) | about 8 years ago | (#15742524)

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

Re:Interesting... (1)

aymanh (892834) | about 8 years ago | (#15742551)

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

Re:Interesting... (1)

BalanceOfJudgement (962905) | about 8 years ago | (#15742560)

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

baadger (764884) | about 8 years ago | (#15742601)

So...change your web host?

Parameters and Stored Procedures (0)

Anonymous Coward | about 8 years ago | (#15742504)

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

Input checking is a half-assed solution. (3, Interesting)

cduffy (652) | about 8 years ago | (#15742508)

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

celotil (972236) | about 8 years ago | (#15742509)

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

celotil (972236) | about 8 years ago | (#15742522)

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... (2, Insightful)

hawkinspeter (831501) | about 8 years ago | (#15742561)

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

celotil (972236) | about 8 years ago | (#15742665)

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

phasm42 (588479) | about 8 years ago | (#15742696)

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... (4, Insightful)

LeRandy (937290) | about 8 years ago | (#15742682)

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

Virak (897071) | about 8 years ago | (#15742688)

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?

Use PreparedStatements with Java (4, Insightful)

sbrown123 (229895) | about 8 years ago | (#15742514)

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 (1)

CastrTroy (595695) | about 8 years ago | (#15742569)

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 (3, Informative)

IPFreely (47576) | about 8 years ago | (#15742579)

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 (1)

tedgyz (515156) | about 8 years ago | (#15742580)

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

Re:Use PreparedStatements with Java (4, Informative)

(trb001) (224998) | about 8 years ago | (#15742596)

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 (1)

phasm42 (588479) | about 8 years ago | (#15742660)

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

Pain for web developers? (1)

kjart (941720) | about 8 years ago | (#15742521)

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.

serious question (1)

CDPatten (907182) | about 8 years ago | (#15742532)

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 (2, Interesting)

cnettel (836611) | about 8 years ago | (#15742607)

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 (1)

TubeSteak (669689) | about 8 years ago | (#15742643)

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.

No. (1)

iabervon (1971) | about 8 years ago | (#15742656)

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

Re:serious question (1)

Bogtha (906264) | about 8 years ago | (#15742669)

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.

Solution for PHP programmers (2, Informative)

ylikone (589264) | about 8 years ago | (#15742538)

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 (3, Insightful)

Bogtha (906264) | about 8 years ago | (#15742699)

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.

Stored procs (1, Funny)

Anonymous Coward | about 8 years ago | (#15742565)

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

Re:Stored procs (1)

wetelectric (956671) | about 8 years ago | (#15742670)

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.

It's worth mentioning... (1)

flibuste (523578) | about 8 years ago | (#15742628)

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?

Egad, Ads! (1)

cakefart (979626) | about 8 years ago | (#15742640)

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" (4, Insightful)

jc42 (318812) | about 8 years ago | (#15742654)

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.

mysql_real_escape_string (1)

The MAZZTer (911996) | about 8 years ago | (#15742658)

Just stick it [php.net] 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.

Multi-tiered approach (3, Insightful)

Billosaur (927319) | about 8 years ago | (#15742668)

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

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

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

Dynamic 'WHERE' clauses (5, Informative)

TheRealBurKaZoiD (920500) | about 8 years ago | (#15742681)

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 [cmu.edu] (pops in a new window)

this doesn't match my anecdotal evidence... (2, Interesting)

JeanBaptiste (537955) | about 8 years ago | (#15742685)

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

Have to be very careful about checking user input? (1)

mrjb (547783) | about 8 years ago | (#15742701)

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 (4, Funny)

digitaldc (879047) | about 8 years ago | (#15742706)

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

Need an Account?

Forgot your password?

Don't worry, we never post anything without your permission.

Submission Text Formatting Tips

We support a small subset of HTML, namely these tags:

  • b
  • i
  • p
  • br
  • a
  • ol
  • ul
  • li
  • dl
  • dt
  • dd
  • em
  • strong
  • tt
  • blockquote
  • div
  • quote
  • ecode

"ecode" can be used for code snippets, for example:

<ecode>    while(1) { do_something(); } </ecode>
Create a Slashdot Account

Loading...