Secure Dynamically built SQL

Hi All,

I have recently had the joy of securing someone elses code from SQL injection attacks.

I have been creating ADO parameters and stored procedure to tighten the application up but with the following code, I cannot work out a good approach to securing this up as it is dynamically built using VB.net?

Please could anyone suggest an approach and even syntax to achieve this?
Dim _strq as String = request("v")
Dim _strSomewords() As String
Dim _strSomeValues() As String
Dim strSeparators() As Char = {"^"}
 
_strSomewords = _strq.Split(strSeparators)
 
Dim i As Integer = 0
 
Dim _sql As String = "SELECT * FROM tbl_tasks WHERE "
 
Do While i <= _strSomewords.GetUpperBound(0)
 
	if i > 0 then 
		_sql = _sql & " AND "
	Else
		_sql = _sql & "("
	End If
 
	_strSomeValues = _strSomewords(i).Split("}")
				
        _sql = _sql & "TaskID = " & _strSomeValues(0) & " "
 
	i = i + 1
 
Loop
 
_sql = _sql & ") AND 20 > task_score"

Open in new window

LVL 1
rito1Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

momi_sabagCommented:
try this:
create a prepared statement
in the loop, instead of adding
 _sql = _sql & "TaskID = " & _strSomeValues(0) & " "
add
 _sql = _sql & "TaskID = ?"

once you are done with this loop, perform that loop again and just add the parameters to the prepared statement
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rito1Author Commented:
Thanks momi_sabag,

Just to confirm, I am to build the SQL statement within my code as it currently is but with your alteration and then accompany the SQL statement with parameters.

Will just supplying parameters without moving the SQL statement to a store procedure still prevent SQL injection attacks?

Many thanks,

Rit
0
momi_sabagCommented:
since the only content that you receive as input will be past to the sql statement as parameters, your code will be secured
0
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

amxCommented:
Looking at your code i'm assuming taskID is an int or something similar, so you could try a CInt before adding the value to the string to help prevent an injection attack
0
rito1Author Commented:
thanks Both.

amx, as I have been updating this app with parameters, to keep continuity, I will be keeping to this though thanks for the advice.

momi_sabag, I will give your solution a go and let you know how I get on.

Many thanks,

Rit
0
rito1Author Commented:
Hi momi_sabag,

I am struggling to figure out how to add a parameter to an inline SQL statement.

Please could you just provide example syntax for the parameter so that I can see how it works with  "_sql = _sql & "TaskID = ?"

Many thanks,

Rit
0
rito1Author Commented:
Hi,

Attached is my code so far to include the parameters but the error I receive is:

Incorrect syntax near '?' - which is referring to the ? in:

_sql = _sql & "TaskID = ? "

I then changes this ? to @TaskID and got the following error:

Must declare the variable '@QuestionID'

Does anyone have an ideas what I am doing wrong?

Many thanks,

Rit
Dim _conn As SqlConnection = New SqlConnection()
            _conn.ConnectionString = _conn_str
 
 
Dim _strq as String = request("v")
Dim _strSomewords() As String
Dim _strSomeValues() As String
Dim strSeparators() As Char = {"^"}
 
_strSomewords = _strq.Split(strSeparators)
 
Dim i As Integer = 0
 
Dim _sql As String = "SELECT * FROM tbl_tasks WHERE "
 
Do While i <= _strSomewords.GetUpperBound(0)
 
        if i > 0 then 
                _sql = _sql & " AND "
        Else
                _sql = _sql & "("
        End If
 
        _strSomeValues = _strSomewords(i).Split("}")
                                
        _sql = _sql & "TaskID = ? "
 
        i = i + 1
 
Loop
 
_sql = _sql & ") AND 20 > task_score"
 
 
_conn.Open()
 
                Dim _cmd As Object = New SqlCommand(_sql, _conn)
                
                Do While i <= _strSomewords.GetUpperBound(0)
                    _strSomeValues = _strSomewords(i).Split("}")
                    _cmd.Parameters.Add("@TaskID", SqlDbType.Int).Value = Convert.ToString(_strSomeValues(0))
                    i = i + 1
                Loop
 
                Dim _dr As SqlDataReader = _cmd.ExecuteReader()
 
                RepeaterTasks.DataSource = _dr
                RepeaterTasks.DataBind()
 
                _dr.Close()
                _conn.Close()

Open in new window

0
rito1Author Commented:
Hi All,

Can anyone see where I am going wrong with the above code. It should be adding parameters dynamically.

Many thanks,

Rit
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.