How to solve sql injection ?

Question

1.00/5 (1 vote)

See more:

Hi
How to solve Sql Injection in sqlserver2008R2 with parameterized quries and asp.net?

If any one knows plz guide me .I don't know about this.

Thanks in advance.

Posted 13-Mar-13 5:00am

24983

Add a Solution

Comments

Jameel VM 13-Mar-13 11:04am

try this link
http://www.mikesdotnetting.com/Article/113/Preventing-SQL-Injection-in-ASP.NET

Richard C Bishop 13-Mar-13 11:11am

It is not helpful to the OP by spoon feeding them the most basic information that exists millions of places online. I know you just want to help, but sometimes you have to take a step back and analyze if what you are doing is actually helping or not. Just a friendly comment for something to think about.

DinoRondelly 13-Mar-13 11:13am

+5 ... I concur

Richard C Bishop 13-Mar-13 11:08am

The fact that you are asking this question shows us that you have not even done the simplest research regarding either one of those subjects. Use a search engine.

24983 13-Mar-13 11:20am

User enter search Engine(Textbox) : ';delete from tablename --
create procedure Dsp_GetData
@SearchText varchar(100)
AS
Begin
select * from tblaname where Field =@SearchText
End

It will delete all rows from my table.

Thats why iam asking

[no name] 13-Mar-13 11:31am

That is exactly why you use a parameterized query as noted in your question. What exactly is the problem?

[no name] 13-Mar-13 11:15am

And the answer to your question lies within the question itself.

2 solutions

Solution 1

Hello Santosh,

As far as I know, SQL injection can happens in front-end code (ASP.NET/Codebehind) as well backend code (Stored Proc/Functions).
Front-End
In front-end the common reason of SQL injection is not using parameterized quries (SQLCommand). What it means that dynamic query string are constructed by concatenating the request values. e.g.

C#

strSQL = "SELECT * FROM user_table WHERE user_code = '" + Request["userCode"] + "' AND user_pass = '" + Request["userPass"] + "'";

In this case a hacker may type in values for say password field such that the resulting query may look like

SQL

SELECT * FROM user_table WHERE user_code = 'IUnknown' || user_code LIKE '%' -- AND user_pass = 'NOPASS'

One way to solve this, but not reommended is to sanitize the input by escaping certain characters (',",| etc.) and generate the dynamic SQL string.
The preferrd way is to use bind variables (parameterized quries using SqlCommand). This way query only contains the placeholders and values are passed to the database. This not only helps databse server to reuse the query execution plan but also removes the need of hard parsing.

Now second type of SQL injection happens due to improper use of bind variables. In this case the front-end code is using the bind variables to say invoke a stored proc. Inside this stored procedure a dynamic query is getting constructed again by concatenating the values. So to prevent this agin you have to use parameterized queires inside your stored proc. A typical example for Oracle database will be

SQL

select * from emp where deptno = :deptno;

Note that :deptno actually refers to a variable whch might be a paramter passed to the stored proc.

Please also look at site and go through their Secure Coding Practices guide to know more about other types of vlunarabilities.

There is also a similar guide by Microsoft.

Regards,

Posted 13-Mar-13 5:27am

Prasad Khandekar

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

fjdiewornncalwe · Accepted Answer · 2013-03-13T05:29:00

Solution 2

Here is a very simple C# example: http://www.dotnetperls.com/sqlparameter[^]

The basic concept is this:
If you build a query using syntax like this: string query = "SELECT something FROM someTable WHERE someKey = '" + someKeyValue + "'";
then malicious sql code could be entered into someKeyValue and then make it's way your database. This malicious code could include any valid sql statement that would then get parsed and executed in your sql server.

To alleviate this, use sql parameters. This will internally sanitize that input value as a parameter and will ensure that you don't run into injection issues.

Posted 13-Mar-13 5:29am

fjdiewornncalwe

Updated 13-Mar-13 5:32am

v2

Comments

[no name] 13-Mar-13 11:34am

Wow... someone is sure on a hair trigger for downvoting.

Richard MacCutchan 13-Mar-13 12:28pm

There is some jerk about who seems to spend his time doing nothing but down voting questions, and some answers here. I have suggested to Chris that the miscreant gets a swift boot in the proverbial.

[no name] 13-Mar-13 12:36pm

Hey! I resemble that.... no wait! :-) I have noticed that. I had not noticed that it extended down to serially down voting answers too. Like Marcus's answer here seems perfectly reasonable to me, yet was down voted within a very short time of being posted.

fjdiewornncalwe 13-Mar-13 13:05pm

Thanks guys.