SQL Injection - What to look for in a string

Question

SQL Injection - What to look for in a string

SQThaal

SSC Rookie

Points: 41
More actions
November 2, 2015 at 11:02 am

#399263

Hi,
I was asked to go through stored procedures and make modifications so SQL injections would be impossible.
I read a lot of articles on sql injections and I see that all the strings in the injections have semicolons and apostrophes in them.
I have to modify a stored procedure that has something like this in it :
exec ('UPDATE thisTable SET ' + @fieldName + ' = ''' + @Value + ''' WHERE thisTableId = ''' + @paramId + '''')
The @paramId parameter is an integer, so no big issue there.
If I raise an error when @fieldName isn't alphanumeric (a-Z,0-9) and/or @Value contains semicolons and/or apostrophes, am I safe?
Thanks.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246690 More actions · Answer 1

SQThaal (11/2/2015)
Hi,
I was asked to go through stored procedures and make modifications so SQL injections would be impossible.
I read a lot of articles on sql injections and I see that all the strings in the injections have semicolons and apostrophes in them.
I have to modify a stored procedure that has something like this in it :
exec ('UPDATE thisTable SET ' + @fieldName + ' = ''' + @Value + ''' WHERE thisTableId = ''' + @paramId + '''')
The @paramId parameter is an integer, so no big issue there.
If I raise an error when @fieldName isn't alphanumeric (a-Z,0-9) and/or @Value contains semicolons and/or apostrophes, am I safe?
Thanks.

You should consider doing this job in the best-practice way and using a fully parameterised call to sp_ExecuteSQL.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

SQThaal SSC Rookie Points: 41 More actions · Answer 2

Thanks.

I went with :

SET @cmd = 'UPDATE thisTable SET ' + @FieldName + ' = @Value WHERE thisTableId = @paramId'
SET @parameters = '@Value nvarchar(100), @paramId int'
EXEC sp_executesql @cmd, @parameters, @Value = @Value, @paramId = @paramId

And verifying that @FieldName is alpha-numeric because @FieldName doesn't work as parameter.

Luis Cazares SSC Guru Points: 183698 More actions · Answer 3

You should also use QUOTENAME( @fieldName) which would fix any problems causing SQL Injection. Or you could also validate its value against system views.

-- Your Parameters

DECLARE @fieldName varchar(128),

@Value varchar(100), --Or the desired length

@paramId int

--The SQL String

DECLARE @SQL nvarchar(4000)

SELECT @SQL = 'UPDATE thisTable SET ' + name + ' = @Value WHERE thisTableId = @paramId;'

FROM sys.COLUMNS

WHERE OBJECT_NAME(object_id) = 'thisTable'

AND name = @fieldName;

exec sp_executesql @SQL, N'@Value varchar(100), @paramId int', @Value, @paramId;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

SQThaal (11/2/2015)
If I raise an error when @fieldName isn't alphanumeric (a-Z,0-9) and/or @Value contains semicolons and/or apostrophes, am I safe?

No.

You need to whitelist (detail what is allowed), not black list. For the stuff that can be parameterised, the do that. For the stuff that can't, like the column name, you need to check it against the system tables to make sure it's a valid column name.

Something like (untested):

DECLARE @sSQL NVARCHAR(8000);

IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE name = @fieldName AND object_id = OBJECT_ID('thisTable'))

BEGIN

RAISERROR ('Not a valid column', 18, 1)

RETURN

END

SET @sSQL = 'UPDATE thisTable SET ' + @fieldName + ' = @_Value WHERE thisTableId = @_Param'

exec sp_executesql @sSQL, '@_Value int, @_Param int', @_Param = @paramId, @_Value = @Value

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQThaal SSC Rookie Points: 41 More actions · Answer 5

Don't think the RETURN line is needed after RAISEERROR().

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

I did say untested, try and tweak as required.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ed Wagner SSC Guru Points: 287024 More actions · Answer 7

SQThaal (11/2/2015)
Don't think the RETURN line is needed after RAISEERROR().

Yes it is required. You can raise an error and continue processing.

Here's a quick example.

if object_id('dbo.TryThis', 'p') is not null drop procedure dbo.TryThis;

go

create procedure dbo.TryThis

as

begin

print 'procedure started';

raiserror('error encountered', 16, 1);

print 'procedure finished';

end;

go

execute dbo.TryThis;

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions