Solved

Prevent SQL Injection

Posted on 2009-05-19
5
611 Views
Last Modified: 2012-05-07
Hi to all,

I have the code below and I want to make sure that it is the best way to try and prevent SQL injection.

I am using Free text for my search in order to let the user type in a description of the item. I am also using full text search on that table.

What would I need to do more to prevent successful attacks on my DB?
Any suggestion would be welcomed.

Thanks
Public Shared Function LoadItemSearch(ByVal ItemDescription As String) As SqlDataReader

            Dim connection As New SqlConnection _

            (RMSConnectionString.StoreCon.ConnectionString)
 

            Dim SQLCommand As SqlClient.SqlCommand

            Dim strSQL As String

            connection.Open()

            Try
 

                strSQL = "Select  Item.ID, Item.ItemLookupCode, UPPER(Item.Description) As Description,    " & _

                            "Item.ExtendedDescription AS ItemDescription, Item.PictureName  " & _

                            " FROM Item INNER JOIN Department ON Item.DepartmentID = Department.ID    " & _

                            " WHERE (Item.Inactive = 0) And Item.WebItem = 0 And Item.ItemType <> 7 And Item.Quantity > 0   " & _

                            " And FreeText(Description, @Item) " & _

                            " ORDER BY SubDescription2 DESC"
 

                SQLCommand = New SqlClient.SqlCommand(strSQL, connection)

                SQLCommand.Parameters.Add(New SqlParameter("@Item", Data.SqlDbType.VarChar)).Value _

                = ItemDescription

               

                Dim MyReader As SqlDataReader = SQLCommand.ExecuteReader

                Return MyReader

                connection.Close()

                SQLCommand.Dispose()

                connection.Dispose()

            Catch Exp As SqlClient.SqlException

                Throw Exp

            End Try

        End Function

Open in new window

0
Comment
Question by:ALawrence007
5 Comments
 
LVL 15

Assisted Solution

by:mohan_sekar
mohan_sekar earned 125 total points
ID: 24423138
0
 
LVL 2

Accepted Solution

by:
d1rtyw0rm earned 200 total points
ID: 24423197
You are doing great by using parameterised queries.

This is where the SQL Command uses a parameter instead of injecting the values directly into the command. The particular second-order attack above would not have been possible if parameterised queries had been used.

-----------------------------------------------------------

Stored Procedures add an extra layer of abstraction in to the design of a software system. This means that, so long as the interface on the stored procedure stays the same, the underlying table structure can change with no noticeable consequence to the application that is using the database. This layer of abstraction also helps put up an extra barrier to potential attackers. If access to the data in SQL Server is only ever permitted via stored procedures, then permission does not need to be explicitly set on any of the tables. Therefore, none of the tables should ever need to be exposed directly to outside applications. For an outside application to read or modify the database, it must go through stored procedures. Even though some stored procedures, if used incorrectly, could potentially damage the database, anything that can reduce the attack surface is beneficial.

Stored procedures can be written to validate any input that is sent to them to ensure the integrity of the data beyond the simple constraints otherwise available on the tables. Parameters can be checked for valid ranges. Information can be cross checked with data in other tables.

--------------------------------------------------------------------

Encrypt sensitive data.
Access the database using an account with the least privileges necessary.
Install the database using an account with the least privileges necessary.
Ensure that data is valid.
Do a code review to check for the possibility of second-order attacks.
Use parameterised queries.
Use stored procedures.
Re-validate data in stored procedures.
Ensure that error messages give nothing away about the internal architecture of the application or the database.


http://unixwiz.net/techtips/sql-injection.html

0
 
LVL 21

Assisted Solution

by:Tapan Pattanaik
Tapan Pattanaik earned 125 total points
ID: 24423204
0
 
LVL 48

Assisted Solution

by:jpaulino
jpaulino earned 50 total points
ID: 24426284
You have allot of links provided already, but basically you need to use Stored Procedures with parameters passed to him, to ensure that you're SQL Injection safe.
0
 

Author Closing Comment

by:ALawrence007
ID: 31583047
Thank you SO much guys!
I added Stored procs to make my site more secure
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Suggested Solutions

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

932 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

12 Experts available now in Live!

Get 1:1 Help Now