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?
 
momi_sabagConnect With a Mentor Commented:
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
 
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
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
amxConnect With a Mentor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.