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 to perform this assessment task. The sample database for this module is called BikeStores. Download the Database Design Diagram below.
Bikestores Database Design Diagram
Use the BikeStores database design diagram for your stored procedure assessment..
Please copy each SQL statement into a Microsoft® Word® document. Below that, enter a screenshot of the execution of the SQL showing the code and the resulting output. Below that, enter text explaining the SQL statement and outcome. What has occurred? Was there an adverse impact from the SQL statement execution?
Task 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 GetUserWithBind. 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 GetUserWithBin
Sample Solution
