Laravel DB::raw prevent SQL Injection without setBindings

Question

I have a dynamic query for filtering, and now I have a problem where I need to use DB::raw for ordering by

$this->query->orderBy(\DB::raw("POSITION(\"$value\" IN $column)", 'asc'))

But now here I have a SQL Injection protection issue, how can I prevent $value and $column from SQL Injection without using ->setBindings

I cant use ->setBindings 'cause I have dinamic numbers of filter columns depending on the table

Devon Bessemer · Accepted Answer · 2019-05-15 14:05:56Z

3

Prepared statements can't evaluate column names, so you need to use whitelisting to protect against injections into $column.

To protect against value, you can either force it to be an integer or float type, or use orderByRaw which accepts an array of parameters:

$this->query->orderByRaw("POSITION(? IN $column)", [$value])

answered May 15, 2019 at 14:05

Devon Bessemer

35.6k10 gold badges77 silver badges102 bronze badges

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

2 Comments

SpaceDogCS Over a year ago

actually I don't need protection to $column cause it's from an array that I create, it works perfectly, thank you

Bill Karwin Over a year ago

You might want to put $column in back-ticks just in case it matches a reserved word.

Jerodev · Accepted Answer · 2019-05-15 14:05:19Z

1

You could use orderByRaw which will accept an array of bindings.

$this->query->orderByRaw('POSITION(? IN ?) asc', [$value, $column]);

answered May 15, 2019 at 14:05

Jerodev

33.3k11 gold badges96 silver badges113 bronze badges

2 Comments

Devon Bessemer Over a year ago

You can't use parameters for column names.

Bill Karwin Over a year ago

This solution won't return an error, but it'll treat the second parameter as a literal string, not the name of a column. So it won't do what the OP wants.

Sandeep Sudhakaran · Accepted Answer · 2019-05-15 14:08:48Z

-1

Try the below,

$orderByQuery = "select * from table order by POSITION(:value IN :column) asc";
$this->query->select($orderByQuery, [
    "value" => $value,
    "column" => $column
])

answered May 15, 2019 at 14:08

Sandeep Sudhakaran

1,0982 gold badges10 silver badges23 bronze badges

Collectives™ on Stack Overflow

Laravel DB::raw prevent SQL Injection without setBindings

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related