how can i use parameters to avoid sql attacks

Question

I have a project without any parameters used in SQL queries. Is there any solution so that i don't have to change the function and validate parameters from the Query string itself?

Query = "select * from tbl_Users where userName='"& textbox1.text &"' and password='"& textbox2.text &"' "
ds = obj.ExecuteQueryReturnDS(Query)

Function where query is passed:

Public Function ExecuteQueryReturnDS(ByVal stQuery As String) As DataSet
        Try
            Dim ds As New DataSet
            Using sqlCon As New SqlConnection(connStr)
                Dim sqlCmd As New SqlCommand(stQuery, sqlCon)
                Dim sqlAda As New SqlDataAdapter(sqlCmd)
                sqlCmd.CommandType = CommandType.Text
                sqlAda.Fill(ds)
            End Using
            Return ds
        Catch ex As Exception

        End Try
    End Function

I tried passing parameters into the function but the function is used in for other queries as well hence i cannot define the parameters inside the function .

Is there any work around

There is a Q&D thing you can do before changing any functions: replace all occurrences of `textbox1.text` with `textbox1.text.Replace("'", "''")` which will stop the easier kinds of attacks. — Mr Lister, Jun 23 '15 at 10:01
Quick and dirty. Note also that I said "before", not "instead of". — Mr Lister, Jun 23 '15 at 10:10

tezzo · Accepted Answer · 2015-06-23T09:58:10.540

I think the only solution is create a new function and gradually migrate to it.

Public Function ExecuteQueryReturnDS(ByVal cmdQuery As SqlCommand) As DataSet

    Try
        Dim ds As New DataSet
        Using sqlCon As New SqlConnection(connStr)
            cmdQuery.Connection = sqlCon
            Dim sqlAda As New SqlDataAdapter(cmdQuery)
            sqlAda.Fill(ds)
        End Using
        Return ds
    Catch ex As Exception
    End Try

End Function

cmdQuery is intended to be an SqlCommand to which you already added all the parameters you need.

score 1 · Answer 2 · answered Jun 23 '15 at 10:06

As an intermediate step before switching to a full parameterized application you could change your actual method to be able to receive an optional argument. This optional argument will be your SqlParameter array defined in the point where you call this query

Public Function ExecuteQueryReturnDS(ByVal stQuery As String, Optional ByVal prms As SqlParameter() = Nothing) As DataSet
   Try
       Dim ds As New DataSet
       Using sqlCon As New SqlConnection(connStr)
           Dim sqlCmd As New SqlCommand(stQuery, sqlCon)
           if Not prms Is Nothing Then
               sqlCmd.Parameters.AddRange(prms)
           End if
           Dim sqlAda As New SqlDataAdapter(sqlCmd)
           sqlCmd.CommandType = CommandType.Text
           sqlAda.Fill(ds)
       End Using
       Return ds
   Catch ex As Exception

   End Try
End Function

how can i use parameters to avoid sql attacks

2 Answers2