Solved

Sql String, sql injection

Posted on 2013-12-04
8
291 Views
Last Modified: 2013-12-09
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
Comment
Question by:rflorencio
  • 4
  • 2
  • 2
8 Comments
 
LVL 15

Assisted Solution

by:JimFive
JimFive earned 50 total points
ID: 39696126
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
 
LVL 27

Expert Comment

by:Chinmay Patel
ID: 39696137
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
 

Author Comment

by:rflorencio
ID: 39696424
Then i should create a parameter for SqlTable, SqlCat, SqlC correct?
0
 
LVL 27

Expert Comment

by:Chinmay Patel
ID: 39696440
For SqlTable Yes.

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

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 15

Expert Comment

by:JimFive
ID: 39696490
I don't think you can create a parameter for the table.
0
 
LVL 27

Expert Comment

by:Chinmay Patel
ID: 39697469
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
 

Author Comment

by:rflorencio
ID: 39697696
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
 
LVL 27

Accepted Solution

by:
Chinmay Patel earned 450 total points
ID: 39697715
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

937 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now