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

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

(@Str varchar(8000)) Returns Varchar(8000)
While CHARINDEX(';', @Str) > 0 Set @Str = replace(@StrSubstring(@Str,CHARINDEX(';',@Str),2),' ')
return @Str

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
Title: q   
Name: q
Date: 2012-09-11 9:54:43 AM
Title: Dynamic sql   
Name: mala
Date: 2009-10-23 8:23:59 AM
very very nice article.....

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

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

©Copyright 1998-2021  |  Page Processed at 2021-12-07 8:17:02 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search