|
Jeremy Falcon wrote: But, type safety aside, SQL in general still does way more good than harm.
In general? Yes - for interactive tasks that are mostly done by a dba or a business analyst.
For getting the data by applications that programatically construct SQL queries by appending strings? No!
I rest my case 
|
|
|
|
|
Nemanja Trifunovic wrote: For getting the data by applications that programatically construct SQL queries by appending strings? No!
Sorry, but I disagree. Even in this instance you'd be worse off with no open standard underneath the hood. In fact SQL is probably the main reason RDBMSes are even so popular these days, because you know how to use them.
Now, imagine you writing a program that uses SQL Server with some obscure MS technology to use in your code, but wait, the customer wants to also access the data from the web in a PHP application. You're screwed, unless someone took the time to write a library, and even then there's no guarantee their lib would be compatible with your code. With SQL, the connectors is the only thing that faces this limitation.
And not to mention, there would be no standard way to represent the data in the first place without SQL. Which means everyone would have to learn your app's implementation and possibly whatever language you used with it, just to make sense of your DB.
I rest my case, we're better off with SQL than without.
|
|
|
|
|
Nemanja Trifunovic wrote: As a mean of handling data from applications it is horrible: typeless and prone to security flaws.
You can enforce strict types and security in SQL as much as you want and still write the SQL.
Nemanja Trifunovic wrote: Most application programmers construct SQL queries on the fly:
"query = "SELECT * FROM Sometable WHERE SomeField=" + myEditControl.Text".
This is pure evil.
There are bad practices for every technology and not validating user input is not restricted to SQL.
Regards.
|
|
|
|
|
PedroMC wrote: You can enforce strict types and security in SQL as much as you want and still write the SQL.
SQL itself does not provide any means to do that. Avoiding SQL injections is possible with parameterized queries, but again that is not SQL as a language that provides it. Not to mention that some pretty popular db engines did not even support parameterized queries until recently (MySQL, for instance).
PedroMC wrote: There are bad practices for every technology and not validating user input is not restricted to SQL.
Not every technology enables users' input to be executed as code. That is what makes "raw" SQL a major source of security problems.
|
|
|
|
|
Nemanja Trifunovic wrote: Avoiding SQL injections is possible with parameterized queries
Sanitizing data is enough to avoid SQL injections. That has always been available and it usually is trivial to do.
Nemanja Trifunovic wrote: Not every technology enables users' input to be executed as code.
Not every, but SQL is far from being the only (e.g. buffer overflows).
Regards.
|
|
|
|
|
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.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
