CodeSnip: How to Create a Complete Dynamic SQL Statement Using SQL Server
page 4 of 5
by Nidal Arabi
Feedback
Average Rating: 
Views (Total / Last 10 Days): 27791/ 63

Risky Point to Avoid

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(@StrSubstring(@Str,CHARINDEX(';',@Str),2),' ')
return @Str
End

This 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.


View Entire Article

User Comments

Title: hello   
Name: asa
Date: 2012-11-17 7:52:29 AM
Comment:
heelo
Title: q   
Name: q
Date: 2012-09-11 9:54:43 AM
Comment:
q
Title: Dynamic sql   
Name: mala
Date: 2009-10-23 8:23:59 AM
Comment:
very very nice article.....

this article help to me....thanks a lot!..






Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2019 ASPAlliance.com  |  Page Processed at 2019-09-21 7:34:58 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search