Understanding SQL Injection

One of the dangers of writing public Web applications is that eventually someone will try to hack your application, especially if it contains sensitive financial information such as credit card numbers or bank account details. For hackers, one way to try to get this information from your application is SQL injection. Its use isn't necessarily limited to applications that use stored procedures, but we're presenting it here because of the way that stored procs are often used.

Let's say that the first page in the application requires users to enter a username and password. Once the hacker has tried the usual combinations of manager, sa, admin, and so on (you already know not to use obvious usernames such as these, right?), he might try entering the following:

 myusername; SELECT username FROM users 

If the contents of the username box are passed directly into a stored proc, then the stored proc runs as normal when executed, except that the semicolon acts as a statement terminator and the additional SELECT statement also runs. Depending on what the proc returns, our hacker could now have a complete list of valid user IDs for the application. The same technique can be used for passwords, so the hacker can now log into the application as any valid user.

How do you stop this? The most obvious way to trap SQL injection is to scan every text input for a semicolon and then pass only the input before the semicolon to the stored proc. Alternatively, if you detect a semicolon, return some kind of error page to hackers that lets them know you're onto them. A more complex method of blocking SQL injection is to run stored procs from a component, rather than calling them directly from an ASP.NET page. In this way, the raw output from the stored proc isn't returned to the hacker's browser, and the hacker receives no clues about your database's structure or data.