asked on

Sql String, sql injection

I have a function to build an SQL statement, that after is sending to another function to display the results in a gridview. To avoid sql injection attacks i think I should use parameters. In this case as sending the SQL statement already built for a DataAdapter, what can I do.

Call Function GetGrid(mygrid, GetQuery ())

Public Shared Function GetQuery () As String
        GetQueryFromCms = ""
        Dim Idcat As String = HttpContext.Current.Request.QueryString("IdCat")
        Dim IdSubcat As String = HttpContext.Current.Request.QueryString("IdSubCat")
        Dim SqlTable  As String = HttpContext.Current.Request.QueryString("Tb")
        Dim SqlStr As String = ""
        Dim Search As String = HttpContext.Current.Request.QueryString("Search")
        Dim SqlC As String = ""
        '-----
        
       SqlStr = "Select * from " & SqlTable  & " where " & SqlCat & SqlC & " order by id desc"
        
         GetQueryFromCms = SqlStr
 
   End Function

Open in new window

Public Shared Function GetGrid(ByVal xGrid As GridView, ByVal xSqlString As String) As GridView
    
Dim conn As SqlConnection = DB.DbInitConn()
    Dim dataset As New DataSet
    Dim adapter As SqlDataAdapter
    Try
        
        adapter = New SqlDataAdapter(xSqlString, conn)
        adapter.Fill(dataset, "table")
        xGrid.DataSource = dataset
        '----------------
        xGrid.DataBind()
    Catch err As Exception
        GnErrorMessage("GetGridView()", ErrorToString().ToString)
    Finally
        conn.Close()
    End Try
    Return xGrid
End Function

Open in new window

SOLUTION

JimFive

membership

This solution is only available to members.

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

Start Free Trial

Chinmay Patel

Here is the link for your ref: http://msdn.microsoft.com/en-us/library/bbw6zyha(v=vs.110).aspx

Basically you will replace your parameters and append @ to note parameters and later on you will add required parameters for a given query.

ASKER

Then i should create a parameter for SqlTable, SqlCat, SqlC correct?

Chinmay Patel

For SqlTable Yes.

Can you tell me the possible values for SqlCat and SqlC? I saw the query and seems something is off.

JimFive

I don't think you can create a parameter for the table.

Chinmay Patel

Yes JimFive is right. You will have to create a Stored Procedure that can accept required parameters and then execute them.

CREATE PROCEDURE [dbo].[spn_executeSQLExample] 
(
    @tableName VARCHAR(100)
)
AS
BEGIN
    DECLARE @sqlStatement AS NVARCHAR(500)
    SET @sqlStatement = 'SELECT COUNT(*) FROM ' + @tableName
 
    exec sp_executesql @sqlStatement
END

Open in new window

Source:
http://www.codeproject.com/Questions/257322/Pass-tablename-as-parameter

ASKER

Honestly if I could i prefere parameters, however the table is not a variable, and sqlstring is not correct, sorry, my mistaque. The string is somethink like this:

SqlStr = "Select * from customer " & " where cat=" & IdCat & " and scat=" & IdSubcat & " order by id desc"

With this string can i create parameters?

ASKER CERTIFIED SOLUTION

Chinmay Patel

membership

This solution is only available to members.

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

Start Free Trial