SQL参数化查询,即在SQL查询字符串中使用变量,在C++Builder中方法如下:
AnsiString strSql = "select * from LoginUser where StationID = :a\
and UserID= :b and UserPwd= :c ";
m_pAdo->Active = false;
m_pAdo->SQL->Clear();
m_pAdo->SQL->Text = strSql;
m_pAdo->Parameters->ParamByName("a")->Value = AnsiString(stLogin.szStationID).Trim();
m_pAdo->Parameters->ParamByName("b")->Value = AnsiString(stLogin.szUserName).Trim();
m_pAdo->Parameters->ParamByName("c")->Value = AnsiString(stLogin.szUserPassword).Trim();
m_pAdo->Active = true;
注意:
(1)字段为字符串,不需求加''
(2)参数不要与字段名字相同,大小写好象也不行
(3)Access和SqlServer中的变量都一样,用:开头
采用参数化查询的好处是,可以防SQL注入。
C#参数化查询代码如下:
using(SqlConnection conn= new SqlConnection(@"Data Source=.\SQLEXPRESS;
AttachDBFilename=|DataDirectory|\Database1.mdf;Integrated Security=true;User Instance=True"))
{
using(var cmd = conn.CreateCommand())
{
cmd.CommandText =" select count(*) from t_users where UserName=@un and Passwrod=@p";
cmd.Parameters.Add(new SqlParameter("un","admin"));
cmd.Parameters.Add(new SqlParameter("p","123"))
int i = Convert.ToInt32(cmd.ExecuteScalar());
if(i > 0)
{
Console.WriteLine("登录成功!");
}
else
{
Console.WriteLine("用户名或者密码错误!");
}
}
}
C#和SqlServer组合开发,参数前面加@