dongwenghe2416 2016-02-19 15:00
浏览 19
已采纳

如何阻止FluentPDO错误地推断表名

I have a table of users, and I want to be able to filter the table by the email column, so I can see all the users with 'gmail.com' accounts, for example.

My current fpdo query looks like this:

$filter_email = trim($_GET['email']);
$fpdo->from('users')
    ->where('users.email LIKE "%' . $filter_email . '%"')
    ->fetchAll();

When I set $filter_email to a@b, everything works fine, and FluentPDO generates this SQL statement:

SELECT users.* FROM users
WHERE users.email LIKE "%a@b%" 

But if I search for a@b.c FluentPDO tries to find the table b and errors

SELECT users.* FROM users
LEFT JOIN b ON b.id = users.b_id
WHERE users.email LIKE "%a@b.c%"

I don't know how FluentPDO sees b.c as a table to join on, or how to stop it.

SOLUTION

Thanks mostly to deceze and also to aynber, here's the working solution:

$filter_email = '%'.trim($_GET['email']).'%';
$fpdo->from('users')
    ->where('users.email LIKE ?',$filter_email)
    ->fetchAll();

My actual query checks three different email fields, but using three ? and appending $filter_email three times works just fine:

->where(
    '(users.email1 LIKE ? OR users.email2 LIKE ? OR users.email1 LIKE ?)',
    $filter_email,
    $filter_email,
    $filter_email
)
  • 写回答

1条回答 默认 最新

  • dongtangze6393 2016-02-19 15:20
    关注

    Its (apparently not so) "smart join builder" probably sees the . and thinks it relates to another table. You might want to file a bug with the author.

    However, you're vulnerable to SQL injection concatenating the input directly into the query like that. Solving that will probably also solve your join issue. Quickly looking over the documentation, the parameter binding syntax looks like it should be this:

    $fpdo->from('users')
         ->where('users.email LIKE ?', '%' . trim($_GET['email']) . '%')
         ->fetchAll();
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥50 汇编语言除法溢出问题
  • ¥65 C++实现删除N个数据列表共有的元素
  • ¥15 Visual Studio问题
  • ¥15 state显示变量是字符串形式,但是仍然红色,无法引用,并显示类型不匹配
  • ¥20 求一个html代码,有偿
  • ¥100 关于使用MATLAB中copularnd函数的问题
  • ¥20 在虚拟机的pycharm上
  • ¥15 jupyterthemes 设置完毕后没有效果
  • ¥15 matlab图像高斯低通滤波
  • ¥15 针对曲面部件的制孔路径规划,大家有什么思路吗