Click here to Skip to main content
14,930,017 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more: , +
Hi,

I have a legacy web application which accepts user input from textboxes.
In one of the screens where user can add new records to the database,
need to prevent SQL injection by making not much changes to existing code.

Existing code is as below :

Current SQL Statement

Insert into ##TableName## (" + varResult1 + ") Values (" + varResult2 + ")

which translates to :

Insert into ##TableName## ([Deptname],[Location],[Column1],[Column2],[Column3]) values ('testval1','testval2','30','50','60')

Where the values i.e. 'testval1','testval2','30','50','60' are all coming from textboxes in the UI screen,

Behind the scenes, the above insert statement is assigned to a string variable @query
in a stored proc and Execute(@query) is being called.

Any way we could avoid SQL injection making minimum changes to existing code?

Any help would be much appreciated.

What I have tried:

Tried parametrizing the values inputted by the user however it does not seem to work as a comma separated list is being passed to the stored procedure.
Posted
Updated 10-Feb-21 21:19pm

You should split both strings (varResult1 and varResult2) with String.Split method into arrays of strings. First array, from varResult1, will be parameter names, second will be parameter values. Then add parameters to procedure parameters list one by one.

Hope it helps
   
Comments
aakar 11-Feb-21 21:46pm
   
Hi, I have split the 2 strings into 2 table variables. Now how do I loop through these and add the params to the SP one by one?
The 2 variables are as below :

@a
-----
[Deptname]
[Location]
[Column1]
[Column2]
[Column3]


@b
-------
testval1,
testval2,
30,
50,
60

and the code that I am using is :

declare @a varchar(100)

set @a = '[Deptname],[Location],[Column1],[Column2],[Column3]'

select
a.value('.', 'varchar(max)')
from
(select cast('<m>' + REPLACE(@a, ',', '<m>') + '' AS XML) as col) as A
CROSS APPLY A.col.nodes ('/M') AS Split(a)



declare @b varchar(100)

set @b = 'testval1','testval2','30','50','60'

select
b.value('.', 'varchar(max)')
from
(select cast('<m>' + REPLACE(@b, ',', '<m>') + '' AS XML) as col) as B
CROSS APPLY B.col.nodes ('/M') AS Split(B)

Can you please help?
Uroš Šmon 12-Feb-21 2:03am
   
As Griff pointed out in second solution, never do this on SP level. Use his solution and add parameters from both column names and values array.
Never 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. Always use Parameterized queries instead.

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 ALWAYS use parameterized queries! Or be prepared to restore your DB from backup frequently. You do take backups regularly, don't you?
C#
using (SqlConnection con = new SqlConnection(strConnect))
    {
    con.Open();
    using (SqlCommand cmd = new SqlCommand("INSERT INTO myTable (myColumn1, myColumn2) VALUES (@C1, @C2)", con))
        {
        cmd.Parameters.AddWithValue("@C1", myValueForColumn1);
        cmd.Parameters.AddWithValue("@C2", myValueForColumn2);
        cmd.ExecuteNonQuery();
        }
    }
   

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