How to escape text in SQL statements

Two things got me thinking about escaping text in SQL statements recently. I had previously thought it was a simple topic that everybody already knew about but given that these two things occurred I can only assume that not everybody knows about why escaping text in SQL is important and how to do it. First people keep ending up on the FreeFlow website because one of the methods in the class library is called SqlEscape, so obviously people want to know how to do it. The implementation is very very simple.

    /// <summary>
    /// Escapes a string so that single quotes are replaced by two quotes for use in SQL expressions.
    /// </summary>
    /// <param name="sql">The string to escape.</param>
    /// <returns>The escaped string</returns>
    public static string SqlEscape(string sql)
    {
      if (sql == null)
        return null;
      return sql.Replace("'", "''");
    }

Second, I got hold of some free PHP code off the web that failed to do any escaping of the text in SQL expressions. I foolishly deployed this to a website without checking the code beforehand. Fortunately I managed to spot the error before any dodgy hacker managed to take advantage of it. In the PHP world, escaping can be done using the mysql_real_escape_string function.

So that covers the how but why is this important? The first, probably least important, reason is that it means queries that include ' in text strings will work. I guess most people come across this problem when they try to enter a name like "O'Connor" into an application and it fails.

The more important issue, particularly with public facing websites, is SQL injection where a hacker can manage to run pretty much any query they like against your database.

There are other ways to solve this problem. Stored procedures and parameterised queries will also do the trick.