I am getting some SQL statement that user inputs on UI:
select * from [table] where [col1] is not null and col2 <> 0
I need to verify that all column names (col1 and col2) are in brackets (like col1). And show some popup message if any column name is not in brackets (in this case, col2).
Is there some way to do such SQL verification in C#?
Regarding parsing SQL, you can use the TSqlParser library that ships with Visual Studio. This parser accepts a visitor class that inherits from TSqlFragmentVisitor and overrides the visit method for ColumnReferenceExpression.
using System;
using System.Collections.Generic;
using System.IO;
using System.Text;
using Microsoft.SqlServer.TransactSql.ScriptDom;
namespace ConsoleApplication8
{
public class QueryParser
{
public IEnumerable<string> Parse(string sqlSelect)
{
TSql100Parser parser = new TSql100Parser(false);
TextReader rd = new StringReader(sqlSelect);
IList<ParseError> errors;
var columns = new List<string>();
var fragments = parser.Parse(rd, out errors);
var columnVisitor = new SQLVisitor();
fragments.Accept(columnVisitor);
columns = new List<string>(columnVisitor.Columns);
return columns;
}
}
internal class SQLVisitor : TSqlFragmentVisitor
{
private List<string> columns = new List<string>();
private string GetNodeTokenText(TSqlFragment fragment)
{
StringBuilder tokenText = new StringBuilder();
for (int counter = fragment.FirstTokenIndex; counter <= fragment.LastTokenIndex; counter++)
{
tokenText.Append(fragment.ScriptTokenStream[counter].Text);
}
return tokenText.ToString();
}
public override void ExplicitVisit(ColumnReferenceExpression node)
{
columns.Add(GetNodeTokenText(node));
}
public IEnumerable<string> Columns {
get { return columns; }
}
}
public class Program
{
private static void Main(string[] args)
{
QueryParser queryParser = new QueryParser();
var columns = queryParser.Parse("SELECT A,[B],C,[D],E FROM T WHERE isnumeric(col3) = 1 Order by Id desc");
foreach (var column in columns)
{
Console.WriteLine(column);
}
}
}
}
However, I am not sure that parsing SQL that an user inputs in UI is a good idea. Even if you run the SQL statements in a sand boxed environment where the user only has read permissions (I do hope you are doing this), it is not very user friendly. As a user I would prefer to be able to select the columns I want using check boxes or by dragging and dropping them rather than having to write a SQL query.
