tinkphp5中sql写法错误示例

2025年11月7日 0条评论

tinkphp5中sql写法错误示例

示例1：

$info = Db::table('client_good')->where(" id", 14380)->limit(3)->select();

SELECT * FROM `client_good` WHERE ( id ) LIMIT 3

如果是下面这样就很危险

$info = Db::table('client_good')->where(" id", 14380)->delete();

所以下面这个字符串很恐怖 ->where("

推荐数组写

//测试数组
$where[" id "] = 14380;
$info = Db::table('client_good')->where($where)->limit(3)->select();

ps：

$info = Db::table('client_good')->where("id='$good_id'")->limit(3)->select(); 这种存在sql注入问题，不过不考虑sql注入，范围问题比下面好。

$info = Db::table('client_good')->where(" id", $good_id)->limit(3)->select(); //这种会恒成立，危险性极大

$where[" id "] = 14380;
$info = Db::table('client_good')->where($where)->limit(3)->select(); //最优方案 id会被trim

$sql = "update client_good set click_num=click_num+1 where id=:g_id"; //原生的sql方案
$flag = Db::execute($sql, ['g_id' => $g_id]); //成功会输出1

//下面的在thinkphp5就不行，在thinkphp8的thinkorm4.0 就没这个问题了。

错误示例：查询表达式错误:0724323738b03a3209ba4ae5570b87f2

$where = [];

$where["clientkeynum"] = "0724323738B03A3209BA4AE5570B87F2";

$whereinit = [];

$whereinit["clientkeynum"] = "0724323738B03A3209BA4AE5570B87F2";

$list = Db::table('card_record_delay')->where($whereinit)->where($where)->select();

print_r($list);

echo Db::table('card_record_delay')->getLastSql();

die;

复杂sql非原生：

$whereinit=[];
$whereinit["clientkeynum"]=$clientkeynum;
$p = $param['page'] ? $param['page'] : 1;
$pagesize = $param['limit'];
$offset = $pagesize * ($p - 1);
//计算记录偏移量
$card_number_id = isset($param['card_number_id']) ? $param['card_number_id'] : '';
$card_number = isset($param['card_number']) ? $param['card_number'] : '';
$user_name = isset($param['user_name']) ? $param['user_name'] : '';
$oper_text = isset($param['oper_text']) ? $param['oper_text'] : '';
$where = "1 = 1 and clientkeynum=:clientkeynum ";
$wherebind = [];
$wherebind["clientkeynum"] = $clientkeynum;
$between_time = isset($param['between_time']) ? $param['between_time'] : '';
//如果下单日期不为空则
if ($between_time != '') {
$between_time_arr = explode('~', $between_time);
$start_time = $between_time_arr[0];
$end_time = $between_time_arr[1];
$where .= " and create_time >=:start_time and create_time <= :end_time ";
$wherebind["start_time"] = $start_time;
$wherebind["end_time"] = $end_time;
}
if ($card_number_id != '') {
$where .= "and card_number_id = :card_number_id ";
$wherebind["card_number_id"] = $card_number_id;
}
if ($card_number != '') {
$where .= "and card_number = :card_number ";
$wherebind["card_number"] = $card_number;
}
if ($user_name != '') {
$where .= "and user_name = :user_name ";
$wherebind["user_name"] = $user_name;
}
if ($oper_text != '') {
$where .= " and `oper_text` like :oper_text ";
$wherebind["oper_text"] = "%{$oper_text}%"; // 绑定值添加%，实现模糊匹配
}
//默认按照时间降序
$count = Db::table('card_log')->where($whereinit)->where($where,$wherebind)->count();
$list = Db::table('card_log')->where($whereinit)->where($where,$wherebind)->order('log_id', 'desc')->limit($offset . ',' . $pagesize)->select();