[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 215
  • Last Modified:

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

0
rito1
Asked:
rito1
  • 5
  • 2
2 Solutions
 
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
 
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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now