如何停止FluentPDO错误地推断表名称 [英] How to stop FluentPDO incorrectly inferring a table name

问题描述

我有一个用户表，我希望能够通过电子邮件列过滤该表，例如，我可以看到所有具有"gmail.com"帐户的用户.

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.

我当前的fpdo查询如下:

My current fpdo query looks like this:

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

当我将$filter_email设置为 a @ b 时，一切正常，并且FluentPDO生成以下SQL语句:

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%" 

但是，如果我搜索 a@b.c ，FluentPDO会尝试查找表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%"

我不知道FluentPDO如何将b.c视为要加入的表，或者如何停止它.

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

解决方案

主要感谢您的选择，同时也感谢aynber，这是有效的解决方案:

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();

我的实际查询检查了三个不同的电子邮件字段，但是使用三个?并附加三个$filter_email效果很好:

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
)

推荐答案

它(显然不是)智能连接生成器"可能看到了.并认为它与另一个表有关.您可能要向作者提交错误.

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.

但是，您容易受到SQL注入的攻击，将输入直接连接到查询中.解决该问题也可能会解决您的加入问题.快速查看文档，参数绑定语法看起来应该是这样的:

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();

这篇关于如何停止FluentPDO错误地推断表名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！