lct2506 internet 2 further sql stored procedures
Post on 20-Dec-2015
215 views
TRANSCRIPT
![Page 1: LCT2506 Internet 2 Further SQL Stored Procedures](https://reader035.vdocuments.us/reader035/viewer/2022062421/56649d535503460f94a2fda7/html5/thumbnails/1.jpg)
LCT2506 Internet 2
Further SQL
Stored Procedures
![Page 2: LCT2506 Internet 2 Further SQL Stored Procedures](https://reader035.vdocuments.us/reader035/viewer/2022062421/56649d535503460f94a2fda7/html5/thumbnails/2.jpg)
LCT2506 Internet 2
Topics
Good practice in complex apps Complex queries Stored Procedures
![Page 3: LCT2506 Internet 2 Further SQL Stored Procedures](https://reader035.vdocuments.us/reader035/viewer/2022062421/56649d535503460f94a2fda7/html5/thumbnails/3.jpg)
LCT2506 Internet 2
Normalization
The idea that a row of a table should refer to a single entity– Bad to have multiple phone numbers in
a single table Leads to more efficient searching
and smaller databases Means you often need information
from more than one table/adamisherwood /normalization
![Page 4: LCT2506 Internet 2 Further SQL Stored Procedures](https://reader035.vdocuments.us/reader035/viewer/2022062421/56649d535503460f94a2fda7/html5/thumbnails/4.jpg)
LCT2506 Internet 2
Combining table contents
Example: Shopping Cart– Record item id, quantity, user id
If you want to display product details when showing cart contents, need more data
Accomplish using a JOIN as part of SELECT
![Page 5: LCT2506 Internet 2 Further SQL Stored Procedures](https://reader035.vdocuments.us/reader035/viewer/2022062421/56649d535503460f94a2fda7/html5/thumbnails/5.jpg)
LCT2506 Internet 2
Example using WHERE clause
SELECT product.Name, product.price, basket.quantity FROM product, basket
WHERE basket.prodId = product.prodId
AND basket.userId = ‘adam’
![Page 6: LCT2506 Internet 2 Further SQL Stored Procedures](https://reader035.vdocuments.us/reader035/viewer/2022062421/56649d535503460f94a2fda7/html5/thumbnails/6.jpg)
LCT2506 Internet 2
Example using INNER JOIN
SELECT product.Name, product.Price, basket.quantity
FROM basket INNER JOIN productON prodId = prodIdWHERE basket.userId = ‘adam’
![Page 7: LCT2506 Internet 2 Further SQL Stored Procedures](https://reader035.vdocuments.us/reader035/viewer/2022062421/56649d535503460f94a2fda7/html5/thumbnails/7.jpg)
LCT2506 Internet 2
Query Builder
Within Visual Studio complex SELECT queries can be built using Query Builder
Can build a static version and then plug in variables as needed.
MS products tend to use the INNER JOIN syntax
![Page 8: LCT2506 Internet 2 Further SQL Stored Procedures](https://reader035.vdocuments.us/reader035/viewer/2022062421/56649d535503460f94a2fda7/html5/thumbnails/8.jpg)
LCT2506 Internet 2
Performance tips
Use the ORDER BY clause for sortingselect * from products ORDER BY cost;
Can calculate query resultsselect top 6 id from users where id > 7
Other functions include count, max, sum
![Page 9: LCT2506 Internet 2 Further SQL Stored Procedures](https://reader035.vdocuments.us/reader035/viewer/2022062421/56649d535503460f94a2fda7/html5/thumbnails/9.jpg)
LCT2506 Internet 2
SQL Injection
![Page 10: LCT2506 Internet 2 Further SQL Stored Procedures](https://reader035.vdocuments.us/reader035/viewer/2022062421/56649d535503460f94a2fda7/html5/thumbnails/10.jpg)
LCT2506 Internet 2
What is SQL Injection?
A security exploit for the database layer of applications
Present when unfiltered user input passed directly to database
At best: cause application error At worst: allow hostile attacker to
discover private information and compromise your server
![Page 11: LCT2506 Internet 2 Further SQL Stored Procedures](https://reader035.vdocuments.us/reader035/viewer/2022062421/56649d535503460f94a2fda7/html5/thumbnails/11.jpg)
LCT2506 Internet 2
Match any
Rather than filter the table contents this query will select all rows
If the user types– anything’ OR ‘x’=‘x
Essentially a match any query
![Page 12: LCT2506 Internet 2 Further SQL Stored Procedures](https://reader035.vdocuments.us/reader035/viewer/2022062421/56649d535503460f94a2fda7/html5/thumbnails/12.jpg)
LCT2506 Internet 2
Not just read-only
Can alter contents…
![Page 13: LCT2506 Internet 2 Further SQL Stored Procedures](https://reader035.vdocuments.us/reader035/viewer/2022062421/56649d535503460f94a2fda7/html5/thumbnails/13.jpg)
LCT2506 Internet 2
Prevention
Use database permissions to restrict access rights (esp DROP)
Parse user inputs to remove ‘ characters
Avoid building SQL on the fly! Use prepared queries or stored
procedures instead
![Page 14: LCT2506 Internet 2 Further SQL Stored Procedures](https://reader035.vdocuments.us/reader035/viewer/2022062421/56649d535503460f94a2fda7/html5/thumbnails/14.jpg)
LCT2506 Internet 2
Stored Procedures
![Page 15: LCT2506 Internet 2 Further SQL Stored Procedures](https://reader035.vdocuments.us/reader035/viewer/2022062421/56649d535503460f94a2fda7/html5/thumbnails/15.jpg)
LCT2506 Internet 2
What is a stored procedure
A feature of MS SQL Server Allows database to pre-compile SQL
queries When data added in place of
variables, execution is very fast
![Page 16: LCT2506 Internet 2 Further SQL Stored Procedures](https://reader035.vdocuments.us/reader035/viewer/2022062421/56649d535503460f94a2fda7/html5/thumbnails/16.jpg)
LCT2506 Internet 2
Benefits
Performance: stored code is pre-computed, real savings if re-used
Reduced network traffic: only limited info passed between web and db servers
Efficient code reuse Multiple programs can use same proc Enhanced security: defeats SQL
Injection
![Page 17: LCT2506 Internet 2 Further SQL Stored Procedures](https://reader035.vdocuments.us/reader035/viewer/2022062421/56649d535503460f94a2fda7/html5/thumbnails/17.jpg)
LCT2506 Internet 2
Simple Example
![Page 18: LCT2506 Internet 2 Further SQL Stored Procedures](https://reader035.vdocuments.us/reader035/viewer/2022062421/56649d535503460f94a2fda7/html5/thumbnails/18.jpg)
LCT2506 Internet 2
Multiple Queries