Problem with Yii2 PDO Statement Postgres Query

Question

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

It looks like the array values are already wrapped in single quotes and your implodeit wrapping them again in single quotes — RiggsFolly
– RiggsFolly, Commented Oct 5, 2018 at 10:35
If you can guarantee that the values will always be pre-wrapped in quotes, you could just do $var_query = implode( "," , $split_var); — RiggsFolly
– RiggsFolly, Commented Oct 5, 2018 at 10:37

rob006 · Accepted Answer · 2018-10-05 11:12:22Z

1

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

answered Oct 5, 2018 at 11:12

rob006

22.3k5 gold badges57 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

law_81 Over a year ago

Thank you for responding me, using Yii wrapper with the array like in your example i'm open to Sql Injection?

rob006 Over a year ago

No, Yii uses param binding and PDO under the hood, it just saves you binding every param manually.

Moeez Over a year ago

I am using MSSQL which is not in my model so how can I pass the connection to (new \yii\db\Query())?

Collectives™ on Stack Overflow

Problem with Yii2 PDO Statement Postgres Query

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related