SQL injection is a code injection technique, used to attack data-driven applications, in which malicious SQL statements are inserted into an entry field for execution (e.g. to dump the database contents to the attacker).SQL injection allows an attacker to create, read, update, alter or delete data stored in the back-end database. In its most common form, a SQL injection attack gives access to sensitive information such as social security numbers, credit card numbers or other financial data. According to Veracode’s State of Software Security Report, SQL injection is one of the most prevalent types of web application security vulnerability. Today, I will show you how to prevent sql injection attacks at stored procedure level. Now write the following code:
CREATE PROCEDURE SearchHistory_Dynamic (
@Product int = NULL,
@OrderID int = NULL,
@TransactionType char(1) = NULL,
@Qty int = NULL)
DECLARE @sSQL NVARCHAR(2000), @Where NVARCHAR(1000) = ''
SET @sSQL = 'SELECT ProductID, ReferenceOrderID, TransactionType, Quantity, TransactionDate, ActualCost
from Production.TransactionHistory '
IF @Product is not null
SET @Where = @Where + 'AND ProductID = @_Product '
IF @OrderID is not null
SET @Where = @Where + 'AND ReferenceOrderID = @_OrderID '
IF @TransactionType IS NOT NULL
SET @Where = @Where + 'AND TransactionType = @_TransactionType '
IF @Qty IS NOT NULL
SET @Where = @Where + 'AND Quantity = @_Qty '
IF LEN(@Where) > 0
SET @sSQL = @sSQL + 'WHERE ' + RIGHT(@Where, LEN(@Where)-3)
EXEC sp_executesql @sSQL,
N'@_Product int, @_OrderID int, @_TransactionType char(1), @_Qty int',
@_Product = @Product, @_OrderID = @OrderID, @_TransactionType = @TransactionType, @_Qty = @Qty
Best SQL Server Hosting Recommendation
One of the most important things when choosing a good SQL Server hosting is the feature and reliability. HostForLIFE is the leading provider of Windows hosting and affordable SQL Server , their servers are optimized for PHP web applications such as the latest SQL Server version. The performance and the uptime of the SQL Server hosting service are excellent and the features of the web hosting plan are even greater than what many hosting providers ask you to pay for. At HostForLIFE.eu, customers can also experience fast SQL Server hosting. The company invested a lot of money to ensure the best and fastest performance of the datacenters, servers, network and other facilities. Its datacenters are equipped with the top equipments like cooling system, fire detection, high speed Internet connection, and so on. That is why HostForLIFE.eu guarantees 99.9% uptime for SQL Server. And the engineers do regular maintenance and monitoring works to assure its SQL Server hosting are security and always up.