[Webinar] Streamline your web hosting managementRegister Today

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

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

0
rflorencio
Asked:
rflorencio
  • 4
  • 2
  • 2
2 Solutions
 
JimFiveCommented:
Think about this:
What will stop someone from typing into the table name field:
TableName; DELETE FROM TableName;--

And what that will do to your sqlstr.
0
 
Chinmay PatelEnterprise ArchitectCommented:
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.
0
 
rflorencioAuthor Commented:
Then i should create a parameter for SqlTable, SqlCat, SqlC correct?
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Chinmay PatelEnterprise ArchitectCommented:
For SqlTable Yes.

Can you tell me the possible values for SqlCat and SqlC? I saw the query and seems something is off.
0
 
JimFiveCommented:
I don't think you can create a parameter for the table.
0
 
Chinmay PatelEnterprise ArchitectCommented:
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
0
 
rflorencioAuthor Commented:
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?
0
 
Chinmay PatelEnterprise ArchitectCommented:
Yes. Absolutely :). Cat and scat will be parameters. You can refer to the link I have sent earlier. As shown in the link you will change you query and then add 2 parameters to the SqlCommand OR Adapter.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

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