Remove sql injection

Question

0.00/5 (No votes)

See more:

.NET3.0

Dear all, can you please do a favor for me i am getting the sql injection while security testing of my appliction the code is given below, may i know how can i remove..
thanks in advance..

C#

i = 0;
j = 0;
SqlDataReader dr;

con = data.GetConnection();
con.Open();
sqlCmd = new SqlCommand("select REmp_Id as 'Emp ID',SFname as 'Sender Name',Smname as 'Sender Middle Name',SSurname as 'Sender Last Name',SAddress as 'Sender Address',RFname as 'Receiver First Name',RMname as 'Receiver Middle Name',RSurname as 'Receiver Last Name',REmail as 'Receiver Email ID',In_Date as 'Date Received',In_Time as 'Time In',ConsignmentNo as 'POD NO',Delivered as'Status of Courier',CollectedBy as 'Received by',DeliveredDate as 'Deliver Date',RTime as 'Received Time',RMobile as 'Contect No',REmpID as 'Receiver Emp ID',Remarks from tblinbound where REmail='" + txtEmailID.Text + "'", con);
SqlDataAdapter sqlDA = new SqlDataAdapter(sqlCmd);
DataSet ds1 = new DataSet();
sqlDA.Fill(ds1, "tblinbound");
grdInbound.DataSource = ds1.Tables[0];
con.Close();

Posted 2-May-13 22:34pm

Rajeshkrathor

Updated 2-May-13 22:38pm

Orcun Iyigun

v2

Add a Solution

3 solutions

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)

OriginalGriff · Answer 1 · 2013-05-02T22:41:00

Solution 2

Do not concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead. So, don't append the email address, parametrize it:

C#

con = data.GetConnection();
con.Open();
sqlCmd = new SqlCommand("select REmp_Id as 'Emp ID',SFname as 'Sender Name',Smname as 'Sender Middle Name',SSurname as 'Sender Last Name',SAddress as 'Sender Address',RFname as 'Receiver First Name',RMname as 'Receiver Middle Name',RSurname as 'Receiver Last Name',REmail as 'Receiver Email ID',In_Date as 'Date Received',In_Time as 'Time In',ConsignmentNo as 'POD NO',Delivered as'Status of Courier',CollectedBy as 'Received by',DeliveredDate as 'Deliver Date',RTime as 'Received Time',RMobile as 'Contect No',REmpID as 'Receiver Emp ID',Remarks from tblinbound where REmail=@EM", con);
sqlCmd.Parameters.AddWithValue("@EM", txtEmailID.Text);
SqlDataAdapter sqlDA = new SqlDataAdapter(sqlCmd);

Posted 2-May-13 22:41pm

OriginalGriff

Comments

[no name] 3-May-13 5:07am

thanks guys

[no name] 3-May-13 5:11am

Is it right format of query?

i = 0;
j = 0;
SqlDataReader dr;
string REmailID = txtEmailID.Text;
con = data.GetConnection();
con.Open();
sqlCmd = new SqlCommand("select REmp_Id as 'Emp ID',SFname as 'Sender Name',Smname as 'Sender Middle Name',SSurname as 'Sender Last Name',SAddress as 'Sender Address',RFname as 'Receiver First Name',RMname as 'Receiver Middle Name',RSurname as 'Receiver Last Name',REmail as 'Receiver Email ID',In_Date as 'Date Received',In_Time as 'Time In',ConsignmentNo as 'POD NO',Delivered as'Status of Courier',CollectedBy as 'Received by',DeliveredDate as 'Deliver Date',RTime as 'Received Time',RMobile as 'Contect No',REmpID as 'Receiver Emp ID',Remarks from tblinbound where REmail=@emailid", con);
sqlCmd.Parameters.Add(new SqlParameter("emailid", REmailID));
SqlDataAdapter sqlDA = new SqlDataAdapter(sqlCmd);
DataSet ds1 = new DataSet();
sqlDA.Fill(ds1, "tblinbound");
grdInbound.DataSource = ds1.Tables[0];
con.Close();

OriginalGriff 3-May-13 5:13am

No.
The parameter names must match: you have "@emailid" in one, and "emailid" in the other. Add the '@' to the parameter (and use the AddWithValue method instead of Add - it's clearer and more "modern")

[no name] 3-May-13 5:22am

thanks a lot sir.

OriginalGriff 3-May-13 5:30am

You're welcome!

**Orcun Iyigun** · Answer 2 · 2013-05-02T22:42:00

To avoid SQL Injection first thing is to use Parametrized Queries. For reference links please visit:
C# SqlParameter[^]
Using a Parameterized Query[^]
Using parameterized Queries in C#[^]
Using Parameterized Queries with the SqlDataSource (C#)[^]

The above links that I provided will give you a brief information how you can manage your queries.

Good luck,
OI

Pheonyx · Answer 3 · 2013-05-02T22:40:00

Solution 1

Have a read up on SQL Parameters.

http://www.dotnetperls.com/sqlparameter[^]

http://csharp-station.com/Tutorial/AdoDotNet/Lesson06[^]

Posted 2-May-13 22:40pm

Pheonyx

Updated 2-May-13 22:41pm

v2