public void TokenizingTests() { WvLog.maxlevel = WvLog.L.Debug5; VxSqlTokenizer tok = new VxSqlTokenizer(); // This actual string tripped up the tokenizer on 28.10.08, because // a stupidly placed Trim() removed the trailing '\n' from the string. // Let's make sure we never do that again. tok.tokenize("create procedure Func1 as select '" + "Hello, world, this is Func1!'\n"); VxSqlToken[] tokens = tok.gettokens().ToArray(); WVPASSEQ(tokens.Length, 6); string[] expecteds = { "create", " procedure", " Func1", " as", " select", " 'Hello, world, this is Func1!'\n" }; VxSqlToken.TokenType[] expectedt = { VxSqlToken.TokenType.Keyword, VxSqlToken.TokenType.Keyword, VxSqlToken.TokenType.Unquoted, VxSqlToken.TokenType.Keyword, VxSqlToken.TokenType.Keyword, VxSqlToken.TokenType.SingleQuoted }; for (int i = 0; i < tokens.Length; ++i) { VxSqlToken t = tokens[i]; WVPASS(t.type == expectedt[i]); WVPASSEQ(t.ToString(), expecteds[i]); } }
internal static string query_parser(string query, int access_restrictions) { VxSqlTokenizer tokenizer = new VxSqlTokenizer(query); VxSqlToken[] tokens = tokenizer.gettokens().ToArray(); List<VxSqlToken> result = new List<VxSqlToken>(); for (int i = 0; i < tokens.Length; ++i) { VxSqlToken tok = tokens[i]; VxSqlTokenizer ret = new VxSqlTokenizer(); if (tok.NotQuotedAndLowercaseEq("get") && i < tokens.Length - 3 && tokens[i + 1].NotQuotedAndLowercaseEq("object")) { // Format: // get object {view|trigger|procedure|scalarfunction|tablefunction} name // Returns: the "source code" to the object ret.tokenize(String.Format( "select cast(text as varchar(max)) text " + "from syscomments " + "where objectproperty(id, 'Is{0}') = 1 " + "and object_name(id) = '{1}' " + "order by number, colid;", tokens[i + 2].name, tokens[i + 3].name)); i += 3; } else if (i < tokens.Length - 1) { VxSqlToken next_tok = tokens[i + 1]; if (tok.IsKeywordEq("use") && next_tok.IsIdentifier()) { // Drop "use x" statements completely ret.tokenize(";"); ++i; } else if (tok.NotQuotedAndLowercaseEq("list")) { if (next_tok.NotQuotedAndLowercaseEq("tables")) { ret.tokenize("exec sp_tables;"); ++i; } else if (next_tok.NotQuotedAndLowercaseEq("columns") && i < tokens.Length - 2) { string tabname = tokens[i + 2].name; var sb = new StringBuilder(tabname.Length*2); for (int b = 0; b < tabname.Length; b++) { if (tabname[b] == '\\' && b < tabname.Length-1) sb.Append(new char[] { '[', tabname[++b], ']' }); else sb.Append(tabname[b]); } ret.tokenize("exec sp_columns @table_name='{0}';", sb); i += 2; } else if (next_tok.NotQuotedAndLowercaseEq("all") && i < tokens.Length - 2) { VxSqlToken nextnext_tok = tokens[i + 2]; i += 2; if (nextnext_tok.NotQuotedAndLowercaseEq("table")) { ret.tokenize( "select distinct cast(Name as varchar(max)) Name" + " from sysobjects " + " where objectproperty(id,'IsTable')=1 " + " and xtype='U' " + " order by Name;"); } else { // Format: list all {view|trigger|procedure|scalarfunction|tablefunction} // Returns: a list of all of whatever // Note: the parameter we pass in can be case insensitive ret.tokenize(String.Format( "select distinct " + " cast (object_name(id) as varchar(256)) Name " + " from syscomments " + " where objectproperty(id,'Is{0}') = 1 " + " order by Name;", nextnext_tok.name)); } } } } if (ret.gettokens() != null) foreach (VxSqlToken t in ret.gettokens()) result.Add(t); else result.Add(tok); } if (access_restrictions == 0) //no restrictions goto end; // see below for what these all do bool have_command = false; bool have_begin = false; bool have_insert = false; bool have_update = false; bool insert_valid_for_exec = true; bool have_alter = false; bool have_alterdb = false; bool have_altertable = false; bool altertable_valid_for_set = true; string exception_txt = "Insufficient security permissions for this type of query."; foreach (VxSqlToken t in result) { // skip comments if (t.IsComment()) continue; if (have_begin) { // Need to identify if a 'begin' is a 'begin...end' block // (which we'll basically skip over), or an actual command // ('begin transaction' or 'begin distributed transaction'). have_begin = false; if (t.IsKeywordEq("transaction") || t.IsKeywordEq("distributed")) { //access_restrictions == 2 => no non-select if (!have_command && access_restrictions < 2) have_command = true; else throw new VxSecurityException(exception_txt); } } if (t.IsKeywordEq("begin")) { have_begin = true; continue; } //FIXME: Does this heuristic suck? //Have to take care of "INSERT INTO x.y (a, "b", [c]) EXEC foo" //If we notice anything other than valid column identifiers and //table names, or the word 'into', commas, periods, or parentheses, //we ban a following 'EXEC', otherwise we allow it if (have_insert && !(t.IsKeywordEq("into") || t.IsValidIdentifier() || t.type == VxSqlToken.TokenType.Comma || t.type == VxSqlToken.TokenType.Period || t.type == VxSqlToken.TokenType.LParen || t.type == VxSqlToken.TokenType.RParen || t.IsKeywordEq("exec"))) insert_valid_for_exec = false; if (have_alter) { have_alter = false; if (t.IsKeywordEq("database")) { have_alterdb = true; continue; } else if (t.IsKeywordEq("table")) { have_altertable = true; continue; } } //If we have "ALTER TABLE x.y", it's OK to have the word 'set' right //afterwards. otherwise, it's not OK. if (have_altertable && !(t.IsValidIdentifier() || t.IsKeywordEq("set"))) altertable_valid_for_set = false; if (IsSimpleCommand(t)) { //access_restrictions == 2 => no non-select if (!have_command && (access_restrictions < 2 || t.IsKeywordEq("select"))) { // No command yet? Now we have one. Flag a few have_command = true; if (t.IsKeywordEq("insert")) have_insert = true; else if (t.IsKeywordEq("update")) have_update = true; else if (t.IsKeywordEq("alter")) have_alter = true; } else if (have_update && t.IsKeywordEq("set")) //'SET' is OK as part of "UPDATE x SET y" have_update = false; else if (have_alterdb && t.IsKeywordEq("set")) //'SET' is OK as part of "ALTER DATABASE x SET y" have_alterdb = false; else if (have_insert && insert_valid_for_exec && (t.IsKeywordEq("exec") || t.IsKeywordEq("execute"))) have_insert = false; //Insert's exec just finished here else if (have_altertable && altertable_valid_for_set && t.IsKeywordEq("set")) have_altertable = false; else if (!t.IsKeywordEq("select")) throw new VxSecurityException(exception_txt); } } end: return result.join(""); }