43

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?

Improve this question
5
  • possible duplicate of Creating safe SQL statements as strings Commented Apr 3, 2011 at 10:24
  • 1
    Use parameterized queries (see linked question). Commented Apr 3, 2011 at 10:24
  • 3
    The answer there says to input the data into parameters. I'm simply trying to generate the script via looking at the text files. My program isn't actually going to call the database, just spit out the scripts. Commented Apr 3, 2011 at 10:25
  • Not sure how your reply addresses the recommendation that you use parameterized queries. The scripts you are generating would simply be scripts that use parameterized queries--you don't actually have to call the database. Commented Apr 3, 2011 at 11:50
  • @Tim: "simply be scripts that use parameterized queries" - how? What does an .sql script file look like with parametrized queries? Wouldn't the parameter values have to be escaped again when they are written into that file? Commented May 8, 2017 at 12:52

3 Answers 3

Reset to default
41

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

safeString = unsafeString.Replace("'","''");
Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

This assumes that the OP is quoting all values in their insert statement though. If they are generating a string like this INSERT INTO T1(numeric,string) values (1,'some string') then it won't help if they have some unexpected bad data in the first column. (e.g. '1,2); DROP TABLE ...'
@MartinSmith Then use int.TryParse for the first parameter to ensure its a clean integer.
What about commas in an insert statement?
This is enough for me. I included it in my micro ORM (EntityLite) for very large IN predicates, to workaround the 2100 SQL Server max number of parameters. I hope there is no more characters to take care of. I think I was too much paranoid with SQL injection.
@JohanAspeling a comma within single quotations should not cause a problem. But if it occurred where you did not expect a string then the other comment about TryParse for a numeric value would take care of it
|
33

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.

Improve this answer

20 Comments

+1 there is no "safe" way to concatenate together SQL statements - using parametrized queries (ALWAYS!) is the only viable way to go
@marc_s: Parameterized queries are only a partial solution. They don't protect against SQL injection into a dynamic SQL query, javascript injection into a varchar field, parameter injection into an html label, etc. There is no "silver bullet" solution to injection.
See this example for how dynamic SQL enables SQL Injection, even though you use a .NET parameterized query
@MichaelStum I have thought a bit more and I agree you should validate the string for XSS and then store it if it's safe. That is, validate, but do not sanitise. Let a framework sanitise the string for you.
The OP says they want to write the INSERT statements into SQL script files. It's not clear from this answer how parametrized queries would work in those. Once the parameter values are written into the script files, they still have to be escaped in some way.
|
0

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
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.