SelecTViews
With Stephen Wynkoop
Find out about business intelligence trend surveys, MySQL in the cloud…the latest news and your SQL Server BI tip for the day.
Injection Prevention Tips and Techniques
In the last couple of newsletters I have provided some code examples of how to prevent SQL Injection. The short answer was to use strongly typed data. But there are other techniques for injection prevention other than database access code.
Although this is not the best medium for providing comprehensive assistance, and there are many web sites that do a great job of education on what Injection is, I did want to look at a few basic things you can do to protect your applications from injection.
One company I worked for used ASP.Net. To prevent injection they created all their own web controls, inheriting from the standard controls shipped in .Net. They added additional functionality to the controls to prevent different forms of injection including Scripting and HTML. This way they didn’t have to continue to write injection proof code over and over.
Here are some generic tips…
For controls in web forms or thick client applications, strongly type the contents as alpha, numeric or both. Enforce that the data contained matches the type of data represented by the control.
Restrict the number of characters allowed in your controls to the maximum allowed in your persistence. Web controls with no text limits provide a rich environment for injection attempts.
If you have controls requiring a lot of text (such as free form notes) have a regular expressions validation method test the contents.
Make your variables only as big as the data requires. Don’t use a long int when a short int is adequate. Don’t use a byte for Boolean values. This applies to client code and database code, parameters or tables.
Don’t build SQL commands by concatenating values resulting from user input, use strongly typed parameters.
Grant the least permissions possible to your application when connecting application resources. This is not just a database issue, but also applies to services.
Don’t store authentication data in clear text on your application servers. Encrypt the data. This could include accounts to use when accessing file, web service, etc resources. It definitely refers to database access credentials.
For databases, restrict your application capabilities as much as possible.
- If using stored procedures, don’t grant access to any tables directly.
- If not using stored procedures, restrict permissions on databases, table and/or views.
- Use Schema’s wherever possible to minimize the work required to implement security.
- Avoid using an account with Database Owner (DBO) rights for your application. Within any database where the account has DBO permissions, they are the equivalent of an administrator in that database.
Readers Comments
Security Reporter Falsely Accused
Bastien:
I read your newsletter with interest, but need to point out that, as I am sure you’ve been told, it’s not TechCrunch (which is a blogging site) but DROPBOX that had the security issue. TechCrunch simply reported it.
Editor:
Bastien…thank you so much for the correction. The online version of the editorial was modified for future reference. Also, my deepest apologies to TechCrunch for incorrectly attributing this flaw to them. Their reporting on the issue was what drew me to the problem in the first place.
SQL Injection
John:
I have an update to the stored procedure that allowed SQL Injection
CREATE PROCEDURE spUpdateTable (
@SQLStatement nvarchar(4000)
)
--Version 1 - some naive programmer. Date = ??. Original version.
--Version 2 - interested reader. Date = 20 Jun 2011. Added in checking to mitigate (somewhat) against sql injection.
set @SQLStatement = ltrim(rtrim(@SQLStatement))
--get rid of double spaces and carraige returns to make pattern matching easier
set @SQLStatement = replace(@SQLStatement, char(10), ' ')
while charindex(' ', @SQLStatement) > 0 begin
set @SQLStatement = replace(@SQLStatement, ' ', ' ')
end
--We only want to allow update statements. Based on careful analysis of sql traces, we have determined that
--the front-end app
-- * only updates one table at a time
-- * has no joins to other tables as part of the update
-- * etc.
if @SQLStatement not like 'update%' begin
select 'missing update directive: ' + @SQLStatement
return -1
end
if @SQLStatement not like 'update % set %' begin
select 'missing set directive: ' + @SQLStatement
return -1
end
Do you have other favorite Injection proofing tips you’d like to share? I just did a high level brainstorm of my favorites. You can share your favorite tips with everyone by sending them to btaylor@sswug.org. I’d really be interested in ideas about how to hold service providers accountable for their security measures. We are betting our business on them.
Cheers,
Ben
$$SWYNK$$
Featured White Paper(s)
Hybrid Management White Paper
Written by AvePoint
As organizations worldwide continue to look for ways to… (read more)
Featured Script
round to increment
Function that will round a value to a increment value…. (read more)