Beispiel #1
0
    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]);
	}
    }
Beispiel #2
0
    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("");
    }