CODEWITHSUNDEEP
phpmysqlyii2
law_81
law_81

Reputation: 2430

Problem with Yii2 PDO Statement Postgres Query

I have some problems/doubt with PDO statement and Yii2 query. I've search and read some question here but i didn't find a solution. So i have an array like this

 array (size=3)
      0 => string '12345' 
      1 => string '6789' 
      2 => string '101258'

From this array i create a string to insert in my IN condition for SQL query

//$split_var is the previous array
$var_query = implode( "','" , $split_var);
//'12345','6789','101258'  i obtained this string

So now I try to create a query like this way

$tupla = Yii::$app->db->createCommand("
                SELECT * FROM oln, opt WHERE opt_cod = oln_opt_cod AND oln_cod IN ( :var_query) order by oln_cod ASC
            ")
            ->bindValue(':var_query' ,$var_query);
        $result = $tupla->queryAll();

It doesn't give me any error but the resulted query isn't what I'm expected. In fact I'll get that query:

SELECT * FROM oln, opt WHERE opt_cod = oln_opt_cod AND oln_cod IN ( '01Y0BIAN'',''05C2LARG'',''0661NO00') order by oln_cod ASC

The problem is in the IN condition and I don't know Why it added another '.

I tried also this method but I don't know how to bind parameters in this way:

$query = (new \yii\db\Query())
         ->select('*')
         ->from('oln, opt')
         ->where('opt_cod = oln_opt_cod')  
         ->andwhere('in', 'oln_cod',[':oln_cod' => $var_query])
         ->addParams([':oln_cod' => $var_query])
        ->orderBy('oln_cod ASC')
        ->all();

Upvotes: 0

Views: 671

Answers (1)

rob006
rob006

Reputation: 22174

Another ' is added because of escaping. Since $var_query is actually a string, it will be treated as single string value and any ' will be escaped to prevent SQL injection. You're building your IN condition in wrong way, you should bind every ID in IN separately:

$tupla = Yii::$app->db->createCommand(
        "SELECT * FROM oln, opt WHERE opt_cod = oln_opt_cod"
        . " AND oln_cod IN (:var_query1, :var_query2, :var_query3) order by oln_cod ASC"
    )
    ->bindValue(':var_query1', 12345)
    ->bindValue(':var_query2', 6789)
    ->bindValue(':var_query3', 101258);

It probably will be easier to use foreach to bind all params. It is also much simpler with Yii wrapper, which has nice shortcut for building IN conditions:

$query = (new \yii\db\Query())
    ->select('*')
    ->from('oln, opt')
    ->where('opt_cod = oln_opt_cod')
    ->andwhere(['in', 'oln_cod', $split_var]) // $split_var is array of values
    ->orderBy('oln_cod ASC')
    ->all();

Upvotes: 1

Related Questions