“ ” by is licensed under . Medicine 1 marosh CC BY-NC-ND 2.0 Looking for a script to find possible SQL injection vulnerabilities on your server? Scroll to the bottom of this post. . Watch this content on YouTube . The infamy of this attack has even made its way into the popular . OWASP names SQL injection as the #1 vulnerability for web applications XKCD comic What is SQL injection? A query is vulnerable to SQL injection if a user can run a query other than the one that was originally intended. SQL Sometimes SQL injection is easier to understand with an example. : Let’s use this table of registered users And then let’s create a simple stored procedure that will query that table: The important thing to note in the query above is that we are generating a dynamic SQL statement; that is, we are building the SQL query and then we are executing it. string, Imagine this stored procedure is running in order to display a “Welcome <Full Name>!” message in our app — a website visitor types in their and we execute the stored procedure to return their full name. @ParmUserName Here’s our code that calls the stored procedure: EXEC dbo.sp_GetFullName 'TFly37' And result: Cool. No problems so far. However, what if our user decides to pass in the following value as their username? EXEC dbo.sp_GetFullName 'TFly37'' or 1=1 --' This funny looking parameter value returns this: !!!! AHHHHHH This user just hacked our website and viewed all of the users in our table. In this specific case only our user’s full names were breached, but in other instances it’s just as easy for more sensitive data like passwords, social numbers, and bank account numbers to be revealed as well (If you are looking for some fun, search for “SQL injection” on the to see all of the companies that aren’t doing a good job protecting your privacy). security Have I been pwned? list of Pwned websites So how did that example of SQL injection actually work? Since our stored procedure executes a dynamically generated query, let’s look at what that generated query actually looks like for both of the parameters that were passed in: Even though doesn’t look like a intelligible input parameter, when its concatenated into our query it makes sense. TFly37'' or 1=1-- Our malicious user is basically writing their own SQL query, one that will return all of the names of our users instead of just their own. In many instances, the crafty hacker can go a step further and write additional injection code to reveal the contents of the entire user table (or other tables in the database)! How do I prevent SQL injection? Simple: don’t concatenate unsanitized user input data to your SQL queries. In this example, this is easy to do: simply rewrite the stored procedure to not use dynamic SQL: When you don’t have dynamic SQL, you can’t be affected by SQL injection attempts. Avoiding dynamic SQL isn’t always possible though, so what can we do in those cases? Use : sp_executesql , removing the possibility of unwanted code injection. sp_executesql allows for parameterization of your dynamic SQL Are any of my current SQL queries vulnerable to SQL injection attacks? So SQL injection is bad and you don’t want to become like or . How do we check to see if we have any vulnerable queries on our server? really Sony Yahoo I wrote the query below to help you start searching. but it does act as a good starting point for auditing potentially injectable queries: It is not perfect, I’ve found this script useful for myself, however if you find any issues with it please let me know, thanks! You might also enjoy following me on Twitter .
