I am inserting data via procedure but Still Some Body Do SQL Injection,Why it is happening?

Question

3.00/5 (1 vote)

See more:

Here is my procedure in ms Sql server 2008
create procedure spp_add_student
(
@Id nvarchar(50),
@Name nvarchar(100),
@Roll nvarchar(500)
)
as
begin
insert into tbl_add_student(Id,Name,Roll) values(@Id,@Name,@Roll)
end
and i am sending the parameters value via dataaccess layer in c#,
I read many forums but This parameterized process is the standard way then how they are doing sql injection in my application,
Please Help Me

Posted 1-Feb-16 6:27am

SAPTARSHI SENGUPTA

Add a Solution

Comments

F-ES Sitecore 1-Feb-16 12:34pm

How are you calling the SP?

Dave Kreskowiak 1-Feb-16 13:05pm

The problem isn't in the stored procedure. It's in how you're calling it from your application code. Show that instead.

ZurdoDev 1-Feb-16 14:46pm

As mentioned, the problem is in C#, not in SQL.

SAPTARSHI SENGUPTA 1-Feb-16 14:58pm

My function is
private int ExecuteNonproc(string storedproc, string[] paraname, string[] paravalue)
{
try
{
int result = 0;
SqlConnection cn = new SqlConnection(cnn.DbConnectionString);
SqlCommand cmd = new SqlCommand(storedproc, cn);
cmd.CommandType = CommandType.StoredProcedure;
for (int i = 0; i < paraname.Length; i++)
{
cmd.Parameters.AddWithValue(paraname[i], paravalue[i]);
}
if (cn.State == ConnectionState.Open)
{
cn.Close();
}
cn.Open();
result = cmd.ExecuteNonQuery();
cn.Dispose();
cn.Close();
return result;
}
catch
{
throw;
}
}
and then
public int createAccount(AccountModel model)
{
try
{
string[] paraname = { "@Id", "@Name", "@Roll"};
string[] paravalue = { model.Id, model.Name, model.Roll };
return ExecuteNonproc("spp_add_student", paraname, paravalue);
}
catch
{

throw;
}
}

2 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)

Verma.No.1 · Answer 1 · 2016-02-01T07:49:00

But, stored procedures do not, by themselves, necessarily protect against SQL injection. The usefulness of a stored procedure as a protective measure has everything to do with how the stored procedure is written.

As done by you,

SQL

insert into tbl_add_student(Id,Name,Roll) values(@Id,@Name,@Roll)

If someone assign value

Quote:
100); Drop TABLE tbl_add_student;

then it will be executed as

SQL

insert into tbl_add_student(Id,Name,Roll) values(@Id,@Name,100);
Drop TABLE tbl_add_student;

Resulting in dropping of Add_Student table.

You can check out these two links further...

(I)Do Stored Procedures Protect Against SQL Injection? - Brian Swan - Site Home - MSDN Blogs[^]
(II)How to prevent SQL Injection in Stored Procedures[^]

ZurdoDev · Answer 2 · 2016-02-01T08:49:00

Solution 2

1. Are you sure they are doing SQL injection? How do you know? Verify that first.
2. Use cmd.Parameters.AddWithValue("@parm1", parm1Value); when adding your parameters in C#. That protects most of sql injection issues.

Posted 1-Feb-16 8:49am

ZurdoDev

Comments

SAPTARSHI SENGUPTA 1-Feb-16 15:00pm

yes because Some one is drop my table and delete data because my db user name pass is only known by me and i changed it regularly

ZurdoDev 1-Feb-16 15:14pm

Another thing to do is to use an sql login that has limited rights, dbreader and dbwriter only. It's more work but well worth it if security is an issue.

Richard Deeming 1-Feb-16 15:37pm

Well the code you've posted in the comments to the question isn't obviously vulnerable.

Are you sure there aren't other data access methods that you haven't shown us, besides the ExecuteNonproc method?

Are you sure that none of the code that calls your methods uses string concatenation instead of passing the parameters in the two arrays?

Are you sure that none of your stored procedures use dynamic SQL (EXEC some-sql-string)?

ZurdoDev 1-Feb-16 15:43pm

I've also seen an issue with parameter overflows but it's much more advanced and few people know about it.

You can change your code to declare exactly what your parameters are and the type instead of using .AddWithValue() which is a more generic way of doing it.

Richard Deeming 1-Feb-16 15:58pm

I've only seen parameter overflows mentioned for dynamic SQL, not for properly parameterized queries. Have you seen something different that would escape AddWithValue / sp_executesql?

ZurdoDev 1-Feb-16 16:02pm

It was a few years ago so I don't recall the details. It seemed like there was something that if you defined in SQL a parameter of NVARCHAR(50) and then just allowed cmd.AddWithValue() without specifying 50 in c# there was a possibility of overflowing the parameter. I'll bet Microsoft fixed it by now anyway.

Richard Deeming 1-Feb-16 16:09pm

I'd be amazed if a bug like that made it through the first beta of .NET 1.0! Either the parameter string would be truncated, or you'd get a "string or binary data would be truncated" error. There's no way a parameter value should ever be able to overflow and be interpreted as code.

As I say, the only time I've seen this come up is with dynamic SQL that's not properly parameterized. For example, see the "Injection Enabled by Data Truncation" section in this article[^].