January 21, 2016 at 9:01 pm
roger.plowman (1/19/2016)
Which is my entire point.SQL (in most of its variants) makes it stupidly easy to SQL inject. We can control our own code, but what about a third party product? We don't control that, or have the ability to demand to examine their code--nor is that particularly helpful even if we could.
Stored procedures (and no dynamic SQL in them) are the way to go, but this all comes back to a fundamental flaw in SQL syntax.
It's a fundamental flaw in the concept of being able to pass code to a system and have it executed, and that has exactly nothing to do with SQL syntax. Of course that means it's a fundamental flaw in the whole concept of an application program being able to call a computing service of some sort, telling it what to do, so it's not one we can afford to allow developers not to understand, and your message is certainly one that will tend to prevent developers understanding it because they'll blame it on your mythical SQL syntax issue instead of their own failure to understand some basic principles of security.
Of course it requires extremely stupid behaviour by developers for the flaw to create an exploitable problem. It certainly appears to be the case that VB and C++ and JavaScript developers suffer dreadfully from this flaw, because it's their code (and not SQL code) which enables most injection attacks (maybe some other languages have caught up with those three in the last few years).
Tom
January 22, 2016 at 4:18 am
TomThomson (1/21/2016)
...It certainly appears to be the case that VB and C++ and JavaScript developers suffer dreadfully from this flaw...
Some developers Tom. Some.
Also, EXEC is not part of VB, C++ nor JavaScript.
A lot of languages can be abused and poorly used (including SQL, VB, C++ and JavaScript). Whilst languages differ in what they solve through language design and constructs (e.g. memory management) I have yet to come across a language without a flaw.
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
January 22, 2016 at 6:14 am
TomThomson (1/21/2016)
roger.plowman (1/19/2016)
Which is my entire point.SQL (in most of its variants) makes it stupidly easy to SQL inject. We can control our own code, but what about a third party product? We don't control that, or have the ability to demand to examine their code--nor is that particularly helpful even if we could.
Stored procedures (and no dynamic SQL in them) are the way to go, but this all comes back to a fundamental flaw in SQL syntax.
It's a fundamental flaw in the concept of being able to pass code to a system and have it executed, and that has exactly nothing to do with SQL syntax. Of course that means it's a fundamental flaw in the whole concept of an application program being able to call a computing service of some sort, telling it what to do, so it's not one we can afford to allow developers not to understand, and your message is certainly one that will tend to prevent developers understanding it because they'll blame it on your mythical SQL syntax issue instead of their own failure to understand some basic principles of security.
Of course it requires extremely stupid behaviour by developers for the flaw to create an exploitable problem. It certainly appears to be the case that VB and C++ and JavaScript developers suffer dreadfully from this flaw, because it's their code (and not SQL code) which enables most injection attacks (maybe some other languages have caught up with those three in the last few years).
The flaw I'm talking about is the lack of a definitive end of line. In SQL syntax the concept of whitespace includes the end of line marker used by the majority of other languages. This makes SQL injection MUCH easier by treating end of line as just another kind of whitespace.
And you're right to some degree, allowing a third party to pass code (as opposed to just data) is insanely dangerous. Especially when it blurs the line between data and code as SQL does.
There are cases where dynamic code construction is unavoidable if you want flexibility. But most of the SQL databases almost FORCE you to use dynamic code if you want to do anything even slightly out of the norm.
Another syntax issue is the way quotes are escaped by repeating them. Which makes it harder to avoid the 'accidental code in parameters' that SQL injection uses.
Most of this does devolve back into the dynamic code being passed issue, and yes, any scripting language can be exploited in a similar manner, but most other scripting languages aren't in direct control of a sensitive database!
January 22, 2016 at 6:38 am
roger.plowman (1/22/2016)
TomThomson (1/21/2016)
roger.plowman (1/19/2016)
Which is my entire point.SQL (in most of its variants) makes it stupidly easy to SQL inject. We can control our own code, but what about a third party product? We don't control that, or have the ability to demand to examine their code--nor is that particularly helpful even if we could.
Stored procedures (and no dynamic SQL in them) are the way to go, but this all comes back to a fundamental flaw in SQL syntax.
It's a fundamental flaw in the concept of being able to pass code to a system and have it executed, and that has exactly nothing to do with SQL syntax. Of course that means it's a fundamental flaw in the whole concept of an application program being able to call a computing service of some sort, telling it what to do, so it's not one we can afford to allow developers not to understand, and your message is certainly one that will tend to prevent developers understanding it because they'll blame it on your mythical SQL syntax issue instead of their own failure to understand some basic principles of security.
Of course it requires extremely stupid behaviour by developers for the flaw to create an exploitable problem. It certainly appears to be the case that VB and C++ and JavaScript developers suffer dreadfully from this flaw, because it's their code (and not SQL code) which enables most injection attacks (maybe some other languages have caught up with those three in the last few years).
The flaw I'm talking about is the lack of a definitive end of line. In SQL syntax the concept of whitespace includes the end of line marker used by the majority of other languages. This makes SQL injection MUCH easier by treating end of line as just another kind of whitespace.
And you're right to some degree, allowing a third party to pass code (as opposed to just data) is insanely dangerous. Especially when it blurs the line between data and code as SQL does.
There are cases where dynamic code construction is unavoidable if you want flexibility. But most of the SQL databases almost FORCE you to use dynamic code if you want to do anything even slightly out of the norm.
Another syntax issue is the way quotes are escaped by repeating them. Which makes it harder to avoid the 'accidental code in parameters' that SQL injection uses.
Most of this does devolve back into the dynamic code being passed issue, and yes, any scripting language can be exploited in a similar manner, but most other scripting languages aren't in direct control of a sensitive database!
Injection is easily avoidable. There are probably hundreds of articles, blogs and examples all over the internet. It's simple. Parameterise effectively. That's it - vulnerability sorted.
The problem is lazy and irresponsible coders who can't be bothered to learn their jobs properly. If someone writes crap code, because they can't be bothered to learn their job properly, and implement a simple well known solution, it's their fault, not the fault of the language
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
January 22, 2016 at 7:08 am
Most languages, particularly scripting languages like JavaScript and PHP (ie: Eval function), support dynamic execution. It's just more problematic with SQL because it's a language that enables retreival and modification of database storage.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
January 22, 2016 at 7:21 am
roger.plowman (1/22/2016)
The flaw I'm talking about is the lack of a definitive end of line. In SQL syntax the concept of whitespace includes the end of line marker used by the majority of other languages. This makes SQL injection MUCH easier by treating end of line as just another kind of whitespace.
I don't really agree that this is a flaw, there are plenty of languages that don't explicitly use EOL as a syntactic element (other than lumping it in with the rest of whitespace as a delimiter "class" (lol inventing terms lol))
But even if it were, I don't even think it matters because databases prefer their interfaces to send user data to the process as parameters, and the SQL to execute on these parameters SEPARATELY. This way, the work to keep code and data (parameters) separate (and therefore keep user supplied code hacks out of the execution stream) is kept in one place and maintained by those best suited to maintain the code and data seperation, ie., THE SERVER DEVELOPERS.
When you mix SQL code and user parameters yourself (by not using parameterized calls to SQL Server), YOU ARE ESSENTIALLY REINVENTING THE WHEEL. When this wheel is REINVENTED POORLY, security can fail, and end users can inject arbitrary code to be executed by the server.
Viewing 6 posts - 31 through 35 (of 35 total)
You must be logged in to reply to this topic. Login to reply