这是一位师哥写的类.哈哈. 之前我是一直用参数化的,看这个写起来比较快.大家感觉怎么样? 有没有其他的BUG呢
public static void Insert(ServiceInfo entity)
{
DBLayer.ExeSql(GetInsertSql(entity));
return;
}public static string GetInsertSql(ServiceInfo entity)
{
StringBuilder strSql = new StringBuilder("Insert ServiceInfo (");
strSql.Append("userid,");
strSql.Append("serviceid,");
strSql.Append("servicename,");
strSql.Append("companyid,");
strSql.Append("companyname,");
strSql.Append("worktime,");
strSql.Append("tellphone,");
strSql.Append("address,");
strSql.Append("consume,");
strSql.Append("busLine,");
strSql.Append("carbit,");
strSql.Append("img,");
strSql.Append("contest,");
strSql.Append("province,");
strSql.Append("city,");
strSql.Append("country,");
strSql.Append("support,");
strSql.Append("demand,");
strSql.Append("price,");
strSql.Append("Mark,");
strSql.Append("shenhe,");
strSql.Append("img)");
strSql.Append(" values (");
strSql.Append("'" + entity.userid + "',");
strSql.Append("'" + entity.serviceid + "',");
strSql.Append("'" + entity.servicename + "',");
strSql.Append("'" + entity.companyid + "',");
strSql.Append("'" + entity.companyname + "',");
strSql.Append("'" + entity.worktime + "',");
strSql.Append("'" + entity.tellphone + "',");
strSql.Append("'" + entity.address + "',");
strSql.Append("'" + entity.consume + "',");
strSql.Append("'" + entity.busLine + "',");
strSql.Append("'" + entity.carbit + "',");
strSql.Append("'" + entity.img + "',");
strSql.Append("'" + entity.contest + "',");
strSql.Append("'" + entity.province + "',");
strSql.Append("'" + entity.city + "',");
strSql.Append("'" + entity.country + "',");
strSql.Append("'" + entity.support + "',");
strSql.Append("'" + entity.demand + "',");
strSql.Append("'" + entity.price + "',");
strSql.Append("'" + entity.Mark + "',");
strSql.Append("'" + entity.shenhe + "',");
strSql.Append("'" + entity.img + "')");
return strSql.ToString();
} public static void ExeSql(string sql)
{
if (Func.filterSql(sql) == 1)
{
return ;
}
SqlConnection conn = new SqlConnection(ConnectionString);
SqlCommand cmd = new SqlCommand(sql, conn);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
} public static int filterSql(string sSql)
{
int srcLen, decLen = 0;
sSql = sSql.ToLower().Trim();
srcLen = sSql.Length;
sSql = sSql.Replace("exec ", "");
sSql = sSql.Replace("drop ", "");
sSql = sSql.Replace("master ", "");
sSql = sSql.Replace("truncate ", "");
sSql = sSql.Replace("declare ", "");
sSql = sSql.Replace("create ", "");
sSql = sSql.Replace("xp_", "no");
sSql = sSql.Replace("net user", "");
sSql = sSql.Replace("xp_cmdshell", "");
sSql = sSql.Replace("net localgroup administrators", "");
sSql = sSql.Replace("master.dbo.xp_cmdshell", "");
sSql = sSql.Replace("/add", "");
decLen = sSql.Length;
if (srcLen == decLen) return 0; else return 1;
}
public static void Insert(ServiceInfo entity)
{
DBLayer.ExeSql(GetInsertSql(entity));
return;
}public static string GetInsertSql(ServiceInfo entity)
{
StringBuilder strSql = new StringBuilder("Insert ServiceInfo (");
strSql.Append("userid,");
strSql.Append("serviceid,");
strSql.Append("servicename,");
strSql.Append("companyid,");
strSql.Append("companyname,");
strSql.Append("worktime,");
strSql.Append("tellphone,");
strSql.Append("address,");
strSql.Append("consume,");
strSql.Append("busLine,");
strSql.Append("carbit,");
strSql.Append("img,");
strSql.Append("contest,");
strSql.Append("province,");
strSql.Append("city,");
strSql.Append("country,");
strSql.Append("support,");
strSql.Append("demand,");
strSql.Append("price,");
strSql.Append("Mark,");
strSql.Append("shenhe,");
strSql.Append("img)");
strSql.Append(" values (");
strSql.Append("'" + entity.userid + "',");
strSql.Append("'" + entity.serviceid + "',");
strSql.Append("'" + entity.servicename + "',");
strSql.Append("'" + entity.companyid + "',");
strSql.Append("'" + entity.companyname + "',");
strSql.Append("'" + entity.worktime + "',");
strSql.Append("'" + entity.tellphone + "',");
strSql.Append("'" + entity.address + "',");
strSql.Append("'" + entity.consume + "',");
strSql.Append("'" + entity.busLine + "',");
strSql.Append("'" + entity.carbit + "',");
strSql.Append("'" + entity.img + "',");
strSql.Append("'" + entity.contest + "',");
strSql.Append("'" + entity.province + "',");
strSql.Append("'" + entity.city + "',");
strSql.Append("'" + entity.country + "',");
strSql.Append("'" + entity.support + "',");
strSql.Append("'" + entity.demand + "',");
strSql.Append("'" + entity.price + "',");
strSql.Append("'" + entity.Mark + "',");
strSql.Append("'" + entity.shenhe + "',");
strSql.Append("'" + entity.img + "')");
return strSql.ToString();
} public static void ExeSql(string sql)
{
if (Func.filterSql(sql) == 1)
{
return ;
}
SqlConnection conn = new SqlConnection(ConnectionString);
SqlCommand cmd = new SqlCommand(sql, conn);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
} public static int filterSql(string sSql)
{
int srcLen, decLen = 0;
sSql = sSql.ToLower().Trim();
srcLen = sSql.Length;
sSql = sSql.Replace("exec ", "");
sSql = sSql.Replace("drop ", "");
sSql = sSql.Replace("master ", "");
sSql = sSql.Replace("truncate ", "");
sSql = sSql.Replace("declare ", "");
sSql = sSql.Replace("create ", "");
sSql = sSql.Replace("xp_", "no");
sSql = sSql.Replace("net user", "");
sSql = sSql.Replace("xp_cmdshell", "");
sSql = sSql.Replace("net localgroup administrators", "");
sSql = sSql.Replace("master.dbo.xp_cmdshell", "");
sSql = sSql.Replace("/add", "");
decLen = sSql.Length;
if (srcLen == decLen) return 0; else return 1;
}
不过建立对象多的话肯定要给GC造成压力,不过我想得肯定大于失的.
SqlCommand cmd = new SqlCommand();
photoParms[0].Value = photo.Pho_Code;
photoParms[1].Value = photo.Pho_UserId;
photoParms[2].Value = photo.Pho_Time;
photoParms[3].Value = photo.Pho_ID;
parms = new SqlParameter[] {
new SqlParameter(PARM_Pho_Code, SqlDbType.BigInt),
new SqlParameter(PARM_Pho_UserId, SqlDbType.VarChar,20),
new SqlParameter(PARM_Pho_Time, SqlDbType.DateTime),
new SqlParameter(PARM_Pho_ID, SqlDbType.BigInt)};
如果是插入操作,最好还是用存储过程或者Parameter public override void ExeSql(string strSql, List<string> paramNames, List<object> objParamValues)
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = this._conn;
cmd.CommandText = strSql;
if ((paramNames != null) && (paramNames.Count != objParamValues.Count))
{
throw new DataFactory.GoberlDataDll.MyException.ParamValueNotMatchParamNameException();
}
//添加ParamItem
if (paramNames != null)
{
for (int i = 0; i < paramNames.Count; i++)
{
if (objParamValues[i].GetType().ToString() == "System.DateTime")
{
SqlParameter p = new SqlParameter();
p.ParameterName = paramNames[i];
p.SqlDbType = SqlDbType.Date;
p.Value = objParamValues[i];
cmd.Parameters.Add(p);
}
else
cmd.Parameters.AddWithValue(paramNames[i], objParamValues[i]);
}
}
else
{
throw new DataFactory.GoberlDataDll.MyException.ParamValueNotMatchParamNameException();
} try
{
this.OpenCon();
this.BeginTrans();
cmd.Transaction = this._trans;
cmd.ExecuteNonQuery();
this.CommitTrans();
}
catch (Exception exp)
{
this.RollbackTrans();
throw exp;
}
finally
{ cmd.Dispose();
this.CloseCon();
this._conn.Dispose();
} }
string sql = "select * from table1 where id=" + int.Parse(Request["id"]); string型参数拼接:
string sql = "select * from table1 where name='" + Request["name"].Replace("'","''") + "'"; ========
请问谁可以注入?
是能够防止SQL注入的,再说使用类,对传入的数据惊醒类型匹配,更能防止非法数据了。