Click here to Skip to main content
15,125,547 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
See more:
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 4:20am
v2
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.

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();
      }
   }
}
   
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!
   
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.

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




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