Link to home
Start Free TrialLog in
Avatar of eeyo
eeyo

asked on

SQL Server Dynamic SQL - SQL Injection Prevention

Understanding that Parameterized Stored Procedures are the best way to go ... having said that:

If I must use Dynamic SQL (SQL Server) with variable numbers of  tables and fields, how "safe" is it for me to "Clean" the string by using regex (or other function) to ensure that:
1) the input string is limited to a short string (such as 15 characters to prevent buffer overflow errors)
2) only the characters A-Z, a-z, 0-9 and _  are used (no other symbols, semicolons, etc.)

Example SQL Statement:
"SELECT orderID where ProductDescription = '" & CleanedSQLText & "'"

Open in new window


Also, is using QUOTENAME for fieldnames and tablenames considered reasonably safe to prevent SQL Injections as well?
Avatar of Shaun Vermaak
Shaun Vermaak
Flag of Australia image

In code? This is a simple function in C#
public static Boolean checkForSQLInjection(string userInput)

        {
            bool isSQLInjection = false;
            string[] sqlCheckList = { "--",
                                       ";--",
                                       ";",
                                       "/*",
                                       "*/",
                                        "@@",
                                        "@",
                                        "char",
                                       "nchar",
                                       "varchar",
                                       "nvarchar",
                                       "alter",
                                       "begin",
                                       "cast",
                                       "create",
                                       "cursor",
                                       "declare",
                                       "delete",
                                       "drop",
                                       "end",
                                       "exec",
                                       "execute",
                                       "fetch",
                                            "insert",
                                          "kill",
                                             "select",
                                           "sys",
                                            "sysobjects",
                                            "syscolumns",
                                           "table",
                                           "update"
                                       };

            string CheckString = userInput.Replace("'", "''");

            for (int i = 0; i <= sqlCheckList.Length - 1; i++)

            {

                if ((CheckString.IndexOf(sqlCheckList[i],

    StringComparison.OrdinalIgnoreCase) >= 0))

                { isSQLInjection = true; }
            }


            return isSQLInjection;
        }

Open in new window

http://www.c-sharpcorner.com/blogs/check-string-against-sql-injection-in-c-sharp1
Avatar of eeyo
eeyo

ASKER

Thanks for the code, but I was mainly asking the general question that if only letters and number (and underscore) are used in the input text, is it safe?  In other words, if there are no "--", ":" ,"/", etc. is it pretty safe to assume that a SQL injection can't occur?  The problem I see with using a blacklist is that it may too aggressive.  For example, if an appropriate user input string is "I had to drop my cast on the table to begin to create my artwork", it wouldn't pass muster using a blacklist.
No, it is not. You need to check for a whole bunch of keywords etc.
ASKER CERTIFIED SOLUTION
Avatar of Snarf0001
Snarf0001
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of eeyo

ASKER

Snarf0001, this works well for values.  Do you know of any way I can also use parameters for specifying tables and/or fields (unfortunately, these can't be specified design time ... must be run time)?
No, without getting overly complicated there's not a simple way to accomplish that.
I would say best approach is to use both of the methods going on here.

For the actual value, use the parameterized query.  Then the use can type in anything they need, and there's no worry about injection.

For the target table name, you would have to clean the input, but you can be a lot more restrictive as it only has to be a valid table name, so a LOT is excluded.
In your above sample, alphanumeric and underscore being the ONLY ones allowed, are spaces also excluded?  If so, then I can't see that being open to injection.  

But would be curious if @Shaun can think of something I'm overlooking.
Avatar of eeyo

ASKER

Luckily, spaces are excluded for the table and field names.