Fending off SQL Injection Attacks plus a nice SP to help you along the way
This topic comes up lots of recent and always surprises me that awareness levels are still quite low.
Adrian (thanks Adrian) from DFEEST in Adelaide sent me a link that discusses SQL Injection attacks (this is a universal DB attack); you can find the article at https://www.sitepoint.com/article/sql-injection-attacks-safe. On this site there is a useful SP you can add to your system for general purpose string cleaning…
CREATE FUNCTION dbo.CleanString (@Clean varchar(8000))
RETURNS varchar(8000) AS
BEGIN
set @Clean=REPLACE(@Clean,'''',' '); -- Remove single quote
set @Clean=REPLACE(@Clean,'^',' '); -- Remove caret
set @Clean=REPLACE(@Clean,'#',' '); -- Remove hash
set @Clean=REPLACE(@Clean,'select',' '); -- Remove select
set @Clean=REPLACE(@Clean,'drop',' '); -- Remove drop
set @Clean=REPLACE(@Clean,';',' '); -- Remove semi colon
set @Clean=REPLACE(@Clean,'--',' '); -- Remove double dash
set @Clean=REPLACE(@Clean,'insert',' '); -- Remove insert
set @Clean=REPLACE(@Clean,'delete',' '); -- Remove delete
set @Clean=REPLACE(@Clean,'xp_',' '); -- Remove extended stored procedure prefix
return @Clean;
END
So this plus the following guidance particularly for web apps:-
- validateRequest is on true by default and most cases this should be fine – it raises an error if a form post contains any HTML whatsoever.
- Use HtmlEncode when reflecting any user input back to the browser
- Validate for known good data
- Use ASP.NET Validators – remember these act client side in IE 5 and above but always execute server side so you MUST check “Page.Isvalid()” is true.
- Use Regular expressions to validate – check out https://www.regxlib.com/ for Regular Expression samples. If you hate regex then validate with your own code
- Validate strings in SQL Server with the above stored proc
- Connect your app to the database with the minimum privileges required and ideally just execute rights on the Stored Procs required to get the work done.