asked on

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

ASKER CERTIFIED SOLUTION

momi_sabag

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rito1

ASKER

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

momi_sabag

since the only content that you receive as input will be past to the sql statement as parameters, your code will be secured

SOLUTION

amx

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rito1

ASKER

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

rito1

ASKER

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

rito1

ASKER

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

rito1

ASKER

Hi All,

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

Many thanks,

Rit