Click here to Skip to main content
15,798,401 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more: , +

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.
Updated 10-Feb-21 22: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
Share this answer
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 :



and the code that I am using is :

declare @a varchar(100)

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

a.value('.', 'varchar(max)')
(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'

b.value('.', 'varchar(max)')
(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:
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:
SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;--'
Which SQL sees as three separate commands:
SELECT * FROM MyTable WHERE StreetAddress = 'x';
A perfectly valid SELECT
A perfectly valid "delete the table" command
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?
using (SqlConnection con = new SqlConnection(strConnect))
    using (SqlCommand cmd = new SqlCommand("INSERT INTO myTable (myColumn1, myColumn2) VALUES (@C1, @C2)", con))
        cmd.Parameters.AddWithValue("@C1", myValueForColumn1);
        cmd.Parameters.AddWithValue("@C2", myValueForColumn2);
Share this answer

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