CODEWITHSUNDEEP
.netsqlsanitization
Diskdrive
Diskdrive

Reputation: 18825

How do I convert a string into safe SQL String?

I'm generating some sql insert statements from a bunch of text files.

These text files are generally user input data. I would like to sanitize this data so that it's not going to break the insert statement.

For example, some of the input data, people have used the word Don't. The "'" in don't will lead the sql statement to think the string has ended and therefore cause an error.

Is there any .NET method I can call to kind of convert all of these characters to either escape codes or safe characters?

Upvotes: 41

Views: 93973

Answers (3)

Chris Lund
Chris Lund

Reputation: 1

I think a combination of both would be a good practice generally. On the code behind (assumes that the textbox only accepts numbers:

    strSQL="exec GetMember" & SanitizeInput(ID)
    *do your db call*

    Public Function SanitizeInput(byval InputString as String)
      Dim OutputString=replace(InputString,"'","''")
      OutputString=replace(OutputString,"<script>","")
      OutputString=replace(OutputString,"DROP","")
      *... and more rules...*
      Return OutputString
    End Function

On your SQl Sproc:

CREATE PROCEDURE GetMember
@ID int
AS
BEGIN
SET NOCOUNT ON;
Select {whatevercolumns} from Members where ID=@ID
END
GO

Upvotes: 0

Andomar
Andomar

Reputation: 238078

There is only a single character you have to escape: ansi 0x27, aka the single quote:

safeString = unsafeString.Replace("'","''");

Upvotes: 40

Michael Stum
Michael Stum

Reputation: 180924

Don't sanitize your strings. Use parameterized queries instead, as they handle all sanitization.

You don't specify which database you are using, so I assume it is MS SQL Server. Microsoft has an article on the official ASP.net website about this. Also see MSDN for SqlCommand.Parameters and the AddWithValue method.

Upvotes: 33

Related Questions