Avatar of rp
rpFlag for Portugal 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

ASP.NETMicrosoft SQL Server 2008Visual Basic.NET

Avatar of undefined
Last Comment
Chinmay Patel

8/22/2022 - Mon
SOLUTION
JimFive

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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
rp

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.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
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
rp

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?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
Chinmay Patel

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question