最近有个项目需要把用户传过来的SQL语句解析 提取其中的表名,列名。
参考了下网上的一些资料 跟ZSWANG以前写的一个C#语法分析。但是要完全正确的提取出来 表名,列名还是没实现。
想在这里想大伙求教下。
下面是分析代码/// <summary>
/// 语法分析基类
/// </summary>
public class SyntaxEngine
{
/// <summary>
/// 语法项
/// </summary>
public class SyntaxItem
{
private string FPattern; // 正则表达式
private RegexOptions FOptions; // 正则表达式附加选项
private string FName; // 语法名称
private int FIndex; // 序号
public string Pattern { get { return FPattern; } } // 正则表达式
public RegexOptions Options { get { return FOptions; } } // 正则表达式附加选项
public string Name { get { return FName; } } // 名称
public int Index { get { return FIndex; } } // 序号
public SyntaxItem(string APattern, RegexOptions AOptions,
string AName, int AIndex)
{
FPattern = APattern;
FOptions = AOptions;
FName = AName;
FIndex = AIndex;
}
}
/// <summary>
/// 语法分析返回项
/// </summary>
public class AnalyzeReslut
{
private SyntaxItem FItem; // 所属语法项
private string FBlock; // 文字块
public SyntaxItem Item { get { return FItem; } }
public string Block { get { return FBlock; } }
public AnalyzeReslut(SyntaxItem AItem, string ABlock)
{
FItem = AItem;
FBlock = ABlock;
}
} private List<SyntaxItem> FSyntaxItems = new List<SyntaxItem>();
private List<AnalyzeReslut> FAnalyzeResluts = new List<AnalyzeReslut>();
public List<SyntaxItem> SyntaxItems { get { return FSyntaxItems; } }
public List<AnalyzeReslut> AnalyzeResluts { get { return FAnalyzeResluts; } }
/// <summary>
/// 进行语法分析
/// </summary>
/// <param name="ACode">所分析的代码</param>
/// <returns>返回分析是否成功</returns>
public virtual bool Analyze(string ACode)
{
if (FSyntaxItems.Count <= 0)
return false;
if (ACode == null)
return false;
AnalyzeResluts.Clear();
string vCode = ACode;
bool vFind = true;
while (vFind && (vCode.Length > 0))
{
vFind = false;
foreach (SyntaxItem vSyntaxItem in FSyntaxItems)
{
bool reg = Regex.IsMatch(vCode, vSyntaxItem.Pattern, vSyntaxItem.Options);
if (reg)
{
AnalyzeResluts.Add(new AnalyzeReslut(vSyntaxItem,
Regex.Match(vCode, vSyntaxItem.Pattern,
vSyntaxItem.Options).Value));
vCode = Regex.Replace(vCode, vSyntaxItem.Pattern, "",
vSyntaxItem.Options);
vFind = true;
break;
}
}
}
return true;
} public string findgjz()
{
string aruslut="";
foreach (AnalyzeReslut ar in AnalyzeResluts)
{
if (ar.Item.Name == "标识符")
aruslut += ar.Block + ",";
}
return aruslut;
}
}
参考了下网上的一些资料 跟ZSWANG以前写的一个C#语法分析。但是要完全正确的提取出来 表名,列名还是没实现。
想在这里想大伙求教下。
下面是分析代码/// <summary>
/// 语法分析基类
/// </summary>
public class SyntaxEngine
{
/// <summary>
/// 语法项
/// </summary>
public class SyntaxItem
{
private string FPattern; // 正则表达式
private RegexOptions FOptions; // 正则表达式附加选项
private string FName; // 语法名称
private int FIndex; // 序号
public string Pattern { get { return FPattern; } } // 正则表达式
public RegexOptions Options { get { return FOptions; } } // 正则表达式附加选项
public string Name { get { return FName; } } // 名称
public int Index { get { return FIndex; } } // 序号
public SyntaxItem(string APattern, RegexOptions AOptions,
string AName, int AIndex)
{
FPattern = APattern;
FOptions = AOptions;
FName = AName;
FIndex = AIndex;
}
}
/// <summary>
/// 语法分析返回项
/// </summary>
public class AnalyzeReslut
{
private SyntaxItem FItem; // 所属语法项
private string FBlock; // 文字块
public SyntaxItem Item { get { return FItem; } }
public string Block { get { return FBlock; } }
public AnalyzeReslut(SyntaxItem AItem, string ABlock)
{
FItem = AItem;
FBlock = ABlock;
}
} private List<SyntaxItem> FSyntaxItems = new List<SyntaxItem>();
private List<AnalyzeReslut> FAnalyzeResluts = new List<AnalyzeReslut>();
public List<SyntaxItem> SyntaxItems { get { return FSyntaxItems; } }
public List<AnalyzeReslut> AnalyzeResluts { get { return FAnalyzeResluts; } }
/// <summary>
/// 进行语法分析
/// </summary>
/// <param name="ACode">所分析的代码</param>
/// <returns>返回分析是否成功</returns>
public virtual bool Analyze(string ACode)
{
if (FSyntaxItems.Count <= 0)
return false;
if (ACode == null)
return false;
AnalyzeResluts.Clear();
string vCode = ACode;
bool vFind = true;
while (vFind && (vCode.Length > 0))
{
vFind = false;
foreach (SyntaxItem vSyntaxItem in FSyntaxItems)
{
bool reg = Regex.IsMatch(vCode, vSyntaxItem.Pattern, vSyntaxItem.Options);
if (reg)
{
AnalyzeResluts.Add(new AnalyzeReslut(vSyntaxItem,
Regex.Match(vCode, vSyntaxItem.Pattern,
vSyntaxItem.Options).Value));
vCode = Regex.Replace(vCode, vSyntaxItem.Pattern, "",
vSyntaxItem.Options);
vFind = true;
break;
}
}
}
return true;
} public string findgjz()
{
string aruslut="";
foreach (AnalyzeReslut ar in AnalyzeResluts)
{
if (ar.Item.Name == "标识符")
aruslut += ar.Block + ",";
}
return aruslut;
}
}
/// <summary>
/// sql语法分析
/// </summary>
public class TSqlEngine : SyntaxEngine
{
public TSqlEngine()
{
SyntaxItems.Add(new SyntaxItem(@"^\s+", RegexOptions.None,
"空白", SyntaxItems.Count));
SyntaxItems.Add(new SyntaxItem(@"^--[^\n]*[\n]?", RegexOptions.None,
"单行注释", SyntaxItems.Count));
SyntaxItems.Add(new SyntaxItem(@"^\/\*.*?\*\/", RegexOptions.None,
"多行注释", SyntaxItems.Count));
SyntaxItems.Add(new SyntaxItem(@"^\b(absolute|action|ada|add|admin|after|aggrate|" +
@"ALIAS|ALL|ALLOCATE|ALTER|AND|ANY|ARE|ARRAY|AS|ASC|" +
@"ASSERTION|AT|AUTHORIZATION|AVG|BACKUP|BEFORE|BEGIN|" +
@"BETWEEN|BINARY|BIT|BIT_LENGTH|BLOB|BOOLEAN|BOTH|" +
@"BREADTH|BREAK|BROWSE|BULK|BY|CALL|CASCADE|CASCADED|" +
@"CASE|CAST|CATALOG|CHAR|CHAR_LENGTH|CHARACTER|CHARACTER_LENGTH|" +
@"CHECK|CHECKPOINT|CLASS|CLOB|CLOSE|CLUSTERED|COALESCE|" +
@"COLLATE|COLLATION|COLUMN|COMMIT|COMPLETION|COMPUTE|" +
@"CONNECT|CONNECTION|CONSTRAINT|CONSTRAINTS|CONSTRUCTOR|" +
@"CONTAINS|CONTAINSTABLE|CONTINUE|CONVERT|CORRESPONDING|" +
@"COUNT|CREATE|CROSS|CUBE|CURRENT|CURRENT_DATE|CURRENT_PATH|" +
@"CURRENT_ROLE|CURRENT_TIME|CURRENT_TIMESTAMP|CURRENT_USER|CURSOR|" +
@"CYCLE|DATA|DATABASE|DATE|DAY|DBCC|DEALLOCATE|DEC|DECIMAL|DECLARE|" +
@"DEFAULT|DEFERRABLE|DEFERRED|DELETE|DENY|DEPTH|DEREF|DESC|DESCRIBE|" +
@"DESCRIPTOR|DESTROY|DESTRUCTOR|DETERMINISTIC|DIAGNOSTICS|DICTIONARY|" +
@"ISCONNECT|DISK|DISTINCT|DISTRIBUTED|DOMAIN|DOUBLE|DROP|DUMMY|DUMP|" +
@"DYNAMIC|EACH|ELSE|END|END-EXEC|EQUALS|ERRLVL|ESCAPE|EVERY|EXCEPT|" +
@"EXCEPTION|EXEC|EXECUTE|EXISTS|EXIT|EXTERNAL|EXTRACT|FALSE|FETCH|" +
@"FILE|FILLFACTOR|FIRST|FLOAT|FOR|FOREIGN|FORTRAN|FOUND|FREE|FREETEXT|" +
@"FREETEXTTABLE|FROM|FULL|FULLTEXTTABLE|FUNCTION|GENERAL|GET|GLOBAL|GO|" +
@"GOTO|GRANT|GROUP|GROUPING|HAVING|HOLDLOCK|HOST|HOUR|IDENTITY|IDENTITY_INSERT|" +
@"IDENTITYCOL|IF|IGNORE|IMMEDIATE|IN|INCLUDE|INDEX|INDICATOR|INITIALIZE|INITIALLY|" +
@"INNER|INOUT|INPUT|INSENSITIVE|INSERT|INT|INTEGER|INTERSECT|INTERVAL|INTO|IS|ISOLATION|" +
@"ITERATE|JOIN|KEY|KILL|LANGUAGE|LARGE|LAST|LATERAL|LEADING|LEFT|LESS|LEVEL|LIKE|LIMIT|" +
@"LINENO|LOAD|LOCAL|LOCALTIME|LOCALTIMESTAMP|LOCATOR|LOWER|MAP|MATCH|MAX|MIN|MINUTE|" +
@"MODIFIES|MODIFY|MODULE|MONTH|NAMES|NATIONAL|NATURAL|NCHAR|NCLOB|W|NEXT|NO|NOCHECK|" +
@"NONCLUSTERED|NONE|NOT|NULL|NULLIF|NUMERIC|OBJECT|OCTET_LENGTH|OF|OFF|OFFSETS|OLD|" +
@"ON|ONLY|OPEN|OPENDATASOURCE|OPENQUERY|OPENROWSET|OPENXML|OPERATION|OPTION|OR|ORDER|" +
@"ORDINALITY|OUT|OUTER|OUTPUT|OVER|OVERLAPS|PAD|PARAMETER|PARAMETERS|PARTIAL|PASCAL|" +
@"PATH|PERCENT|PLAN|POSITION|POSTFIX|PRECISION|PREFIX|PREORDER|PREPARE|PRESERVE|PRIMARY|" +
@"PRINT|PRIOR|PRIVILEGES|PROC|PROCEDURE|PUBLIC|RAISERROR|READ|READS|READTEXT|REAL|RECONFIGURE|" +
@"RECURSIVE|REF|REFERENCES|REFERENCING|RELATIVE|REPLICATION|RESTORE|RESTRICT|RESULT|RETURN|" +
@"RETURNS|REVOKE|RIGHT|ROLE|ROLLBACK|ROLLUP|ROUTINE|ROW|ROWCOUNT|ROWGUIDCOL|ROWS|RULE|SAVE|" +
@"SAVEPOINT|SCHEMA|SCOPE|SCROLL|SEARCH|SECOND|SECTION|SELECT|SEQUENCE|SESSION|SESSION_USER|SET|" +
@"SETS|SETUSER|SHUTDOWN|SIZE|SMALLINT|SOME|SPACE|SPECIFIC|SPECIFICTYPE|SQL|SQLCA|SQLCODE|SQLERROR|" +
@"SQLEXCEPTION|SQLSTATE|SQLWARNING|START|STATE|STATEMENT|STATIC|STATISTICS|STRUCTURE|SUBSTRING|UM|" +
@"SYSTEM_USER|TABLE|TEMPORARY|TERMINATE|TEXTSIZE|THAN|THEN|TIME|TIMESTAMP|TIMEZONE_HOUR|TIMEZONE_MINUTE|" +
@"TO|TOP|TRAILING|TRAN|TRANSACTION|TRANSLATE|TRANSLATION|TREAT|TRIGGER|TRIM|TRUE|TRUNCATE|TSEQUAL|UNDER|" +
@"UNION|UNIQUE|UNKNOWN|UNNEST|UPDATE|UPDATETEXT|UPPER|USAGE|USE|USER|USING|VALUE|VALUES|VARCHAR|VARIABLE|" +
@"VARYING|VIEW|WAITFOR|WHEN|WHENEVER|WHERE|WHILE|WITH|WITHOUT|WORK|WRITE|WRITETEXT|YEAR|ZONE)\b",
RegexOptions.IgnoreCase, "关键字", SyntaxItems.Count));
SyntaxItems.Add(new SyntaxItem(@"^(\+\=|\-\=|\&\&|\|\||\/\=|\&\=|\%\=|\~|\!|\+\+|\-\-|" +
@"\$|\%|\^|\&|\*|\(|\)|\+|\-|\=|\{|\}|\[|\]|\\|\;|\:|\<|\>|\?|\,|\.|\/)+",
RegexOptions.None, "标点符号", SyntaxItems.Count));
SyntaxItems.Add(new SyntaxItem(@"^(\d+(?!\.|x|e|d|m)u?)|^0x([\da-f]+(?!\.|x|m)u?)",
RegexOptions.IgnoreCase, "整数", SyntaxItems.Count));
SyntaxItems.Add(new SyntaxItem(@"^(\d+)?\.\d+((\+|\-)?e\d+)?(m|d|f)?|^\d+((\+|\-)?e\d+)?(m|d|f)",
RegexOptions.IgnoreCase, "浮点数", SyntaxItems.Count));
SyntaxItems.Add(new SyntaxItem(@"^'(('')*([^'])*)*'?",
RegexOptions.None, "字符串", SyntaxItems.Count));
SyntaxItems.Add(new SyntaxItem(@"^""([^""])*""|^\[([^\[^\]])*\]|^[^""^\[]\w*|^@@*\w+|^#\w+", RegexOptions.None,
"标识符", SyntaxItems.Count));
}
}
请教有SQL之类的源码最好
/*
利用正则表达式替换字符
调用:
1.获取字符串中的英文字符
declare @str varchar(100)
set @str='xyz123z5'
select dbo.regexReplace(@str,'[^a-zA-Z]','',1,1)
2.获取字符串中的英文字符长度
declare @str varchar(100)
set @str='xyz123z5'
select len(dbo.regexReplace(@str,'[^a-zA-Z]','',1,1))
*/
create function dbo.regexReplace
(
@source varchar(5000), --原字符串
@regexp varchar(1000), --正则表达式
@replace varchar(1000), --替换值
@globalReplace bit = 0, --是否是全局替换
@ignoreCase bit = 0 --是否忽略大小写
)
returnS varchar(1000) AS
begin
declare @hr integer
declare @objRegExp integer
declare @result varchar(5000)
exec @hr = sp_OACreate 'VBScript.RegExp', @objRegExp OUTPUT
IF @hr <> 0 begin
exec @hr = sp_OADestroy @objRegExp
return null
end
exec @hr = sp_OASetProperty @objRegExp, 'Pattern', @regexp
IF @hr <> 0 begin
exec @hr = sp_OADestroy @objRegExp
return null
end
exec @hr = sp_OASetProperty @objRegExp, 'Global', @globalReplace
IF @hr <> 0 begin
exec @hr = sp_OADestroy @objRegExp
return null
end
exec @hr = sp_OASetProperty @objRegExp, 'IgnoreCase', @ignoreCase
IF @hr <> 0 begin
exec @hr = sp_OADestroy @objRegExp
return null
end
exec @hr = sp_OAMethod @objRegExp, 'Replace', @result OUTPUT, @source, @replace
IF @hr <> 0 begin
exec @hr = sp_OADestroy @objRegExp
return null
end
exec @hr = sp_OADestroy @objRegExp
IF @hr <> 0 begin
return null
end
return @result
end
GO declare @sql varchar(8000)
set @sql='select * from table1 join table2 on 1>0'
select dbo.regexReplace(@sql,'.+?(from)(.+?)(join)(.+?)(on).+','$2 $4',1,1)
---结果
/*
table1 table2
*/
省的这么麻烦.
一定要这样做的话,想想其它办法,先让sql执行一下......