In order to prevent or minimize the probability of an SQL
injection happening, you can check for any semicolons in the string and replace
them with a space. An example replace function is presented below.
Listing 3 - The CleanChars Function used to prevent
SQL Injection
CREATE FUNCTION dbo.CleanChars
(@Str varchar(8000)) Returns Varchar(8000)
Begin
While CHARINDEX(';', @Str) > 0 Set @Str = replace(@Str, Substring(@Str,CHARINDEX(';',@Str),2),' ')
return @Str
EndThis function would clean the passed string of any semicolon
and replace it with a space. Each string passed to DynamicSql stored procedure
should be checked against the CleanChars function in the following pattern.
Listing 4
Set @FieldList = dbo.CleanChars(@FieldList)
After doing the check for every alphanumeric passed
variable, you should be safe when calling the DynamicSql stored procedure.
