How to restrict SQL injection in string command text

Question

How to restrict this below command text with SQL injection:

cmdExcel.CommandText = "SELECT * From [" + sheetName + "]";

Any help would be appreciated. Thanks!

Answer 1

Since you know that you are intressted of table name, then you know that i should not containe any space.

So if you make sure of that then even if someone tried to add more sql then it will only be an error.

So here is how you could do it.

cmdExcel.CommandText = "SELECT * From [" + sheetName.Replace(" ", "") + "]";

Answer 2

use ToSqlStrValue("") to protect SQL injection.

for example:

str sql="Select * from Customers where username="+ToSqlStrValue(username);

    /// <summary>
    /// This is to prevent potential SQL worms because of SQL assembling.
    /// </summary>
    /// <param name="str"></param>
    /// <returns></returns>
    public static String FilterInvalidSqlChars(String str)
    {
        if(str==null || str=="")
            return str;
        String[] badChars = { "\'" };
        str = str.Replace(badChars[0], "\'\'");
        return str;
    }

    /// <summary>
    /// This function adds "'" to the begin and end of a string value so that it
    /// can be used in a SQL statement directly by + operation. It also filters the 
    /// invalid SQL characters, such as "that's ok" to --> "'That''s ok'"
    /// Note all strings are unicode strings. if strValue is null, it returns a string "null".
    /// </summary>
    public static String ToSqlStrValue(String strValue)
    {
        if(strValue==null)
            return "null";
        else 
            return "N\'" + FilterInvalidSqlChars(strValue) + '\'';

    }

Answer 3

I am guessing cmdExcel is type of SqlCommand. It allows us to add parameters.

see sample below:

string commandText = "UPDATE Sales.Store SET Demographics = @demographics "
        + "WHERE CustomerID = @ID;";

    SqlConnection connection = new SqlConnection(connectionString)
        SqlCommand command = new SqlCommand(commandText, connection);
        command.Parameters.Add("@ID", SqlDbType.Int);
        command.Parameters["@ID"].Value = customerID;


        command.Parameters.AddWithValue("@demographics", demoXml);