At a training recently I was explaining how to do as well as prevent SQL injection and figured I should make a blog about it since very few DBA and developers understand it. Some of it has to do with the fact that we all test for functionality and performance and the rest as to do with the fact that if your company trained you on how to hack so that you can test better than there is a good chance they might end up regretting it. But with the way the cloud has blown up in the last year and the way that its becoming easier for more developers to code and deploy code without really doing any due diligence.
So what is dynamic SQL? It is a way for developers to execute code against the database where the definition of the code can change depending on user requirement. A simple example of this might be a form where different users can filter for different search criteria as a result the developer can’t predict what the where condition should look like. So he writes something like this.
CREATE PROCEDURE dynamicsql @where NVARCHAR(128)
AS
BEGIN
DECLARE @sql NVARCHAR(2000) = 'select * from sys.databases '
SET @sql = @sql + ' ' + @where
EXEC sp_executesql @sql
END
GO
-- EXPECTED BEHAVIOUR
EXEC dynamicsql ' where state_desc =''ONLINE'''
— SQL INJECTION
EXEC dynamicsql ' where 1=1 ; select * from sys.syslogins'
The best way to avoid SQL injection is to use stored procedures for application logic. Instead of embedding dynamic content write out the logic in the procedure.
The web developer also needs to ensure proper validation and textbox max length property is set when designing forms.
If you need to use sp_executesql use it properly as shown here.
EXECUTE sp_executesql N'SELECT * FROM AdventureWorks2012.HumanResources.Employee
WHERE BusinessEntityID = @level'
,N'@level tinyint'
,@level = 109;
Please Consider Subscribing
