Contents tagged with SQL

  • Source-controlled database backups

    This is in beautiful Kauai in case you were wonderingI’m not very satisfied with traditional database backup solutions. It seems like almost no progress was made since SQL Server 6.5 (at least for the built-in tools). They are still outputting monolithic binary backup files that only do the job if the only thing you expect from backups is the ability to recover from catastrophic failures. I expect more, but before I explain, we need a disclaimer:


  • Recovering the admin password in Orchard

    (c) Bertrand Le Roy 2003These things happen, and it seems hopeless at first: you've locked yourself out of your own site and that's that. Well, not quite. If you still have access to the database there is a way out. Access may be through FTP and WebMatrix or Visual Studio if using SQL CE or through SQL Server Management Studio or whatever is your preferred way to access your database. In this tutorial I'll use WebMatrix over a local SqlCe database but other tools would work just as well with minor differences.


  • Please, please, please, learn about injection attacks!

    I answer a lot of posts on the forums of the ASP.NET site. And more often than I would like to, I answer a different question than the one the poster asked, because I happened to easily spot a potential injection attack in the posted code.
    Now, what is an injection attack? If you don't know and you're a web developer, you're in trouble. Read on.
    There are mainly two types of injection attacks, but both use the same vector of penetration: unvalidated user input.
    Rule #1: User input is EVIL (pronounced eyveel, like the doctor of the same name) and should never be trusted. Validate all user input. In the case of a web application, user input is form fields, headers, cookies, query strings, or any thing that was input or sent by users (that may include some database data, or other sometimes more exotic input like mail or ftp).
    The first type of injection attack, and the most deadly for most web sites are SQL Injection Attacks. It happens most of the time when the developer injects user input into a SQL query using string concatenation. For example:

    SqlCommand cmd = new SqlCommand(
      "SELECT ID, FullName FROM User WHERE Login='"
      + "' AND Password='"
      + "'");
    This is C#, but I'm sure our VB programmer friends will get the idea (+ means &). This code is simply frightening, but I've seen it or a variation on it so often I just can't count. OK, why is it frigtening? Well, try to enter these strings into the login and password textboxes:
    ' OR ''='
    There, you're authenticated! What happens is simple. Instead of being the simple text that you expected, the user input some evil text that contains the string delimiter and some SQL code that you're very generously executing.
    Of course, this is not the worse that could happen. Any SQL command could be executed, especially if you've been careless enough not to restrict the rights of the ASP.NET user on your database. For example, the user could very well steal all the information in your database, completely obliterate it or even take complete control of the server. This leads us to rule #2, our fisrt counter measure:
    Rule #2: Secure your database: don't use the sa user to connect to the database from your application, have a strong password on the sa user (and on any user), preferably use integrated authentication to keep all secrets out of your connection string and config file, and restrict the authorizations on your database objects to what's absolutely necessary (don't give writing rights to internet users except on log tables or forum tables, for example). This way, even if you accidentally write injectable code, the database will refuse to execute anything harmful beyond information disclosure (which could still be pretty bad). Please note that the above injection example will still work even if the database is secured.
    So it may seem at first that the quotes are the usual suspects in this case. Actually, if you use this kind of code, you may have noticed a few glitches for example if people have legitimate quotes in their names. So what many people have been doing for a long time is to double the quotes in the input strings, something like Login.Text.Replace("'", "''"), or replace them with another harmless character (you can recognize these sites usually because they use the ` character instead of quotes). This gives a false sense of security, which is sometimes worse than no security at all. Consider this request:

    "SELECT FullName FROM User WHERE ID=" + Identity
    Here, no need for quotes to inject code, all you need is space and letters. Enter 0 DELETE * FROM User into the textbox, and there goes your User table. And I'm sure a hacker creative enough could come up with wicked injections that don't even need spaces. Escape sequences in particular are a usual way to pass characters that were thought to be invalid in many applications (including, yes, Microsoft products whose name have two letters, begin with I and end with E). This leads us to the third rule:
    Rule #3: Black lists are always incomplete (because hackers are many and potentially smarter than you). If you have to, rely on white lists, but never black lists.
    A black list is a list of all characters you consider evil (like the quote). There will always be something missing in it. Consider this as a fact (even though of course it can be complete, but you should act as if this was not the case).
    A white list is a list of authorized characters. What's great about it is that you know precisely what's permitted (what's in the list) and what's is forbidden (everything else). In the last example, restricting ID.Text to numeric characters is enough to secure the request.
    And now for the good news. While it is useful to know all this about SQL Injections, the .NET framework (and all modern development frameworks, like Java) provide an excellent way to prevent injections: parametrized queries. Parametrized queries are safer, cleaner and make your code easier to read. Here are the two previous examples, rewritten as parametrized queries:


    SqlCommand cmd = new SqlCommand("SELECT ID, FullName FROM User WHERE Login=@Login AND Password=@Password");
    SqlDbType.NVarChar, 50).Value = Login
    SqlDbType.NVarChar, 50).Value = Password.Text;

    SqlCommand cmd = new SqlCommand("SELECT FullName FROM User WHERE ID=@ID");
    SqlDbType.Int).Value = int.Parse(Identity.Text);

    This way, there is no need to escape any characters because the parameter values are directly communicated in a strongly typed manner to the database.
    N.B. In the second example, you may also want to use validator controls on the Identity TextBox, and check the validity of the page server-side before you build and execute the SQL query using Page.IsValid.
    Rule #4: Use parametrized queries whenever possible.
    Whenever possible? Does that mean that it's not always possible? Well, here's a little problem I got from the ASP.NET forums: you have a list of checkboxes on a page that have numeric identifiers as their values. Let's say that you must extract all the database rows that have the checked values. You'd want to write something like that (pseudo-code here):


    SqlCommand cmd = new SqlCommand("SELECT FullName FROM User WHERE ID IN(@IdArray)");
    SqlDbType.IntArray).Value = Convert.ChangeType(Request.Form["Identities"], typeof(int[]));

    Unfortunately, there is no such thing as an array type for Sql. So in this case, unfortunately, unless someone comes up with something better, you have to rely on concatenation:


    string idList = Request.Form["Identities"];
    if (IntListValidate(idList)) {
    SqlCommand cmd = new SqlCommand("SELECT FullName FROM User WHERE ID IN(" + idList + ")");
    else {
    throw new InvalidDataException("The posted data contains illegal characters.");
    private bool IntListValidate(string input) {
      for (int i = 0; i < input.Length; i++) {
    if (!Char.IsDigit(input, i) && input[i] != ',') return false;
    return true;

    Of course, here, you have to use a white list, digits and comma in this case. Not even space is allowed. That's pretty safe, but I wish you could do that with parameters.

    Update 8/19/2004: Kyle Heon just pointed me to this great Sql article that explains just how to do that with a parameter. Thanks for the link, Kyle! So now, there's one less reason not to use parameters everywhere.

    The second common type of injection attack is Cross-Site Scripting, or X-Site Scripting. Consider this simple piece of asp page:

    Bonjour, <%= Request.Form("Name") %>.

    What if the user enters <script>alert("5tUp1dH4k3R ownz you");</script> in the Name form field? Well, he successfully displayed an alert on his browser using your page. Nothing to be afraid about for the moment, as he'll be the only one to see it. But what if instead of directly displaying the user input we store it in a database for other users to see, in a forum application, for example? What if the parameter is passed in the querystring parameters of a hyperlink in a mail that seems to come from your bank?

    Well, then a lot of nasty things can happen (by the way, these are real scenarios, stuff that happened and continues to happen every day). For example, the attacker can inject script that will post all your cookies or some confidential information that's displayed on the page to some remote site that he owns. This can include your social security number, your authentication cookies, your credit card number, or any sensitive information that may be displayed on the page.

    It is usually relatively harmless for many sites because they just don't have any information that's confidential or that could allow for other, more dangerous attacks.

    Once again, ASP.NET gives a first line of defense out of the box. Since v1.1, all form and querystring data is validated on the server before the page is executed. So the above example does not work on ASP.NET 1.1, it will just throw an exception. Now, this feature is sometimes deactivated by controls such as rich text editors.

    The second thing we're doing, which is what you should do in your own pages and controls, is to HtmlEncode any property that comes from user input. That includes the value of an input tag that's rendered from a TextBox. It protects this particular textbox from script injections and also makes it robust against legitimate characters in the contents, such as quotes.

    Rule #5: Encode all rendered properties that come from user input when rendering them.

    The above example would then become:

    Bonjour, <%= Server.HtmlEncode(Request.Form("Name")) %>.

    There's another often overlooked rule:

    Rule #6: Don't display any secrets in error messages.

    ASP.NET limits by default the complete error messages to calls from the local machine. A complete error message sent to any machine can reveal table names or other secrets that could give clues for some attacker to use. And usually, an error message gives an indication as to how to make this application fail, which can be repeated and improved on the basis of all the information the error message contains.

    And of course, probably the most important rule:

    Rule #7: Encrypt any page that contains sensitive data.

    Of course, these rules are all important and the order in which they are presented here is irrelevant. Did I forget something?

    If you need more information, here's some more reading:

    On Sql Injections:

    On Cross-Site Scripting:

    And of course, Google is your friend.

    UPDATE: I've used the word "quote" in this article for both the apostrophe (or single quote) and double quote. Todd pointed out in the comments that was somehow ambiguous. The point is that anything that can be used as a string delimiter, or as a delimiter in general, should be considered suspicious. Double-quotes are more frequent, but some languages such as JavaScript use both single and double quotes. SQL uses single quotes. Bottom line: beware of delimiters, and remember you may not even know the list of possible delimiters.

    UPDATE: we just released new tools that aim at helping developers scan their code for potential injection attacks.