|
Apparently, lots of people, seeing the amount of effort that goes in to hiding it!
SQL is not the best ever designed language but, common, it's not that bad!
Regards.
|
|
|
|
|
PedroMC wrote: SQL is not the best ever designed language but, common, it's not that bad
It is very good for interactively querying data and making changes (e.g. for DBAs and business analysts)
As a mean of handling data from applications it is horrible: typeless and prone to security flaws. Most application programmers construct SQL queries on the fly:
query = "SELECT * FROM Sometable WHERE SomeField=" + myEditControl.Text
This is pure evil
|
|
|
|
|
Nemanja Trifunovic wrote: As a mean of handling data from applications it is horrible: typeless and prone to security flaws. Most application programmers construct SQL queries on the fly:
Because most don't know SQL well.
Nemanja Trifunovic wrote: query = "SELECT * FROM Sometable WHERE SomeField=" + myEditControl.Text
In all sincerity if you think that's horrible, then in pseudocode what would you do that's so superior to go about telling a DB you want to pull values from a storage mechanism where the condition for which rested in myEditControl.Text?
I'm not saying you, but in my experience the people that knock SQL the most are the ones that know it the least. It offers a lot of nice short cuts in it as well that can shorten the amount of time you would have to query compared to writing code. Sure it has its ups and downs, but so does anything (expect me of course  ).
|
|
|
|
|
Jeremy Falcon wrote: In all sincerity if you think that's horrible, then in pseudocode what would you do that's so superior to go about telling a DB you want to pull values from a storage mechanism where the condition for which rested in myEditControl.Text?
Something like:
result = DataStorage.GetSomething(myEditControl.Text)
But even with SQL, there are parameterized SQL queries (even in MySQL these days, since version 5.0 or something  ) which would at least prevent SQL injections.
|
|
|
|
|
Nemanja Trifunovic wrote: result = DataStorage.GetSomething(myEditControl.Text)
But you can do that with a wrapper already if you prefer objects over a query string.
Remember, SQL specifies syntax that's meant to be standardized, much like how XML, HTML, etc. does, so you don't get everyone and their mother specifying their own thing underneath the hood. It's not supposed to interact with objects in your app intrinsically as it was never created for that means, and you would throw portability out the window that way as then data access would be solely dependent on the environment accessing the data rather than database.
|
|
|
|
|
Jeremy Falcon wrote: But you can do that with a wrapper already if you prefer objects over a query string.
If my wrapper internally does the same thing (appends a user-supplied string to a query) then there is no point.
SQL is inherently bad, for the similar reasons printf(...) is bad: it requires an interpreter that blindly executes strings that often contain parts supplied by users. I attended a couple of security seminars lately, and examples of exploiting this fact were abundant.
|
|
|
|
|
The same argument could be said about a programming language. Especially the ones that allow you to dynamically execute code.
What I'm getting at is SQL does more good than harm by providing an open means rather than closing it off. Abusing it is bad, then again so is anything.
|
|
|
|
|
Nemanja Trifunovic wrote: As a mean of handling data from applications it is horrible: typeless and prone to security flaws. Most application programmers construct SQL queries on the fly:
Btw, SQL isn't typeless. It's loosely typed, but that's not the fault of SQL, that's the fault of MS and SQL Server as that's one of the most forgiving RDBMSes out there. Some like that, some don't. But even still, it's not hard to enforcing typing in the application before it's sent to the DB.
|
|
|
|
|
Jeremy Falcon wrote: that's the fault of MS and SQL Server as that's one of the most forgiving RDBMSes out there.
|
|
|
|
|
What I'm trying to say is SQL Server is so forgiving it starts to loose the value of its intrinsic data types because you can mix and match so easily in it whereas you can't in other RDBMSes.
|
|
|
|
|
Jeremy Falcon wrote: What I'm trying to say is SQL Server is so forgiving it starts to loose the value of its intrinsic data types because you can mix and match so easily in it whereas you can't in other RDBMSes.
I was not talking about that, but would like to see some evidence anyway 
|
|
|
|
|
How about how SQL Server will implicitly convert between int and string for example?
SELECT 1 WHERE 1='1'
Though I don't know if that's unique to SQL Server, it could be the standard behaviour, either way I would prefer an Exception.
What's really crazy is that (if I recall correctly) it converts the string to int rather than the other way around. 
|
|
|
|
|
PIEBALDconsult wrote: Though I don't know if that's unique to SQL Server, it could be the standard behaviour, either way I would prefer an Exception.
That's valid in MySQL too, but MySQL offers a few tidbits to overcome it. It's not perfect, but it's something. For instance...
SELECT * FROM news WHERE 1 = '1.0';
...would match, but...
SELECT * FROM news WHERE BINARY 1 = BINARY '1.0';
...would not match.
|
|
|
|
|
Jeremy Falcon wrote: SELECT * FROM news WHERE BINARY 1 = BINARY '1.0';
|
|
|
|
|
Well my point was, SQL Server is even worse AFAIK. But, type safety aside, SQL in general still does way more good than harm. Imagine data access without it. Everyone would have their own binary implementation of access and the lock down to RDBMS and how it be used would be way worse than it is already.
|
|
|
|
|
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.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
