在线急等 求帮忙解析SQL语句问题! 本帖最后由 lxcnn 于 2008-11-13 21:03:03 编辑 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 这个有些麻烦需要写多个正则 逐一匹配 比如 "from xxx" "update xxx" .... 匹配成功后xxx就是表名等等吧,需要把sql每一种操作 的情况都匹配出来。 http://www.blogjava.net/anwenhao/archive/2007/06/25/126127.html 将传过来的Sql语句连接Sql Server后执行(请一定连接没有任何表的库);这样会获取到异常 如:对象名 'CI_Meet' 无效,对象名 'CI_MyMeet' 无效。解析出错后的异常信息即可获取全部表名;然后在库中创建所解析到的表再次执行Sql语句同样会出异常如:列名 'AAA' 无效,列名 'BBB' 无效。再次解析异常信息即可的到字段名不知道这方法行不,您可以试下 这种情况太复杂了,如果来个复合查询比如select * from (select a,b from ta where 条件) t inner join ta tt on t.a = tt.a你这种情况怎么去判断来个*号你啥列名都得不到 你这个有点复杂了啊就是用正则也不好解决万一对方传的是一个嵌套的SQL就很难处理如比这种:select * from tab1 t1 inner join (select * from tab2) t2 on t1.pid=t2.id还有inner join 可以写成 join是个强贴,关注 你这个有点复杂了啊就是用正则也不好解决万一对方传的是一个嵌套的SQL就很难处理如比这种:select * from tab1 t1 inner join (select * from tab2) t2 on t1.pid=t2.id还有inner join 可以写成 join是个强贴,关注 我想,这个还是要具体问题具体分析比较好,没必要非得搞个通用方法出来才罢休。。LZ应该说一下你的这个SQL语句是不是真的要包括所有复杂的情况? 自定义下命名格式,如表:tb_User,tb_Product;列名:col_User,col_Pwd;存储过程proc_InsertUser.根据前缀来判断 引用 15 楼 xuanlv123 的回复:每天一顶 我想,这个还是要具体问题具体分析比较好,没必要非得搞个通用方法出来才罢休。。 LZ应该说一下你的这个SQL语句是不是真的要包括所有复杂的情况?恩 是包含所有情况的SQL语句 大家看我这个思路怎样,利用SQL数据库的报错服务 解析出来 表名,列名。来替换using System;using System.Data;using System.Collections.Generic;using System.Data.SqlClient;using System.Text;using System.Collections;using System.Text.RegularExpressions;public partial class _Default : System.Web.UI.Page { public int a = 0; protected void Page_Load(object sender, EventArgs e) { } /// <summary> /// 查找字符在字符串中的位置 /// </summary> /// <param name="matchStr">字符</param> /// <param name="searchedStr">字符串</param> /// <param name="startPos">查找起始位置</param> /// <returns></returns> private static int[] FindAll(string matchStr, string searchedStr, int startPos) { int foundPos = -1; int count = 0; List<int> foundItems = new List<int>(); do { foundPos = searchedStr.IndexOf(matchStr,startPos); if (foundPos > -1) { startPos = foundPos + 1; count++; foundItems.Add(foundPos); Console.WriteLine("Found item at position:"+foundPos.ToString()); } } while (foundPos > -1 && startPos < searchedStr.Length); return ((int[])foundItems.ToArray()); } /// <summary> /// 去掉字符串中的相同字符 /// </summary> /// <param name="str">错误信息字符串</param> /// <returns></returns> private string DelStr(string str) { str = Regex.Replace(str, @"[\n\r]", ""); string strlist = ""; string[] a = str.Split('。'); ArrayList mylist = new ArrayList(); for (int i = 0; i < a.Length; i++) { bool IsExist = true; for (int j = 0; j < mylist.Count; j++) { if (mylist[j].ToString() == a[i]) { IsExist = false; break; } } if (IsExist) mylist.Add(a[i]); } for (int k = 0; k < mylist.Count; k++) { strlist += mylist[k]; } return strlist; } /// <summary> /// 查找并修改SQl语句中的表名 /// </summary> /// <param name="strsql">SQL语句</param> /// <param name="wrong">错误信息</param> /// <returns></returns> private string FindTable(string strsql,string wrong) { wrong = DelStr(wrong); string word=""; string testword = ""; int[] findtb = FindAll("'", wrong, 0); for (int i = 0; i < findtb.Length; i = i + 2) { word = wrong.Substring(findtb[i] + 1, findtb[i + 1] - findtb[i] - 1); testword = DBUsersTableName(word); if (testword != "-1") { strsql = strsql.Replace(word,testword); } else { a++; return "表名输入错误"; } } return strsql; } /// <summary> /// 查找并修改SQL语句中的列名 /// </summary> /// <param name="strsql"></param> /// <param name="wrong"></param> /// <returns></returns> private string FindList(string strsql, string wrong) { wrong = DelStr(wrong); string word = ""; string testword = ""; int[] findtb = FindAll("'", wrong, 0); for (int i = 0; i < findtb.Length; i = i + 2) { word = wrong.Substring(findtb[i] + 1, findtb[i + 1] - findtb[i] - 1); testword = DBUsersTableLine(word); if (testword != "-1") { strsql = strsql.Replace(word, testword); } else { a++; return "列名输入错误"; } } return strsql; } /// <summary> /// 查询修改过后对应的表名 /// </summary> /// <param name="word">原始表名</param> /// <returns></returns> public string DBUsersTableName(string word) { string connectiong = "server=10.104.11.242;database=test;uid=sa;pwd=242"; SqlConnection conn = new SqlConnection(connectiong); string strsql = "select distinct youngtablename from userstable where oldtablename='" + word + "'"; conn.Open(); SqlCommand cmd = new SqlCommand(strsql, conn); try { SqlDataAdapter sda = new SqlDataAdapter(cmd); DataTable dt = new DataTable(); sda.Fill(dt); return dt.Rows[0][0].ToString(); } catch (Exception) { return "-1"; } finally { conn.Close(); } } /// <summary> /// 查询修改过后对应的列名 /// </summary> /// <param name="word">原始列名</param> /// <returns></returns> public string DBUsersTableLine(string word) { string connectiong = "server=10.104.11.242;database=test;uid=sa;pwd=242"; SqlConnection conn = new SqlConnection(connectiong); string strsql = "select distinct younglinename from userstable where oldlinename='" + word + "'"; conn.Open(); SqlCommand cmd = new SqlCommand(strsql, conn); try { SqlDataAdapter sda = new SqlDataAdapter(cmd); DataTable dt = new DataTable(); sda.Fill(dt); return dt.Rows[0][0].ToString(); } catch (Exception) { return "-1"; } finally { conn.Close(); } } /// <summary> /// 连接数据库显示数据 /// </summary> /// <param name="strsql">查询SQL语句</param> /// <returns>1语句正确,返回结果。否则返回错误信息</returns> public string DBTest(string strsql) { string connectiong = "server=10.104.11.242;database=test;uid=sa;pwd=242"; SqlConnection conn = new SqlConnection(connectiong); SqlCommand cmd = new SqlCommand(strsql, conn); SqlDataAdapter sda = new SqlDataAdapter(cmd); DataSet ds = new DataSet(); try { sda.Fill(ds); this.GridView1.DataSource = ds; this.GridView1.DataBind(); return "1"; } catch (SqlException ex) { return ex.Message; } finally { conn.Dispose(); conn.Close(); } } /// <summary> /// 点击事件 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void Button1_Click(object sender, EventArgs e) { string strsql = this.TextBox1.Text; DBbind(strsql); } /// <summary> /// 分析原始SQL语句错误原因 /// </summary> /// <param name="strsql">查询SQL语句</param> private void DBbind(string strsql) { string wrong = DBTest(strsql); while (wrong != "1") { int[] find = FindAll("对象名", wrong, 0); int[] find1 = FindAll("列名", wrong, 0); if (find.Length > 0 || find1.Length > 0) { if (find.Length > 0) { this.Label1.Text = FindTable(strsql, wrong); strsql = this.Label1.Text; } else { this.Label1.Text = FindList(strsql, wrong); } if (a == 0) wrong = DBTest(this.Label1.Text); else break; } else { this.Label1.Text = "sql语句错误"; break; } } }} 利用数据库来SQL分析来解决这问题,想法比较巧妙.但要想干净的解决这具问题,恐怕还是要写一个SQL 解析器,其解析器的功能可以根据实际需要来决定,肯定不必做得象SQL SERVER的那么强.此问中可以考虑只解析SELECT 有关于SQL解析器的 大概例子吗? c#怎么调用java生成的RSA 公钥进行加密? C#窗体常识 什么是单类模式? 是不是指单例模式 menuStrip控件如何在后面加"CTRL+P"这样的提示 SQL Server 2005 安装问题 请教SOAP的研究 请问如何通过C#在EXCEL中插入一行? c#中reportview的问题 结构体赋值问题 [初学者问题]如何用button控件实现带有用户类型选择的用户名密码提交 DataTable到DataView再到DataTable的问题 求一实现方法 正则表达式
需要写多个正则 逐一匹配
比如 "from xxx" "update xxx" .... 匹配成功后xxx就是表名
等等吧,需要把sql每一种操作 的情况都匹配出来。
这样会获取到异常
如:
对象名 'CI_Meet' 无效,
对象名 'CI_MyMeet' 无效。
解析出错后的异常信息即可获取全部表名;然后在库中创建所解析到的表再次执行Sql语句
同样会出异常
如:
列名 'AAA' 无效,
列名 'BBB' 无效。
再次解析异常信息即可的到字段名不知道这方法行不,您可以试下
比如
select * from (select a,b from ta where 条件) t inner join ta tt on t.a = tt.a你这种情况怎么去判断来个*号你啥列名都得不到
就是用正则也不好解决万一对方传的是一个嵌套的SQL就很难处理如比这种:select * from tab1 t1 inner join (select * from tab2) t2 on t1.pid=t2.id
还有inner join 可以写成 join是个强贴,关注
就是用正则也不好解决万一对方传的是一个嵌套的SQL就很难处理如比这种:select * from tab1 t1 inner join (select * from tab2) t2 on t1.pid=t2.id
还有inner join 可以写成 join是个强贴,关注
我想,这个还是要具体问题具体分析比较好,没必要非得搞个通用方法出来才罢休。。LZ应该说一下你的这个SQL语句是不是真的要包括所有复杂的情况?
每天一顶
我想,这个还是要具体问题具体分析比较好,没必要非得搞个通用方法出来才罢休。。 LZ应该说一下你的这个SQL语句是不是真的要包括所有复杂的情况?恩 是包含所有情况的SQL语句
using System.Data;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Text;
using System.Collections;
using System.Text.RegularExpressions;public partial class _Default : System.Web.UI.Page
{ public int a = 0;
protected void Page_Load(object sender, EventArgs e)
{ } /// <summary>
/// 查找字符在字符串中的位置
/// </summary>
/// <param name="matchStr">字符</param>
/// <param name="searchedStr">字符串</param>
/// <param name="startPos">查找起始位置</param>
/// <returns></returns>
private static int[] FindAll(string matchStr, string searchedStr, int startPos)
{
int foundPos = -1;
int count = 0;
List<int> foundItems = new List<int>();
do
{
foundPos = searchedStr.IndexOf(matchStr,startPos);
if (foundPos > -1)
{
startPos = foundPos + 1;
count++;
foundItems.Add(foundPos);
Console.WriteLine("Found item at position:"+foundPos.ToString());
}
} while (foundPos > -1 && startPos < searchedStr.Length);
return ((int[])foundItems.ToArray());
} /// <summary>
/// 去掉字符串中的相同字符
/// </summary>
/// <param name="str">错误信息字符串</param>
/// <returns></returns>
private string DelStr(string str)
{
str = Regex.Replace(str, @"[\n\r]", "");
string strlist = "";
string[] a = str.Split('。');
ArrayList mylist = new ArrayList();
for (int i = 0; i < a.Length; i++)
{
bool IsExist = true;
for (int j = 0; j < mylist.Count; j++)
{
if (mylist[j].ToString() == a[i])
{
IsExist = false;
break;
}
}
if (IsExist)
mylist.Add(a[i]);
}
for (int k = 0; k < mylist.Count; k++)
{
strlist += mylist[k];
}
return strlist;
} /// <summary>
/// 查找并修改SQl语句中的表名
/// </summary>
/// <param name="strsql">SQL语句</param>
/// <param name="wrong">错误信息</param>
/// <returns></returns>
private string FindTable(string strsql,string wrong)
{
wrong = DelStr(wrong);
string word="";
string testword = "";
int[] findtb = FindAll("'", wrong, 0);
for (int i = 0; i < findtb.Length; i = i + 2)
{
word = wrong.Substring(findtb[i] + 1, findtb[i + 1] - findtb[i] - 1);
testword = DBUsersTableName(word);
if (testword != "-1")
{
strsql = strsql.Replace(word,testword);
}
else
{
a++;
return "表名输入错误";
}
}
return strsql;
} /// <summary>
/// 查找并修改SQL语句中的列名
/// </summary>
/// <param name="strsql"></param>
/// <param name="wrong"></param>
/// <returns></returns>
private string FindList(string strsql, string wrong)
{
wrong = DelStr(wrong);
string word = "";
string testword = "";
int[] findtb = FindAll("'", wrong, 0);
for (int i = 0; i < findtb.Length; i = i + 2)
{
word = wrong.Substring(findtb[i] + 1, findtb[i + 1] - findtb[i] - 1);
testword = DBUsersTableLine(word);
if (testword != "-1")
{
strsql = strsql.Replace(word, testword);
}
else
{
a++;
return "列名输入错误";
}
}
return strsql;
} /// <summary>
/// 查询修改过后对应的表名
/// </summary>
/// <param name="word">原始表名</param>
/// <returns></returns>
public string DBUsersTableName(string word)
{
string connectiong = "server=10.104.11.242;database=test;uid=sa;pwd=242";
SqlConnection conn = new SqlConnection(connectiong);
string strsql = "select distinct youngtablename from userstable where oldtablename='" + word + "'";
conn.Open();
SqlCommand cmd = new SqlCommand(strsql, conn);
try
{
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
sda.Fill(dt);
return dt.Rows[0][0].ToString();
}
catch (Exception)
{
return "-1";
}
finally
{
conn.Close();
}
} /// <summary>
/// 查询修改过后对应的列名
/// </summary>
/// <param name="word">原始列名</param>
/// <returns></returns>
public string DBUsersTableLine(string word)
{
string connectiong = "server=10.104.11.242;database=test;uid=sa;pwd=242";
SqlConnection conn = new SqlConnection(connectiong);
string strsql = "select distinct younglinename from userstable where oldlinename='" + word + "'";
conn.Open();
SqlCommand cmd = new SqlCommand(strsql, conn);
try
{
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
sda.Fill(dt);
return dt.Rows[0][0].ToString();
}
catch (Exception)
{
return "-1";
}
finally
{
conn.Close();
}
} /// <summary>
/// 连接数据库显示数据
/// </summary>
/// <param name="strsql">查询SQL语句</param>
/// <returns>1语句正确,返回结果。否则返回错误信息</returns>
public string DBTest(string strsql)
{
string connectiong = "server=10.104.11.242;database=test;uid=sa;pwd=242";
SqlConnection conn = new SqlConnection(connectiong);
SqlCommand cmd = new SqlCommand(strsql, conn);
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
try
{
sda.Fill(ds);
this.GridView1.DataSource = ds;
this.GridView1.DataBind();
return "1";
}
catch (SqlException ex)
{
return ex.Message;
}
finally
{
conn.Dispose();
conn.Close();
}
} /// <summary>
/// 点击事件
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void Button1_Click(object sender, EventArgs e)
{
string strsql = this.TextBox1.Text;
DBbind(strsql);
} /// <summary>
/// 分析原始SQL语句错误原因
/// </summary>
/// <param name="strsql">查询SQL语句</param>
private void DBbind(string strsql)
{
string wrong = DBTest(strsql);
while (wrong != "1")
{
int[] find = FindAll("对象名", wrong, 0);
int[] find1 = FindAll("列名", wrong, 0);
if (find.Length > 0 || find1.Length > 0)
{
if (find.Length > 0)
{
this.Label1.Text = FindTable(strsql, wrong);
strsql = this.Label1.Text;
}
else
{
this.Label1.Text = FindList(strsql, wrong);
}
if (a == 0)
wrong = DBTest(this.Label1.Text);
else
break;
}
else
{
this.Label1.Text = "sql语句错误";
break;
}
}
}
}
但要想干净的解决这具问题,恐怕还是要写一个SQL 解析器,其解析器的功能可以根据实际需要来决定,肯定不必做得象SQL SERVER的那么强.此问中可以考虑只解析SELECT