public int UserLogin(string userName, string userPass)
{
SqlCommand com = new SqlCommand("select influence from UserInfo where name= '"+userName+"' and pass= '"+userPass+"' ", DBconn());
int bol;
bol = Convert.ToInt32(com.ExecuteScalar());
return bol;
} SqlCommand com = new SqlCommand("select influence from UserInfo where name= '"+userName+"' and pass= '"+userPass+"' ", DBconn());
我写成
SqlCommand com = new SqlCommand("select influence from UserInfo where name= '{0}' and pass= '{1}' ", DBconn()); 为啥查出来的结果就不对了呢?
还有@userName @userPass 怎么用,能帮忙讲讲各自的好处么
{
SqlCommand com = new SqlCommand("select influence from UserInfo where name= '"+userName+"' and pass= '"+userPass+"' ", DBconn());
int bol;
bol = Convert.ToInt32(com.ExecuteScalar());
return bol;
} SqlCommand com = new SqlCommand("select influence from UserInfo where name= '"+userName+"' and pass= '"+userPass+"' ", DBconn());
我写成
SqlCommand com = new SqlCommand("select influence from UserInfo where name= '{0}' and pass= '{1}' ", DBconn()); 为啥查出来的结果就不对了呢?
还有@userName @userPass 怎么用,能帮忙讲讲各自的好处么
/// <summary>
/// 获取dt
/// </summary>
/// <param name="strSql">sql语句</param>
/// <param name="sqlCnn">连接数据库</param>
/// <returns></returns>
public static DataTable dtResult(string strSql, params SqlParameter[] pars)
{
sqlCnn = new SqlConnection(strCnn);
sqlCmd = new SqlCommand(strSql, sqlCnn);
sqlCmd.CommandType = CommandType.Text;
if (pars != null && pars.Length > 0)
{
foreach (SqlParameter par in pars)
{
sqlCmd.Parameters.Add(par);
}
}
DataTable dt = new DataTable();
sqlAdapter = new SqlDataAdapter(sqlCmd);
try
{
cnnOpen();
sqlAdapter.Fill(dt);
}
catch (Exception ex)
{ }
finally
{
myDispose();
}
return dt;
}
#endregion
from tb_HM_EmpDetail as t1
join tb_HM_UserAndClient as t2
on t1.EmpAccount=t2.UserAccount
where t1.EmpAccount=@EmpAccount
and t1.EmpPassword=@EmpPassword
and (t2.UserFlag='TUser' or t2.UserFlag='TAdmin')";
List<SqlParameter> pars = new List<SqlParameter>();
pars.Add(new SqlParameter("@EmpAccount", strUserID));
pars.Add(new SqlParameter("@EmpPassword", clsPublic.GetFormsStringByMd5(strPassword)));
DataTable dt = sqlConnecting.dtResult(strSql, pars.ToArray());#region 获取dt
/// <summary>
/// 获取dt
/// </summary>
/// <param name="strSql">sql语句</param>
/// <param name="sqlCnn">连接数据库</param>
/// <returns></returns>
public static DataTable dtResult(string strSql, params SqlParameter[] pars)
{
sqlCnn = new SqlConnection(strCnn);
sqlCmd = new SqlCommand(strSql, sqlCnn);
sqlCmd.CommandType = CommandType.Text;
if (pars != null && pars.Length > 0)
{
foreach (SqlParameter par in pars)
{
sqlCmd.Parameters.Add(par);
}
}
DataTable dt = new DataTable();
sqlAdapter = new SqlDataAdapter(sqlCmd);
try
{
cnnOpen();
sqlAdapter.Fill(dt);
}
catch (Exception ex)
{ }
finally
{
myDispose();
}
return dt;
}
#endregion
LZ最好用参数传值,要不很容易给SQL注入的
是这样用滴。
使用传参可以防止sql注入。
/// <summary>
/// 获取dt
/// </summary>
/// <param name="strSql">sql语句</param>
/// <param name="sqlCnn">连接数据库</param>
/// <returns></returns>
public static DataTable dtResult(string strSql, params SqlParameter[] pars)
这个方法写在一个公共类里面,然后每次调用就可以了,而不必要每个页面都去写一大堆不必要的代码,至于params SqlParameter[] pars这样的定义是为了传参为可变而设置的,比如说你传进来的可能是1或2或3或没有参数或N个参数都可以的,用法你看看我在3楼发的回复应该知道了,用@作为参数可以防止SQL注入,因为会把微软会把参数的值转作为一个字符串来处理,像select * from user where userid='+userid+' and pwd='+pwd+' 这样写的话,有可能用户会输入 xxx or 1=1 这样就无论输入什么都会是通过的了
注意下面using的使用:
StringBuilder sb = new StringBuilder(256);
sb.Append("select s_region,s_category,sum(n_count) from ");
sb.Append(viewname);
sb.Append(" where d_uploadTime between :startTime and :endTime");
if (!string.IsNullOrEmpty(region))
{
sb.Append(" and s_region=:region");
}
sb.Append(" and n_type=:type");
sb.Append(" group by s_region,s_category"); IList<UploadLogData> uploadLogDataList = new List<UploadLogData>();
using (DbCommand dbCommand = database.GetSqlStringCommand(sb.ToString()))
{
database.AddInParameter(dbCommand, ":startTime", DbType.DateTime, startTime);
database.AddInParameter(dbCommand, ":endTime", DbType.DateTime, endTime);
if (!string.IsNullOrEmpty(region))
{
database.AddInParameter(dbCommand, ":region", DbType.String, region);
}
database.AddInParameter(dbCommand, ":type", DbType.Int32, (int)type); using (IDataReader reader = database.ExecuteReader(dbCommand))
{
while (reader.Read())
{
UploadLogData uploadLogData = new UploadLogData();
uploadLogData.Region = reader.GetString(0);
uploadLogData.Category = reader.GetString(1);
uploadLogData.Count = reader.GetInt32(2); uploadLogDataList.Add(uploadLogData);
}
}
} return uploadLogDataList;
再说这样使用 的好处:这样是屏蔽sql注入攻击的最好的方法。.Net会帮你自动的屏蔽特殊字符。以后注意使用存储过程和自己写sql都尽量使用这种参数绑定的方式。不用拼接sql以及执行动态sql,那都是很危险的。
using (SqlConnection conn = DBHelper.Conn)
{
try
{
using (SqlCommand comm = new SqlCommand(sql, conn))
{
sql = "insert into progressfile values(@title,@district,@photo,@itemId)";
comm.Parameters.Add("@title", SqlDbType.VarChar, 50).Value = p.Title;
comm.Parameters.Add("@district", SqlDbType.VarChar, 5000).Value = p.Districk;
comm.Parameters.Add("@photo", SqlDbType.Image, p.Pictrue.Length).Value = p.Pictrue;
comm.Parameters.Add("@itemId", SqlDbType.Int).Value = p.ItemId;
comm.CommandText = sql;
return comm.ExecuteNonQuery();
}
}
catch (Exception ex)
{
myWeb.DAL.Regulation.SysService.WriteLog(ex);
return 0;
}
finally
{
conn.Close(); }
}
mssqlserver的例子