///<summary>Returns true if the given SQL script in strSql contains any commands (INSERT, UPDATE, DELETE, etc.). Surround with a try/catch.</summary> private static bool IsCommandSql(string strSql) { string trimmedSql = strSql.Trim(); //If a line is completely a comment it may have only a trailing \n to make a subquery on. We need to keep it there. //splits the string while accounting for quotes and case/if/concat statements. string[] arraySqlExpressions = UserQueries.SplitQuery(UserQueries.RemoveSQLComments(trimmedSql).ToUpper(), false, ";").ToArray(); //Because of the complexities of parsing through MySQL and the fact that we don't want to take the time to create a fully functional parser //for our simple query runner we elected to err on the side of caution. If there are comments in the middle of the query this section of //code will fire a UE. This is due to the fact that without massive work we cannot intelligently discern if a comment is in the middle of //a string being used or if it is a legitimate comment. Since we cannot know this we want to block more often than may be absolutely //necessary to catch people doing anything that could potentially lead to SQL injection attacks. We thus want to inform the user that simply //removing intra-query comments is the necessary fix for their problem. for (int i = 0; i < arraySqlExpressions.Length; i++) { //Clean out any leading comments before we do anything else while (arraySqlExpressions[i].Trim().StartsWith("#") || arraySqlExpressions[i].Trim().StartsWith("--") || arraySqlExpressions[i].Trim().StartsWith("/*")) { if (arraySqlExpressions[i].Trim().StartsWith("/*")) { arraySqlExpressions[i] = arraySqlExpressions[i].Remove(0, arraySqlExpressions[i].IndexOf("*/") + 3).Trim(); } else //Comment starting with # or starting with -- { int endIndex = arraySqlExpressions[i].IndexOf("\n"); if (endIndex != -1) //This is so it doesn't break if the last line of a command is a comment { arraySqlExpressions[i] = arraySqlExpressions[i].Remove(0, arraySqlExpressions[i].IndexOf("\n")).Trim(); } else { arraySqlExpressions[i] = arraySqlExpressions[i].Remove(0, arraySqlExpressions[i].Length).Trim(); } } } if (String.IsNullOrWhiteSpace(arraySqlExpressions[i])) { continue; //Ignore empty SQL statements. } if (arraySqlExpressions[i].Trim().StartsWith("SELECT")) //We don't care about select queries { continue; } else if (arraySqlExpressions[i].Trim().StartsWith("SET")) { //We need to allow SET statements because we use them to set variables in our query examples. continue; } else if (arraySqlExpressions[i].Trim().StartsWith("UPDATE")) //These next we allow if they are on temp tables { if (HasNonTempTable("UPDATE", arraySqlExpressions[i])) { return(true); } } else if (arraySqlExpressions[i].Trim().StartsWith("ALTER")) { if (HasNonTempTable("TABLE", arraySqlExpressions[i])) { return(true); } } else if (arraySqlExpressions[i].Trim().StartsWith("CREATE")) //CREATE INDEX or CREATE TABLE or CREATE TEMPORARY TABLE { int a = arraySqlExpressions[i].Trim().IndexOf("INDEX"); int b = arraySqlExpressions[i].Trim().IndexOf("TABLE"); string keyword = ""; if (a == -1 && b == -1) { //Invalid command. Ignore. } else if (a != -1 && b == -1) { keyword = "INDEX"; } else if (a == -1 && b != -1) { keyword = "TABLE"; } else if (a != -1 && b != -1) { keyword = arraySqlExpressions[i].Trim().Substring(Math.Min(a, b), 5); //Get the keyword that is closest to the front of the string. } if (keyword != "" && HasNonTempTable(keyword, arraySqlExpressions[i])) { return(true); } } else if (arraySqlExpressions[i].Trim().StartsWith("DROP")) //DROP [TEMPORARY] TABLE [IF EXISTS] { int a = arraySqlExpressions[i].Trim().IndexOf("TABLE"); //We require exactly one space between these two keywords, because there are all sorts of technically valid ways to write the IF EXISTS which would create a lot of work for us. //Examples "DROP TABLE x IF EXISTS ...", "DROP TABLE x IF /*comment IF EXISTS*/ EXISTS ...", "DROP TABLE ifexists IF EXISTS /*IF EXISTS*/" int b = arraySqlExpressions[i].Trim().IndexOf("IF EXISTS"); string keyword = ""; if (a == -1 && b == -1) { //Invalid command. Ignore. } else if (b == -1) { keyword = "TABLE"; //Must have TABLE if it's not invalid } else { keyword = "IF EXISTS"; //It has the IF EXISTS statement } if (keyword != "" && HasNonTempTable(keyword, arraySqlExpressions[i])) { return(true); } } else if (arraySqlExpressions[i].Trim().StartsWith("RENAME")) { if (HasNonTempTable("TABLE", arraySqlExpressions[i])) { return(true); } } else if (arraySqlExpressions[i].Trim().StartsWith("TRUNCATE")) { if (HasNonTempTable("TABLE", arraySqlExpressions[i])) { return(true); } } else if (arraySqlExpressions[i].Trim().StartsWith("DELETE")) { if (HasNonTempTable("DELETE", arraySqlExpressions[i])) { return(true); } } else if (arraySqlExpressions[i].Trim().StartsWith("INSERT")) { if (HasNonTempTable("INTO", arraySqlExpressions[i])) { return(true); } } else //All the rest of the commands that we won't allow, even with temp tables, also includes if there are any additional comments embedded. { return(true); } } return(false); }