Laravel Query Builder Security

InfinityPaul
Apr 15 · 3 min read

“Never Trust A User Input” as it is widely said

SQL Injection

Most people ain’t aware that PDO which is what most modern PHP application use to talk to databases, don’t have a built in mechanism for escaping column names like they do the values.

for instance when you write the code below

User::where('firstname', 'infinitypaul')->first();

the ‘infinitypaul’ value is properly escaped and you safely pass user input into that of course, but the column name “firstname” there is no built in mechanism to escape the column name in PDO,so you never let your user dictate the column of your query without you verifying/sanitizing it first otherwise it is possible to have a SQL injection vulnerability in your application

public function welcome(Request $request){
User::where($request->firstname, 'infinitypaul')->orderBy($request->col)->first();
}

if a user enters something like this 1+ORDER+BY+10 or ->”%27))%23injectedSQL , or someone with ill intentions can add any malicious code they want there. the input might not make sense to you but Laravel will convert the input to an SQL query which might return an output that might give an hint to something on your database..

i guess you now asking what if i need the user to dictate the column name, wait, am almost there, the best practice as at the time of writing is whitelist on the server of allowed column check just to make sure what the user is sending across matches your whitelisted set of column names, that way you know you can only pass those values

public function welcome(Request $request){
$column_name = array('firstname', 'lastname', 'username');
if (!in_array($request->firstname, $column_name, true)){
//you can add whatever result here if not found
return
;
}
User::where($request->firstname, 'infinitypaul')->first();

}

the inarray keyword checks if what the user sent is in your whitelisted value in the array. and the true makes sure it is case sensitive

As short as the above will solve and prevent the database from being compromise, there are several ways in which you can prevent that, it depends on your use case.. easy as that right.. the most important factor is bringing this info to your knowledge

One of the thing i love about php/laravel is the up and running community .Laravel Stated it in red in their documentation just to make you are aint ignorant of the above info

Query Builder Security https://laravel.com/docs/master/queries#introduction

On a final note, you should either not allow your user control the column name at all or if you need to allow that, then you need to use a whitelist, cause if you fall vulnerable of that. it could be very disastrous,

Check your apps if you know you allowed user to control the column name and correct where necessary

InfinityPaul

Written by

Your Friendly Neighborhood Software Developer, CEH & Tech Evangelist