Solved

Sql String, sql injection

Posted on 2013-12-04
8
293 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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
 
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

776 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