Microsoft’s Advice On Avoiding SQL Injection Attacks

Not to pat myself on the back, but Microsoft’s official advice on avoiding SQL injection attacks sounds awfully familiar to readers of this blog:

Clean variables are good variables. Photo by blickpixel via Pixabay, in the public domain.
Clean variables are good variables. Photo by blickpixel via Pixabay, in the public domain.

Sanitize (validate) all inputs: “This helps to ensure that the input is free from characters that cause SQL injection attacks.” It also allows you to fix the form and data type of the user input, which pretty much renders basic script kiddie attacks useless.

Parameters, not strings, as query variables: “Creating dynamic queries using string concatenation potentially allows an attacker to execute an arbitrary query through the application.”

In other words, it’s harder to break this:

@person VARCHAR(20); SELECT * FROM table WHERE person = @person;

than it is to break this:

SELECT * FROM table WHERE person = 'some user string';

Stored procedures, not free-form queries:Stored procedures by themselves do not remove SQL injection vulnerabilities. They only raise the bar on the attacker by hiding much of the underlying database schema.” That is, the attacker can’t easily find out what columns are in a table, or what type of data is in those columns, if you use a stored procedure.

Minimal permissions: “In general, database applications should be using a low-privileged account that has the minimum permissions required to execute the statements submitted to SQL Server.” As in, create a user in your SQL database whose only permission set is to execute your Web-based stored procedures, and connect to the database server as that user.

Those are the basics. And if you don’t understand how to do them, I’ll be putting together a blog series on how to convert your old string-queried Web applications into one secured with stored procedures and proper permissions.

Additionally, Microsoft suggests:

Escape your strings: Microsoft notes that this is important “when SQL identifiers (table names, column names, and so on) might be constructed dynamically.” I’d say, if your solution requires you to dynamically determine table or column names, your solution is doing it wrong; try again. That doesn’t mean you shouldn’t escape strings by default; it means, a dumb or wrong approach isn’t the only reason to do so.

Protect against buffer overruns: “Buffer lengths need to be handled properly to prevent SQL injections through truncation.” In Web development, if you fix the length and type of data, you’ll generally avoid buffer overruns. If you’re taking in bitstreams to dump into BLOBs, that’s another matter altogether; but if you’re skilled enough to do that, you should be skilled enough to armor against overruns.

All links in this post on delicious:

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

  • Check out the Commenting Guidelines before commenting, please!
  • Want to share code? Please put it into a GitHub Gist, CodePen or pastebin and link to that in your comment.
  • Just have a line or two of markup? Wrap them in an appropriate SyntaxHighlighter Evolved shortcode for your programming language, please!