Task 2 - Testing Security Flaws

Purpose

Bike Stores is implementing a new website to allow online bike purchases. In support of

this, a stored procedure called GetUser has been created to retrieve a customer record

using the customer's first name, last name, and password supplied from a website form.

From your research on database security, you notice that there is a serious security

concern with the GetUser procedure. You have established a series of SQL statements to

test for GetUser security flaws. These statements will be used with two temporary tables

called staffs_temp and order_items_temp, which are copies of

the staffs and order_items tables, respectively.

Assessment Instructions

You must have SQL Server Express and SQL Server Management Studio (SSMS) installed/nTask 2.1 - Dynamic SQL Statements Without Binding

Execute the individual SQL statements contained within the following text file in a Microsoft

SSMS query window:

• BikeStores SQL Injection Commands

After executing these statements, explain why the GetUser stored procedure is

problematic. Did any unauthorized data modifications occur? Did data corruption occur?

Were any tables improperly dropped from the database?

Task 2.2 - Dynamic SQL Statements With Binding

Based on the Task 2.1 results, the GetUser stored procedure has been revised and

replaced with a stored procedure called GetUserWith Bind. Execute the individual SQL

statements contained within the following text file in a Microsoft SSMS query window:

• BikeStores SQL Injection Commands With Binding

After executing these statements, explain whether the GetUserWith Bind stored procedure

has solved the security issues uncovered in Task 2.1. If the security issues have been

resolved, explain how it was accomplished. Perform research and briefly describe the

binding parameter concept. Below is a table contrasting the use of "execute" versus

"execute sp_executesql" in Microsoft SQL Server.