将正则表达式绑定到PDO语句中的PDO参数 [英] binding regular expression to PDO parameter in PDO statement

问题描述

好，所以我对php和mysql还是相当陌生.我正在创建一个php前端到mysql数据库.我正在使用PDO驱动程序来访问数据库，因为它可以防止sql注入攻击.到目前为止，一直很好，直到我解决了这个问题.我有一个搜索功能，用户可以输入完整或不完整的公司名称来搜索有关它的数据.

Ok, so I am fairly new to php and mysql. I am creating a php front end to mysql database. I am using PDO driver to access the database, since it prevents sql injection attacks. So far it has been fine until I came to this problem. I have a search function where a user can type company name full or partial to search for data about it.

这是我用来在数据库中执行搜索的PDO语句:

Here is PDO statement I am using to carry out the search in database:

SELECT CompName FROM CompanyName 
WHERE CompName REGEXP :name 
ORDER BY CompName ASC LIMIT 1

因此，我可以准备，将搜索字段中的哪些用户类型绑定到参数名称并执行该语句.只要用户不键入任何元字符，它就可以工作.这是我插入PDO语句中而不是名称的非常​​基本的正则表达式:
^ whatusertyped-从最初开始，我正在寻找一个完整的比赛.由于某些公司名称确实包含句点，因此我希望用户能够键入这些字符和我的正则表达式，以将它们作为文字而不是元字符.到目前为止，这就是我一直在替换元字符以获得其字面意思的方式:

So then I can prepare, bind what user types in search field to parameter name and execute the statement. As long as user does not type any metacharacters it works. Here is the very basic regular expression I insert into the PDO statement instead of name:
^whatusertyped -since originally I am looking for a complete match. Since some company names do contain periods and such I want to be able user to type those characters and my regular expression to take them as literals as opposed to metacharacters. So far this is how I have been replacing metacharacters to get their literal meaning:

用户类型:C.查找以C开头的公司名称.

user types: C. to look for company name that starts with C.

php函数插入^和\\\\以获得^ C \\的输出.

php function inserts ^ and \\\\ to get an output of ^C\\.

mysql获取:^ C \\.并在公司名称的开头搜索C.，其中的句点是文字，而不是要说寻找通配符".

mysql gets: ^C\\. and searches for C. in the beginning of company name where period is literal and not meant to say "look for wild card character".

所以我正在尝试C.因为数据库中有一家公司的名字以此开头，我应该得到一个匹配，但是我没有，PDO语句返回一个空数组.

So I am trying for C. because there is a company in the database whose name starts like that and I should get a match, but I don't, the PDO statement returns back an empty array.

我确实尝试过查询:

SELECT CompName FROM CompanyName 
WHERE CompName REGEXP "^C\\\\." 
ORDER BY CompName ASC LIMIT 1

直接在mysql中获取匹配项，我也直接在PHP中执行查询，而无需准备，绑定并获取匹配项.在我看来，问题在于准备并将搜索项绑定到参数，但是我一直无法弄清楚它到底是什么以及如何解决它.我真的很想像我说的那样使用绑定和准备，以避免sql注入.

directly in mysql and got back a match and I also directly executed the query in PHP without prepare, bind and got back a match. The problem seems to me is in preparing and binding the search term to a parameter, but I have not been able to figure out what it is exactly and how to solve it. I really want to use bind and prepare like I said to avoid sql injection.

任何帮助将不胜感激.希望我能清楚地解释这个问题.

Any help would be greatly appreciated. Hopefully I explained the problem clearly.

推荐答案

我相信您绑定到:name的值应该只是^C\.，而不是^C\\..您不需要为 sql 级别进行任何转义，只需为 regex 级别进行转义(即，对于文字.的\.).

I believe the value you bind to :name should be just ^C\., not ^C\\.. You don't need to do any escaping for the sql level, just for the regex level (i.e., \. for a literal .).

此外，请考虑使用WHERE CompName LIKE 'C.%'.您在这里不需要正则表达式.您将这样使用:

Also, consider using WHERE CompName LIKE 'C.%'. You don't need a regex here. You would use like so:

function like_escape($s) {
// Do like-expression escaping
    return addcslashes($s, '%_');
}
$searchprefix = 'C.';
$sql = 'SELECT CompName FROM CompanyName WHERE CompName LIKE ? ORDER BY CompName ASC';
$stmt = $db->prepare($sql);
$likeclause = like_escape($searchprefix).'%';
$db->bindValue(1, $likeclause, PDO::PARAM_STR);
$stmt->execute();

这篇关于将正则表达式绑定到PDO语句中的PDO参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！