Is orderBy() method in Laravel provides protection against sql injection?
Ex:
$column = "SUBSTRING_INDEX(material_type, '\\\', -1)";
$sort = 'desc';
DB::table('students')->orderBy(DB::raw($column), $sort)->get();
Asked
Active
Viewed 1,321 times
1
Someshwer Bandapally
- 141
- 1
- 11
-
`DB::table('students')->orderby($column, $sort);` try this – Alexander Villalobos Oct 05 '18 at 13:34
-
I am changing query here DB::table('students')->orderby(DB::raw($column), $sort)->get(); – Someshwer Bandapally Oct 05 '18 at 13:39
-
you cant do it via `eloquent` or need `query builder` – Alexander Villalobos Oct 05 '18 at 13:44
-
Eloquent uses parameter binding behind the scene, which safely escapes any input used in your query. – Alexander Villalobos Oct 05 '18 at 13:47
-
Hey @Alexander Villalobos, Yes you are right! both Eloquent and Query builder provides protection against sql injection. But I want to make sure that larave orderBy() method gives protection against SQL injection or not. Since I heard some where Laravel provides protection against sql injection when only we use where() method and not for orderBy() and limit() methods etc... – Someshwer Bandapally Oct 05 '18 at 14:32
3 Answers
3
->orderBy(DB::raw($column), $sort) is not secure, raw expressions are always vulnerable to SQL injections.
You can use ->orderBy($column, $sort), but I wouldn't recommend it.
Laravel does not use parameter binding for column names because the underlying PDO doesn't support it (reference). Parameter bindings can only be used for values (where name = ?).
Laravel tries to prevent SQL injections through the column name (reference), but I wouldn't rely on that. I assume you have a list of allowed column names, so use it as a whitelist:
$columns = ['id', 'name', 'created_at', ...];
if(!in_array($column, $columns, true)) {
// Throw an exception or set $column to a secure default value.
}
Laravel uses asc as the default direction if the provided value is invalid (reference).
So I would say that you don't have to check $sort yourself.
Jonas Staudenmeir
- 24,815
- 6
- 63
- 109
-
Nice explanation! Well, actually my case is to have sub-query there at the place of $column. So DB::raw($column) came into existence. So query becomes like $column = 'Some sub-query' pretend $column = 'SUBSTRING_INDEX(material_type, '\\\', -1)' ->orderBy(DB::raw($column), $sort) – Someshwer Bandapally Oct 05 '18 at 15:59
-
-
-
Yes user can supply any value. Pretend $column some times can be 'id', some other time it can be 'created_at' and then next time it can be like "SUBSTRING_INDEX(material_type, '\\\', -1)". It depends on the user input. – Someshwer Bandapally Oct 05 '18 at 16:07
-
This is a *very* bad idea, you can't implement that securely. You have to find a different solution where you whitelist the allowed column names and functions. – Jonas Staudenmeir Oct 05 '18 at 16:11
-
I have not implemented that dude. Somebody else was implemented that but now I am pulling out my hair. Now I need to change the whole thing. – Someshwer Bandapally Oct 05 '18 at 16:13
-
0
In Short, YES.
Modify your code from
DB::table('students')->orderby(DB::raw($column), $sort);
to
DB::table('students')->orderBy($column, $sort);
Why?
The Laravel query builder uses PDO parameter binding to protect your application against SQL injection attacks. There is no need to clean strings being passed as bindings.
Gabriel
- 970
- 7
- 20
0
If you use DB::raw($text) then no. what is inside $text goes into the query as is.
As suggested, use:
DB::table('students')->orderby($column, $sort);
in this case, yes the orderBy() method in Laravel provides protection against sql injection since the variables will be passed as bindings.
PS: it's the same case for all methods of eloquent. if you use DB::raw() you need to be extra careful.
N69S
- 16,110
- 3
- 22
- 36
-
So ->orderby(DB::raw($column), $sort); also indirectly provides protection against sql injection. Since I have used DB::raw() inside orderBy() method. – Someshwer Bandapally Oct 05 '18 at 13:43
-