|
PedroMC wrote: Sanitizing data is enough to avoid SQL injections. That has always been available and it usually is trivial to do.
Actually, it is far from trivial. The only 100% reliable way to do this is to have exactly the same SQL parser as your DBMS, run your assembled queries through it and make sure that it does whatever you intended it to do.
Parameterized queries are the only practical protection from SQL injections.
PedroMC wrote: Not every, but SQL is far from being the only (e.g. buffer overflows).
That's a very good observation. Programming techniques that introduce stack overflow are today recognized as dangerous and good C compilers even emit warnings for them. Assembling SQL queries "on fly" and including users' input in them is exactly one such technique (except that it is much easier to exploit) and should be avoided.
modified on Thursday, November 13, 2008 12:23 PM
|
|
|
|
|
Nemanja Trifunovic wrote: The only 100% reliable way to do this is to have exactly the same SQL parser as your DBMS, run your assembled queries through it and make sure that it does whatever you intended it to do.
It is the first time I have heard about it. From all I have read about securing SQL, even from the mouth of several horses (a.k.a. servers developers and documentation), sanitizing all data (e.g. using the function(s) provided by the server API) that goes in to the query is 100% effective. I'm curious about how could a SQL attack be accomplished if all data is sanitized.
Note that I'm not considering bugs in the SQL parser or server itself. Those have little to do about SQL security.
Regards.
|
|
|
|
|
PedroMC wrote: From all I have read about securing SQL, even from the mouth of several horses (a.k.a. servers developers and documentation), sanitizing all data (e.g. using the function(s) provided by the server API) that goes in to the query is 100% effective. I'm curious about how could a SQL attack be accomplished if all data is sanitized.
And how do you "sanitize" data reliably if it does not pass through the same parser that will ultimatelly proces your SQL query? Even if you do that (for instance, if the dbms provides such API) it still requires the discipline on the cient programmer's side to sanitize each and every user input and that is simply unrealistic - most application developers simply never do that and never will.
The only sane way to pass data is to use precompiled (a.k.a. parametarised) queries that will treat your text data just as plain text and never try to execute it. For more detailed info, see This Stackoverflow.com thread[^] and even better, this CP article[^]
[Edit] - Another excellent article on the topic[^]
|
|
|
|
|
Nemanja Trifunovic wrote: And how do you "sanitize" data reliably if it does not pass through the same parser that will ultimatelly proces your SQL query?
Simple MS is the only oddball because they're the retards that like to not follow standards. If you're connecting to SQL Server, use "" or '' and everything else in the world use \" or \'. Problem solved. This assumes you do validate your input and don't accept something like "SELECT * FROM blah;" as an age field.
|
|
|
|
|
Jeremy Falcon wrote: If you're connecting to SQL Server, use "" or '' and everything else in the world use \" or \'. Problem solved.
Go post your "solution" on any serious security-related forum and watch the people laugh.
|
|
|
|
|
Nemanja Trifunovic wrote: Go post your "solution" on any serious security-related forum and watch the people laugh.
Read the rest of my post, and don't quote just one part of it. You should always validate your data. This is a duh, but that's irrespective of SQL. You're blaming SQL for something that's not its fault.
|
|
|
|
|
Nemanja Trifunovic wrote: And how do you "sanitize" data reliably if it does not pass through the same parser that will ultimatelly proces your SQL query?
Using the facilities provided by the server API and there is nothing special or complex about it. In fact, it amounts to escape a few characters with structural significance for the SQL parser.
Nemanja Trifunovic wrote: it still requires the discipline on the client programmer's side to sanitize each and every user input and that is simply unrealistic
Unrealistic why?! If a programmer is incapable of validating the data is program will be working on then maybe (s)he should be looking for another occupation.
Any and all data should be validated (at least once). I always validate each and every user, file, network, whatever data input. I always validate each and every data that goes in to a database (using direct SQL, parametrized SQL, API, whatever). I almost always validate (assert) function parameters.
Security is one reason for all this validation but structuring the programs behaviour in my head, reducing bugs and simplifying bug hunting are also strong reasons for it.
Regards.
|
|
|
|
|
Nemanja Trifunovic wrote: Assembling SQL queries "on fly" and including users' input in them is exactly one such technique (except that it is much easier to exploit) and should be avoided.
It's not easier to exploit. Have you tried it? Seriously, go find an app you didn't write and attempt a SQL injection attack on it. Finding the vulnerability and making it valid can be quite challenging. It's pretty close to finding a buffer overrun one.
And, not all compilers do catch those. Not to mention most issue warnings for them unless on level 4, which means you can still get away with them in C++ just like someone who doesn't know SQL still can. Using your logic we should start blaming C++ rather than the programmer too.
|
|
|
|
|
Nemanja Trifunovic wrote: Actually, it is far from trivial.
Sorry, but it's trivial when constructing a SQL string from user input that's validated. If you've ever done a search and replace for delimiters then after you validate input (which you should anyway) you're 90% there.
|
|
|
|
|
|
Nemanja Trifunovic wrote: I am really tired of arguing, but it is so easy to find good information on the Internet nowdays...[^]
You still ignore the validation point that's not the fault of SQL. So, assuming all fields are validated, then that will work. If you don't believe me, read your own link...
The reason I have not yet accepted any of the answers is that I still don't know of any way to effectively launch a SQL injection attack against this code.
And of course, he's right.
|
|
|
|
|
Jeremy Falcon wrote: And of course, he's right.
And of course, he never bothered to read this link from the post with the highest ranking[^] (pay special atention to the Unicode part). The conclusion:
This paper proves that an application may be vulnerable to SQL Injection
attacks, even though it does proper input validation before calling a stored procedure,
in contrast to conventional wisdom that input validation is sufficient to protect an application
against SQL Injection.
Jeremy Falcon wrote: So, assuming all fields are validated, then that will work.
Repeating myself again...
1) It is not possible to reliably validate fields if they are going into an SQL statement
2) Even if it was possible, it is too much work and no-one is doing it
From 1) and 2) => Concatinating strings to make SQL queries is evil. Never, ever do that. Or if you do, don't say I didn't warn you 
|
|
|
|
|
Nemanja Trifunovic wrote: This paper proves that an application may be vulnerable to SQL Injection
attacks, even though it does proper input validation before calling a stored procedure,
in contrast to conventional wisdom that input validation is sufficient to protect an application
against SQL Injection.
I think you need to read that PDF. Its whole argument is Unicode translation, which any clown knows the difference between VARCHAR and NVARCHAR. Let me quote page 7...
Even if the application calls the SP properly, using parameter objects etc., the input will be
passed to the SP in a varchar variable, which typically does not support Unicode values; an
affected database server will promptly translate this character to a standard quote, breaking
the WHERE clause and causing the dynamic execution to execute whatever arbitrary code was
included in the malicious parameter.
And it's translated when passed so it can be stored in the variable, therefore you can test it in the SP as well to eliminate this if you can't fathom keeping your code in the same charset. So, you *can* double check the input inside the SP and problem solved - which that PDF did not do. Any decent defensive programmer should know this.
And on top of this, any application accepting input in Unicode and doesn't use NVARCHAR is probably someone who's used to programming in VB. Once again, if you have a clue about SQL, protecting it is trivial. And, in your link's example, assuming it's non-Unicode (which is the case) there is no translation involved and it would still work because even in PHP you can't reliably fit a Unicode string inside an ANSI string outside of the first 127 characters without some serious problems that should be caught by the validation routine anyway.
Point is, in that PHP example, that string check is not a Unicode one. And even if it was, checking the SP too would avoid the PDF's point. It's pretty simple to do IMO.
I need this weekend, anyway, that's VB not PHP, and VB stores all its strings as Unicode anyway. So if someone stuffs a Unicode string into a VARCHAR, well, they're VB programmers what can I say.
Nemanja Trifunovic wrote: Repeating myself again...
1) It is not possible to reliably validate fields if they are going into an SQL statement
2) Even if it was possible, it is too much work and no-one is doing it
Repeating myself again... it is and its easy.
|
|
|
|
|
Nemanja Trifunovic wrote: That is what makes "raw" SQL a major source of security problems.
That's what makes lack of knowledge about SQL and programming the source of security problems. According to your logic, C++ must suck too because you can create buffer overrun in it if you don't know what you're doing.
|
|
|
|
|
|
I addressed your answer already too. 
|
|
|
|
|
PedroMC wrote: it's not that bad!
Agreed.
|
|
|
|
|
PedroMC wrote: seeing the amount of effort that goes in to hiding it
This may be because the major DBMS each have their own SQL dialects.
In my last project, we wrote an application that was designed to interact with any database whatsoever. The project lead (which wasn't I) decided to do this by using pure SQL and therein only the ANSI-SQL standard. Believe me, it was a pure horror when it came to things more complex than a simple SELECT x FROM y ORDER by Z. That's why so many people want to get rid of the SQL stuff: It is very painful in practical use.
Regards
Thomas
Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning.
Programmer - an organism that turns coffee into software.
|
|
|
|
|
Thomas Weller wrote: It is very painful in practical use.
It's painful when it's abused. It is not painful for practical use, and I've written some pretty complex queries in my day.
|
|
|
|
|
Jeremy Falcon wrote: I've written some pretty complex queries
 Me too. And I will do it again if it becomes necessary. But writing big complex SQL queries is no good thing, just because you can!
It's because of maintenance and stable software architecture - especially the impedance mismatch problem in the lifecycle of your business objects - that you greatly profit from if you are using an ORM. I don't want my business logic smeared over different places and written in different languages. I want it in my business domain and nowhere else. And I don't want my business domain polluted with database stuff whatsoever. That is why I prefer an ORM over plain SQL. And if you really need to do something extraordinary (e.g. some performance optimizations) and write a statement yourself, you can do it in the ORM itself.
Regards
Thomas
Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning.
Programmer - an organism that turns coffee into software.
|
|
|
|
|
Sorry, but this post is full of crap. You think SQL sucks because you prefer not having your business logic "smeared" over different places and in different languages. Surely you can see how this is apples and oranges and flawed logic right?
And, you can protect your DB if you don't like it being "smeared".
Regarding ORM, it has its flaws too. It makes the data store way too dependent on the application to work properly. While it's a nice concept, it's far from perfect.
|
|
|
|
|
Jeremy Falcon wrote: Sorry, but this post is full of crap.
It's not. It's just about something you don't agree with. You should choose your words more carefully.
Jeremy Falcon wrote: You think SQL sucks
I don't think so. SQL serves its purpose quite well. I just don't want to have pieces of SQL in the main part of my software. SQL is part of a DB which in turn is part of some persistence system that should help my business logic in doing whatever it's supposed to do. But SQL is not the software itself and thus should be kept apart from the functional pieces of the software - it clearly is an infrastructural service and nothing more. An ORM helps maintaining that separation and prevents you from reinventing the wheel (i.e. writing data access code for every single project) again and again.
Jeremy Falcon wrote: It makes the data store way too dependent on the application
If the data store does not depend on the application, what is it good for then anyway?
Jeremy Falcon wrote: While it's a nice concept, it's far from perfect.
As everything else out there in the wild. Sure, it has its flaws. I never stated and never believed it is perfect. It's not even the preferable solution for every single situation. But in general it's a good one.
Generally, an O/R mapper is not about 'hiding SQL'. You can use SQL freely inside the mapper. There might be people out there who believe that they can avoid learning SQL and use an O/R mapper instead - I'm not one of them.
An O/R mapper is just an advanced conceptional technique that moves data access one step further.
Regards
Thomas
Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning.
Programmer - an organism that turns coffee into software.
|
|
|
|
|
Thomas Weller wrote: It's not. It's just about something you don't agree with. You should choose your words more carefully.
You're right, I don't agree with faulty logic.
Thomas Weller wrote: I don't think so. SQL serves its purpose quite well. I just don't want to have pieces of SQL in the main part of my software. SQL is part of a DB which in turn is part of some persistence system that should help my business logic in doing whatever it's supposed to do. But SQL is not the software itself and thus should be kept apart from the functional pieces of the software - it clearly is an infrastructural service and nothing more. An ORM helps maintaining that separation and prevents you from reinventing the wheel (i.e. writing data access code for every single project) again and again.
And no valid reasons outside of prejudice is mentioned.
Thomas Weller wrote: As everything else out there in the wild. Sure, it has its flaws. I never stated and never believed it is perfect. It's not even the preferable solution for every single situation. But in general it's a good one.
And it's still worse than SQL if you care about the design of your application being truly extensible.
Thomas Weller wrote: enerally, an O/R mapper is not about 'hiding SQL'. You can use SQL freely inside the mapper. There might be people out there who believe that they can avoid learning SQL and use an O/R mapper instead - I'm not one of them.
An O/R mapper is just an advanced conceptional technique that moves data access one step further.
But your code doesn't use it and therefore it's pointless to mention this since it's no longer quite as portable.
|
|
|
|
|
This post reads as if we are talking about two completely different things. I don't really get what you mean and it's apparent from your answers that you also don't really realize my points.
That's why I end this pointless discussion here.
Regards
Thomas
www.thomas-weller.de
Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning.
Programmer - an organism that turns coffee into software.
|
|
|
|
|
Thomas Weller wrote: you also don't really realize my points.
Sorry, but you assume too much. It's pretty straightforward, you say A sucks without giving a real reason why. You say B doesn't suck without giving a real reason why. I say prove A sucks; you don't. I give a disadvantage to B that you don't act as if its important. So, we go 'round and 'round.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
