Sql parametrized queries

Index
• Definition
• Why Parameterized Queries?
• Disadvantages
• Parameterized queries VS Stored Procedures
• Parameterized queries using Vb.net
2

Definition
• “Parameterized query (also known as
prepared statement) is a technique of query
execution which separates a query string from
query parameters values”.
3

Protection against SQL Injection
Attack

• If the contents of a text field are just passed to
SQL Server and executed, then that text field
can contain a complete new query that does
something totally different.
• A parameter works because it‘s treated as a
literal value rather than executable code. And
it's also checked for type and length.
Why Parameterized Queries Protection against SQL Injection Attack6

• A typical SQL injection string would have to be
much longer, and the SqlParameter class
would throw an exception.

Example
• Dynamic Sql Query
• The command built in the application is :
“SELECT * FROM DbCustomers.dbo.Customers WHERE
FirstName = ' “ + @Firstname + “';”
• If we searched a Customer table (first name
column) for this value for this value :
Ali';Truncate Table dbo.Customers;--

• The CommandText will be as shown below
SELECT * FROM DbCustomers.dbo.Customers
WHERE FirstName = 'Ali'; Truncate Table
dbo.Customers;--' ;

• The Command Text is composed of 4 parts:
1. SELECT * FROM DbCustomers.dbo.Customers
WHERE FirstName = 'Ali';
2. Truncate Table dbo.Customers;
3. --' ;

• Parameterized SQL Query
if we made a search on the same value
Ali';Truncate Table dbo.Customer;--
• This value will be passed as a parameter
@FirstName (varchar(255),Text)
Why Parameterized Queries Protection against SQL Injection Attack
11

12 Why Parameterized Queries Protection against SQL Injection Attack
• The command text will be as shown below
•SELECT * FROM DbCustomers.dbo.Customers WHERE
FirstName = @firstname;

• [Firstname] will be compared with the value:
“Ali';Truncate Table dbo.Customer;--”
13 Why Parameterized Queries Protection against SQL Injection Attack

• From the point of view of a developer, there is
no difference between dynamic and
parameterized queries, but there are many
from the point of view of SQL Server.
Why Parameterized Queries Performance Implications15

• When using dynamic queries the entire query
has to be constructed and compiled by SQL
Server every time
• When using parameterized queries SQL Server
generates a query execution plan just once
and then plugs the parameter value into it.

Simple Parameterization feature
• In cases in which values are specified explicitly, as
in query below, SQL Server invokes a feature
known as ‘simple parameterization’.
SELECT ZipCode, Latitude, Longitude, City, State,
Country FROM dbo.UsZipCodes WHERE ZipCode =
'54911'
• Simple parameterization is designed to reduce
the resource cost associated with parsing SQL
queries and forming execution plans by
automatically parameterizing queries.

Simple Parameterization feature
• With simple parameterization, SQL Server
actually creates two execution plans for this
query.
• The first execution plan is a shell plan
containing a pointer to the second execution
plan.

Experiments
• We will show 2 experiments made by David
Berry (worked extensively with both Oracle
and SQL Server with a special interest in
database performance tuning) concerning
performance implications of parameterized
queries.

• David berry focused on four different metrics
for the analysis:
– The total elapsed time use to process n queries.
– The total CPU time used by SQL Server to process
n queries.
– The total number of plans in SQL Server’s plan
cache after processing n queries.
– The total amount of memory used by SQL Server’s
plan cache after processing n queries.

A Most Basic Query
• We created a table called UsZipCodes that
contains a record for every zip code in the
United States along with the associated city,
state, longitude and latitude. In total, there
are 42,741 rows in the table.

• For both dynamic SQL and parameterized SQL,
we will execute a query that selects a single
record from the table by querying on the zip
code itself.
• This query will then be repeated 5000 times
with a different zip code each time.
• Executing this query 5000 times will comprise
a single test run. To make sure the results are
repeatable, we have performed this test 20
times.

• Parameterized queries are shown to run about
33% faster than the dynamic SQL queries
• The dynamic SQL uses roughly 3.3 times the
amount of CPU on the database server as the
parameterized query.
• The table below shows the results for the
average of all 20 runs.

• SQL Server is using simple parameterization to
automatically parameterize the dynamic SQL.
Inspecting the plan cache data shows that for
dynamic SQL, there are 5000 different shell
plans and a single auto-parameterized
execution plan.

Query with a Join and an Order By
• This experiment is using the AdventureWorksLT
database
• Since the AdventureWorksLT contains a small
data sample size, we used a data generator to
insert data into the primary tables in the
database.
• In this test database, the SalesOrderHeader table
contains about 650,000 rows and the
SalesOrderDetail table around 8.5 million rows.
This larger dataset will provide more realistic test
conditions for our test.

• Consider the query below:
SELECT h.SalesOrderID, h.OrderDate, h.SubTotal As
OrderSubTotal, p.Name AS ProductName,
d.OrderQty, d.ProductID, d.UnitPrice, d.LineTotal
FROM SalesLT.SalesOrderHeader h
INNER JOIN SalesLT.SalesOrderDetail d
ON h.SalesOrderID = d.SalesOrderID
INNER JOIN SalesLT.Product p
ON d.ProductID = p.ProductID
WHERE h.CustomerID = @customer_id
AND h.OrderDate > @start_date
AND h.OrderDate < @end_date
ORDER BY h.SalesOrderID, d.LineTotal;

• the query was executed 100 times. A total of
20 test runs each were conducted. The results
are shown in the table below
• The parameterized version of the query has an
elapsed time that is 10.8% less than its dynamic
SQL counterpart
• The dynamic SQL version of the query uses 3.7
times more CPU than the parameterized version

Experiment Conclusion
• The results show that on SQL Server, there is a
measurable performance impact of using
parameterized queries versus dynamic SQL. The
difference in performance can be seen in all every
aspect of performance measured. By choosing
dynamic SQL, an application will see response
times that are slower than if parameterized
queries are used. This will ultimately be reflected
in the response time of the application, perhaps
giving the user the impression that application
performance is sluggish.

SINGLE AND DOUBLE QUOTES
PROBLEMS
29

• Most programmers find parameterized
queries easier to avoid errors when they don‘t
have to keep track of single and double quotes
to construct SQL strings using VB.NET
variables.
Why Parameterized Queries Single and Double quotes problems30

QUERIES ARE EMBEDDED INTO
APPLICATION CODE
32

• One of the main disadvantages is that since
the queries are embedded into your
application code, you could end up with the
same query in multiple places.
This duplication can be eliminated by creating a
central location to store your queries.
• Query are created for one application.
• DBA have no control over the code which
executes on application, which can be a
unsafe for large databases.
Disadvantages queries are embedded into application code33

PARAMETERIZED QUERIES
VS
STORED PROCEDURES
34

Parameterized queries
• DBA’s have less control on
queries.
• Parameters can be add
while building query string.
• Good Execution time.
• Used by single application
Stored procedures
• DBA’s have a very good
control on queries
• Fixed parameters number
• Good Execution time.
• Created once used by many
applications
• More secure; queries are
written on the data layer
Parameterized queries VS Stored Procedures35

• It’s up to you to choose working with
Parameterized queries or Stored Procedures
(According to the application and data
properties and many other factors…)
36
Parameterized queries VS Stored Procedures

PARAMETERIZED QUERIES USING
VB.NET
37

38 Parameterized queries using Vb.net

Sql parametrized queries

Recommended

More Related Content

What's hot (20)

Similar to Sql parametrized queries (20)

More from Hadi Fadlallah (20)

Recently uploaded (20)

Sql parametrized queries

Editor's Notes