使用MySql，PHP和ADODB在准备好的语句中参数化IN子句 [英] Parameterised IN Clause in prepared statement using MySql,PHP and ADODB

问题描述

我正在编写一些SQL，并使用AdoDb连接到我的数据库并运行查询等等。我正在使用参数化查询，但遇到了麻烦。

I am writing some SQL and using AdoDb to connect to my database and run the queries and so on. I am using parametrized queries and have run into a snag.

它们是将值数组传递给AdoDb / MySql中的in_clause进行参数化的一种方法。

Is their a way to pass an array of values to an in_clause in AdoDb/MySql for parametrization.

我的问题是，如果我将准备好的字符串作为参数传递，即'test'，'test2'，'test3'，则它不能作为库或数据库会自动对其进行转义，并在开头和结尾处添加外部引号，因此所有内部引号都将自动转义，因此查询在查找'\'test\'，\'test2＼时不会返回任何内容＼'，\'test3\''，而不是我喂饱的食物。

My problem is that if I pass a prepared string as the parameter i.e. 'test','test2','test3' it does not work as the library or database auto escapes it and adds external quotes at the start and end so all the internal quotes are then auto escaped thus the query returns nothing as it looks for '\'test\',\'test2\',\'test3\'' as opposed to what I fed it.

已用另一种可能的方法更新了

<?php
$in_clause = implode(",", $first_names);

$query = "
SELECT    
    mytable_id_pk
FROM 
    mytable
WHERE
FIND_IN_SET(mytable_fname," . $DB->Param('first_names') . ")"

$stmt = $DB->Prepare($query);

$result = $DB->Execute($stmt,array($in_clause));
?>

推荐答案

我会这样做（因为我正在谷歌搜索有一阵子，谷歌没有任何用处）：

I would do it this way (as I was googling for a while and google came up with nothing useful):

$count = count($first_names);
$in_params = trim(str_repeat('?, ', $count), ', ');

$query = "
SELECT    
    mytable_id_pk
FROM 
    mytable
WHERE
    mytable_fname IN ({$in_params});";

$stmt = $DB->Prepare($query);
$result = $DB->Execute($stmt, $first_names);

应该这样做...

这篇关于使用MySql，PHP和ADODB在准备好的语句中参数化IN子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！