Click here to Skip to main content
15,890,375 members
Search within:


How do I change a stored procedure that has a normal select statement to prevent SQL injection attacks?
Please Sign up or sign in to vote.
1.00/5 (2 votes)
See more:
ASP.NET
SQL
If I have a code SQL like that

SQL
create procedure selectStatment
as
begin 
   select * from spacific_table;
end;


how do I ensure there's no SQL injection possible?

What I have tried:

I tried to add parameter to the procedure but I it doesn't works
Posted
Updated 23-Aug-19 3:20am
772.9K
v2
Add a Solution
Comments
Patrice T 23-Aug-19 7:26am    
"i tried to add prameter to the procedure but i it doent works"
Show what you tried, this is the interesting part.

2 solutions

Calling a Stored Procedure and appending a string to the command has nearly the same level of SQL Injection risk.

To add a parameter to your stored procedure you add it in at the declaration level. Note that the parenthesis (in either case below) are not required but I use them for readability
SQL
CREATE PROCEDURE selectSttment (
  @ParamName1 INT
) as
BEGIN 
  SELECT *
  FROM   spacific_table
  WHERE  (ID = @ParamName1)
END
This is my general way of calling a stored procedure in C# (VB very similar). Using the using block will do a cleanup of all the resources used within it, so most of the code in finally is superfluous.. but it is good practice to use all three in the try...catch...finally
C#
string CmdName = "selectSttment";
int IDtoFind = 123;
SqlDataReader reader;
using (SqlConnection conn = new SqlConnection("ConnectionInfo")) {
   using (SqlCommand cmd = new SqlCommand(CmdName, conn)) {
      cmd.CommandType = CommandType.StoredProcedure;
      cmd.Parameters.AddWithValue("@ParamName1", IDtoFind);

      try {
         conn.Open();
         reader = cmd.ExecuteReader();
         // your script to read...
      }
      catch (SqlException sx) {
         // your error handling for SQL specific exception
      }
      catch (Exception ex) {
         // your error handling for general exception
      }
      finally {
         // cleanup code regardless of success
         cmd.Dispose();
         conn.Close();
         conn.Dispose();
      }
   }
}
 
Share this answer
 
Posted
Comments
Maciej Los 23-Aug-19 8:08am    
5ed!
Richard Deeming 23-Aug-19 8:14am    
With the using blocks, all of the code in the finally block is unnecessary. :)

But you should be wrapping the SqlDataReader in a using block too.
const string CmdName = "selectSttment"; // Make it "const" so you can't inject values.
int IDtoFind = 42;

using (SqlConnection conn = new SqlConnection("..."))
using (SqlCommand cmd = new SqlCommand(CmdName, conn))
{
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("@ParamName1", IDtoFind);
    
    conn.Open();
    using (SqlDataReader reader = cmd.ExecuteReader())
    {
        while (reader.Read())
        {
            ...
        }
    }
}
MadMyche 23-Aug-19 10:06am    
Agreed, and I did preface the code so most of the code in finally is superfluous.
Member 14479161 23-Aug-19 8:35am    
you are passing the parameter on the asp.net file but what if i dont want to pass the parameter 
const string CmdName = "selectSttment"; // Make it "const" so you can't inject values.
int IDtoFind = @ParamName1;

using (SqlConnection conn = new SqlConnection("..."))
using (SqlCommand cmd = new SqlCommand(CmdName, conn))
{
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("@ParamName1", IDtoFind);
    
    conn.Open();
    using (SqlDataReader reader = cmd.ExecuteReader())
    {
        while (reader.Read())
        {
            ...
        }
    }
}
<pre>
MadMyche 23-Aug-19 10:12am    
You asked about adding a parameter, so I showed you how.
If you want that parameter to be optional, then you set a default in the Stored Procedure declaration and adjust the procedure accordingly
CREATE PROCEDURE selectSttment (
   @ParamName1 INT = -1
) as
BEGIN
   IF ( @ParamName1 > 0) BEGIN
      SELECT *
      FROM   spacific_table
      WHERE  (ID = @ParamName1)
   END; ELSE BEGIN
      SELECT *
      FROM   spacific_table
   END
END
That SELECT statement (and hence the Stored Procedure) is not vulnerable to SQL Injection, because it doesn't use any strings at all, much less any that might contain harmful material. All it does is select all columns and all rows from a table; everythign is fixed in advance.

When you concatenate strings, you cause problems because SQL receives commands like:
SQL
SELECT * FROM MyTable WHERE StreetAddress = 'Baker's Wood'
The quote the user added terminates the string as far as SQL is concerned and you get problems. But it could be worse. If I come along and type this instead: "x';DROP TABLE MyTable;--" Then SQL receives a very different command:
SQL
SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;--'
Which SQL sees as three separate commands:
SQL
SELECT * FROM MyTable WHERE StreetAddress = 'x';
A perfectly valid SELECT
SQL
DROP TABLE MyTable;
A perfectly valid "delete the table" command
SQL
--'
And everything else is a comment.
So it does: selects any matching rows, deletes the table from the DB, and ignores anything else.

So you are only vulnerable when you concatenate strings, and you don't do that at all!
 
Share this answer
 
Posted
Comments
Member 14479161 23-Aug-19 7:50am    
that means if i concatinating a a select statment with a value that comes from a text box using the '+' this is ulnerable to SQL Injectio in asp.net
MadMyche 23-Aug-19 7:53am    
Yes; and this vulnerability is language agnostic.
It affects almost all programming languages that will talk to any flavor of RDBMSSQL.
Add a Solution

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

  Print Answers RSS
Top Experts
Last 24hrsThis month
Related Questions
SQL Injection Possible in Non-Dynamic Query in Stored Procedures
Do this SQL code prevent SQL injection
How do I resolve SQL injection attacks when queries are fetched from resource(.resx) file
Prevent SQL Injection SP attached.
How to prevent SQL injection with this method
If write dynamic query inside store procedure will it have threat of SQL Injection attack?
Sql stored procedure select statement
How to put the result of a stored procedure in a condition in the select statement?
Prevent SQL injection while accepting user input.
Select statement with variable as parameter for sql stored procedure

Advertise
Privacy
Cookies
Terms of Use
Last Updated 23 Aug 2019
Layout: fixed | fluid
Copyright © CodeProject, 1999-2024
All Rights Reserved.

Web04 2.8:2024-04-02:1

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900