February 16, 2015 at 8:33 pm
Comments posted to this topic are about the item SQL Injection, Still?
February 16, 2015 at 10:49 pm
Great article, Steve. There's been no excuse for any code that's susceptible to SQL or any other type of injection for years and yet it's still the most common hack attack. As you point out, there's a ton of information out there on it, it's not like it's a new subject, the tools of today and even yesteryear make it easy to prevent, and it doesn't take any extra time. It doesn't even take people that are ultra-serious about the jobs they're supposed to be doing but, like little kids, developers sometimes need a set of rules and a swat on the hinny to get their attention.
Management should also know better and management includes any lead developers. There should be a set of rules to follow and there should be code checks to make sure the rules are followed. While that does take a little extra time up front, it also saves big time on rework and really big time if you can't be hacked through injection because of it. Of course, that also means that you need have and enforce a good username/password policy as well as disabling the SA account.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 16, 2015 at 11:55 pm
On top of the "lazy" experienced developers the bar appears to be getting lower for new developers. Perhaps that is not accurate. Developers are being gauged on how quickly they can develop nice looking UIs and how many frameworks they can use as well as whether they can pair program and know how to run a scrum. There appears to be little emphasis put on quality.
Two coders pair programming without any emphasis on quality will lead to poor quality code.
Quite simply, we have moved consumerism into engineering and, as an industry, are aghast at the results whilst at the same time refusing to acknowledge the cause. Because it is deemed an uncompetitive strategy to pay to do it properly.
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
February 17, 2015 at 2:37 am
I spend a reasonable amount of time keeping up with what's going on and leaving cross site scripting or SQL injection vulnerabilities would be unthinkable - I think the frameworks and architectures we have set up prevent this comfortably. Not to say it wouldn't be impossible to hack some of our work, but you'd need to be pretty sharp I'd hope. Many devs just want to sit down and code though. It's definitely up to us more experienced types that DO take the time to understand and test issues to make sure this is not in anything our company puts out - in my opinion. Taking responsibility on that sort of front is natural for some not others but I can see how some companies do not have that sort of governance. In the past it is fair to say there have been extremely lax attitudes demonstrated.
February 17, 2015 at 4:36 am
Over the years I have tried to lock the databases/servers down. Results have been mixed and the path difficult. Comments thrown at me: "Not your responsibility", "Not our responsibility for security", "you making development impossible" , "Why would anyone bother..", "no ones complaining but you", "I get paid more than you therefore I know best!" etc etc. But never anyone saying "sure we will help what do you want us to do"
One of the simplest pen tests is to issue a crafted shutdown command. Worst one was I managed a shutdown from the logon page designed for an external facing public system (before anyone asks it was in a test system setup for DR testing and yes I got the security managers permission). This feature was there before we went live, and during the first 2 months of having gone live as apparently it was too difficult to fix
SQL Injection will always be an uphill struggle for the majority of places. Too much indifference and politics.
February 17, 2015 at 5:33 am
Yet Another DBA (2/17/2015)
Over the years I have tried to lock the databases/servers down. Results have been mixed and the path difficult. Comments thrown at me: "Not your responsibility", "Not our responsibility for security", "you making development impossible" , "Why would anyone bother..", "no ones complaining but you", "I get paid more than you therefore I know best!" etc etc. But never anyone saying "sure we will help what do you want us to do"One of the simplest pen tests is to issue a crafted shutdown command. Worst one was I managed a shutdown from the logon page designed for an external facing public system (before anyone asks it was in a test system setup for DR testing and yes I got the security managers permission). This feature was there before we went live, and during the first 2 months of having gone live as apparently it was too difficult to fix
SQL Injection will always be an uphill struggle for the majority of places. Too much indifference and politics.
I still find it incredible that so called professionals behave in such a manner!!!
(To be clear, I am not talking about Yet Another DBA ;-))
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
February 17, 2015 at 6:51 am
It seems the general consensus is that information about SQL injection has been around forever (years anyway). A quick search of SQL ServerCentral editorials finds a number of them, maybe one or two every year or so randomly going back quite a number of years. Yet I missed them all, or at least don't remember them. Don't chastise me for missing them, applaud me for seeing them now. Isn't that why we write and share, so others will learn? Thanks.
February 17, 2015 at 7:23 am
Iwas Bornready (2/17/2015)
It seems the general consensus is that information about SQL injection has been around forever (years anyway). A quick search of SQL ServerCentral editorials finds a number of them, maybe one or two every year or so randomly going back quite a number of years. Yet I missed them all, or at least don't remember them. Don't chastise me for missing them, applaud me for seeing them now. Isn't that why we write and share, so others will learn? Thanks.
Were you already catering for SQL Injection? If so it might be the case of that traffic light (stop light?) that you didn't consciously register as it was green.
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
February 17, 2015 at 7:24 am
Years ago I worked for a guy that saw a "widget" on the floor, and called a meeting of the entire warehouse staff, where he lectured us for over an hour on the cost of us not picking up the widget. At the end of the meeting one of us asked him why he didn't pick it up. The manager was incensed until the warehouse guy pointed out that he had seen the manager walk up to the widget and turn around, and call a meeting minutes later. The warehouse guy picked up the widget.
So, while it is a good thing to point out issues like this, we are missing a great opportunity to "pick up the widget"! People who read this would have good reason to expect that someone would post a link to an article that explains just what SQL injection is, and how to prevent it. I don't personally know of a good link, so I can't pick up the widget. As I read the article, my expectation was that somewhere there would be a link to a detailed article on how to prevent it. Kevin's article is a decent overview, but doesn't go into the technical details sufficiently. The point of the article is SQL injection isn't being prevented, not really how to prevent it. Newer developers would benefit for a more in depth look.
Someone on this group certainly can post a link or two, wouldn't it be good to use this article as a place where someone reading it can find a solution.
Dave
February 17, 2015 at 8:01 am
I need some help rewriting the stored procedure shown below to avoid sql injection. Also Web application currently replaces single quotes with 2 single quotes for @SearchString parameter and was wondering if there is a better way to avoid that logic in web application.
Thanks in advance !
CREATE PROCEDURE _SearchCustomer
@IdClient INT,
@SearchString VARCHAR(100) --Single quote already replaced with 2 single quotes in the web application.
AS
DECLARE @strsql NVARCHAR(4000)
DECLARE @TxDBName VARCHAR(50)
SELECT @TxDBName = DBName FROM Clients WHERE IdClient= @IdClient
SET @strsql='Select LName, Fname from ' + @TxDBName + '.dbo.Customers where (LName like ' + @SearchString + '%) OR (FName like ' + @SearchString + '%)'
EXEC sp_executesql @strsql
February 17, 2015 at 8:01 am
Okay, I'll pick up the widget...
SQL injection has been news for an awful long time (prior to 2000). Even so, I remember seeing code that used concatenation of SQL on Microsoft's "Patterns and Practices" site circa 2005. Old style ASP pretty well defined the pattern.
Recently, I've seen Ajax, particularly moving SQL to the browser environment, cause a resurgence in concatenation techniques. After all, we want flexible web sites, right?
There is going to have to be real liability for someone to really make the changes, I think...
February 17, 2015 at 8:11 am
brdudley (2/17/2015)
Okay, I'll pick up the widget...https://www.owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet
SQL injection has been news for an awful long time (prior to 2000). Even so, I remember seeing code that used concatenation of SQL on Microsoft's "Patterns and Practices" site circa 2005. Old style ASP pretty well defined the pattern.
Recently, I've seen Ajax, particularly moving SQL to the browser environment, cause a resurgence in concatenation techniques. After all, we want flexible web sites, right?
There is going to have to be real liability for someone to really make the changes, I think...
Thanks - while this will take some time to read, this source appears to be a very good point to start!
Also, I fixed your link, if you want to edit it in your post, the starting tag looked like this , and I removed the ] character after the URL but before the ending tag.
Dave
February 17, 2015 at 8:33 am
Venkat.Mantraratnam (2/17/2015)
I need some help rewriting the stored procedure shown below to avoid sql injection. Also Web application currently replaces single quotes with 2 single quotes for @SearchString parameter and was wondering if there is a better way to avoid that logic in web application.Thanks in advance !
CREATE PROCEDURE _SearchCustomer
@IdClient INT,
@SearchString VARCHAR(100) --Single quote already replaced with 2 single quotes in the web application.
AS
DECLARE @strsql NVARCHAR(4000)
DECLARE @TxDBName VARCHAR(50)
SELECT @TxDBName = DBName FROM Clients WHERE IdClient= @IdClient
SET @strsql='Select LName, Fname from ' + @TxDBName + '.dbo.Customers where (LName like ' + @SearchString + '%) OR (FName like ' + @SearchString + '%)'
EXEC sp_executesql @strsql
Yes indeed this needs a rewrite. You should create a forum question instead of just sticking this in as a comment in an article.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 17, 2015 at 9:06 am
Just to give an example on what can be done. But I agree that future replies should be done on a new thread.
CREATE PROCEDURE _SearchCustomer
@IdClient INT,
@SearchString VARCHAR(100) --Double single quotes not needed any more.
AS
DECLARE @strsql NVARCHAR(4000)
DECLARE @TxDBName VARCHAR(50)
SELECT @TxDBName = DBName
FROM Clients
WHERE IdClient= @IdClient
SET @strsql='SELECT LName, Fname ' + CHAR(13) +
'FROM ' + QUOTENAME( @TxDBName) + '.dbo.Customers ' + CHAR(13) + --Prevent injection on object names concatenation
'WHERE LName LIKE @SearchString + ''%'' ' + CHAR(13) + --Use a variable instead of concatenation
'OR FName LIKE @SearchString + ''%''' --Use a variable instead of concatenation
EXEC sp_executesql @strsql, N'@SearchString VARCHAR(100)', @SearchString --Parametrize the query
February 17, 2015 at 9:20 am
Thanks!!!
Viewing 15 posts - 1 through 15 (of 75 total)
You must be logged in to reply to this topic. Login to reply