SQL Server 2005 : Dynamic T-SQL - Supporting Optional Parameters (part 3) - SQL Injection

10/14/2012 7:29:40 PM

SQL Injection

Concatenating string parameters such as @NationalIdNumber directly onto queries can open your applications to considerable problems. The issue is a hacking technique called a SQL injection attack, which involves passing bits of semiformed SQL into textboxes in order to try to manipulate dynamic or ad hoc SQL on the other side.

The example GetEmployeeData stored procedure doesn't actually have much of a problem as-is, because @NationalIdNumber is defined as only 15 characters—this doesn't give a hacker much room to work with. But what if you were working with another stored procedure that had to be a bit more flexible? The following example procedure, which might be used to search for addresses in the AdventureWorks database, gives an attacker more than enough characters:

    @String NVARCHAR(60)


    SET @sql = '' +
        'SELECT AddressId ' +
        'FROM Person.Address ' +
        'WHERE AddressLine1 LIKE ''%' + @String + '%'''


This stored procedure can be executed with a string such as "Stone" used for the parameter value:

EXEC FindAddressByString
    @String = 'Stone'

This outputs the result set you might expect, with all of the address IDs that use that string in the AddressLine1 column. That output is shown in Figure 6.

Output of searching addresses for the string "Stone"

Consider what actually happened inside of the stored procedure. The WHERE clause for the query was concatenated, such that it literally became WHERE AddressLine1 LIKE '%Stone%'. But nothing is stopping someone from passing a string into the stored procedure that has a more profound effect. For instance, consider what happens in the following case, the output of which is shown in Figure 7:

EXEC FindAddressByString
    @String = ''' ORDER BY AddressId --'

After concatenation, the WHERE clause reads WHERE AddressLine1 LIKE '%' ORDER BY AddressId --%'. An ORDER BY clause—which was not there before—has been added to the query, and the %' at the end of the query has been commented out so that it will have no effect.

An injected ORDER BY clause ordered the results.

This is, of course, a fairly mundane example. How about something a bit more interesting, such as getting back the full pay history for every employee in the database?

EXEC FindAddressByString
    @String = '''; SELECT * FROM HumanResources.EmployeePayHistory --'

Assuming that the account used for the query has access to the HumanResources.EmployeePayHistory table, running the stored procedure produces the output shown in Figure 8. The fact is the attacker can do anything in the database that the authenticated account has access to do and that can be done in 60 characters (the size of the string parameter). This includes viewing data, deleting data, or inserting fake data. Such an attack can often be waged from the comfort of a web browser, and intrusion can be incredibly difficult to detect.

An attacker can use SQL injection to do anything that the authenticated account has access to do.

The solution is not to stop using dynamic SQL. Rather, it's to make sure that your dynamic SQL is always parameterized. Let me repeat that for effect: Always, always, always parameterize your dynamic SQL! The next section shows you how to use sp_executesql to do just that.
PS4 game trailer XBox One game trailer
WiiU game trailer 3ds game trailer
Top 10 Video Game
-   Minecraft Mods - MAD PACK #10 'NETHER DOOM!' with Vikkstar & Pete (Minecraft Mod - Mad Pack 2)
-   Minecraft Mods - MAD PACK #9 'KING SLIME!' with Vikkstar & Pete (Minecraft Mod - Mad Pack 2)
-   Minecraft Mods - MAD PACK #2 'LAVA LOBBERS!' with Vikkstar & Pete (Minecraft Mod - Mad Pack 2)
-   Minecraft Mods - MAD PACK #3 'OBSIDIAN LONGSWORD!' with Vikkstar & Pete (Minecraft Mod - Mad Pack 2)
-   Total War: Warhammer [PC] Demigryph Trailer
-   Minecraft | MINIONS MOVIE MOD! (Despicable Me, Minions Movie)
-   Minecraft | Crazy Craft 3.0 - Ep 3! "TITANS ATTACK"
-   Minecraft | Crazy Craft 3.0 - Ep 2! "THIEVING FROM THE CRAZIES"
-   Minecraft | MORPH HIDE AND SEEK - Minions Despicable Me Mod
-   Minecraft | Dream Craft - Star Wars Modded Survival Ep 92 "IS JOE DEAD?!"
-   Minecraft | Dream Craft - Star Wars Modded Survival Ep 93 "JEDI STRIKE BACK"
-   Minecraft | Dream Craft - Star Wars Modded Survival Ep 94 "TATOOINE PLANET DESTRUCTION"
-   Minecraft | Dream Craft - Star Wars Modded Survival Ep 95 "TATOOINE CAPTIVES"
-   Hitman [PS4/XOne/PC] Alpha Gameplay Trailer
-   Satellite Reign [PC] Release Date Trailer
Game of War | Kate Upton Commercial