Protection from SQL injection

Question

Is there a function which accepts a string as a parameter and returns a "cleaned" string, so as to support protection from SQL injection?

http://www.mikesdotnetting.com/Article/113/Preventing-SQL-Injection-in-ASP.NET — IvanL, Aug 10 '12 at 09:32
@NurlanKenzhebekov the problem is: string sanitisation is not robust, and is an "arms race". The correct approach is to forget completely about "cleaning" the string, and use parameters instead. — Marc Gravell, Aug 10 '12 at 09:33
possible duplicate of [How can I avoid SQL injection attacks in my ASP.NET application?](http://stackoverflow.com/questions/305044/how-can-i-avoid-sql-injection-attacks-in-my-asp-net-application) — derobert, Aug 10 '12 at 11:06

score 7 · Answer 1 · edited Aug 10 '12 at 09:31

7

You can add parameters with AddWithValue method like this:

SqlCommand cmd = new SqlCommand("Select * From Test Where TestID = @TestID");
cmd.Parameters.AddWithValue("@TestID", 5);

edited Aug 10 '12 at 09:31

Marc Gravell

976,458
251
2,474
2,830

answered Aug 10 '12 at 09:30

pilavust

528
1
7
19

score 4 · Accepted Answer · edited Jun 20 '20 at 09:12

4

You need to use parameters, LINQ to SQL or Entity Framework.

edited Jun 20 '20 at 09:12

Community

1
1

answered Aug 10 '12 at 09:29

JohnnBlade

4,233
1
20
22

1

make that "or a library that will do this for you, such as... {blah}" and it would be better; this isn't unique to L2S/EF ;p – Marc Gravell Aug 10 '12 at 09:29

score 2 · Answer 3 · edited Jan 15 '17 at 12:03

2

No function will be perfect or foolproof. You should really aim to protect against SQL injection through other means (i.e., stored procedure, SQL parameterisation, etc.).

Although this function is fairly effective :)

string CleanString(string s)
{
    return string.Empty;
}

edited Jan 15 '17 at 12:03

Peter Mortensen

30,030
21
100
124

answered Aug 10 '12 at 09:29

podiluska

50,144
7
94
100

LOL))))) I am using HttpContext.Current.Server.HtmlEncode(). Does this protect? – Nurlan Aug 10 '12 at 09:34
No. It will protect against some forms of XSS attacks (which are different), but not all, and is no protection against SQL injection – podiluska Aug 10 '12 at 09:36

score 1 · Answer 4 · edited May 23 '17 at 12:00

I don't think a function that clever enough exists to find if there is a possibility of SQL injection or not.

But the best thing is to avoid all the possibilities. For example, using type-safe SQL parameters for data access.

Please have a look:

score 1 · Answer 5 · edited Mar 17 '17 at 10:45

Have a look at the answers on this question at Security Stack Exchange - the most appropriate path for you may be OWASP's ESAPI - their Enterprise Security API, which has functionality for Java EE, .NET, ASP Classic, PHP, ColdFusion CFML, Python, JavaScript, Objective-C, Force com, Ruby, Swingset, C, C++ and Perl is:

a free, open source, web application security control library that makes it easier for programmers to write lower-risk applications. The ESAPI libraries are designed to make it easier for programmers to retrofit security into existing applications. The ESAPI libraries also serve as a solid foundation for new development. Allowing for language-specific differences, all OWASP ESAPI versions have the same basic design: There is a set of security control interfaces. They define for example types of parameters that are passed to types of security controls. There is a reference implementation for each security control. The logic is not organization‐specific and the logic is not application‐specific. An example: string‐based input validation. There are optionally your own implementations for each security control. There may be application logic contained in these classes which may be developed by or for your organization. An example: enterprise authentication.

And also read this document: SQL Injection Prevention Cheat Sheet

score 0 · Answer 6 · edited Jan 15 '17 at 12:03

0

I strongly believe on using parameters to avoid SQL injection. You can write a stored procedure or parametrized query to avoid it.

Parameters has many benefits besides SQL injection, such as it can handle lot of issues with dates and numbers...

edited Jan 15 '17 at 12:03

Peter Mortensen

30,030
21
100
124

answered Aug 10 '12 at 09:29

Akash KC

15,511
5
36
58

Protection from SQL injection

6 Answers6

LINQ to SQL

Entity framework

SQL Parameters