Laravel: using WHERE CONCAT in query

Question

I am pretty new to laravel and facing a problem building a query using CONCAT:

#From input
$password = $request->password;
#sql statement
UserMainTbl::where('username', '=', $username)->whereRaw('hashkey', '=', CONCAT('admin_id'.$password))

Table: UserMainTbl
Field: username, hashkey, admin_id

Got error:

Call to undefined function App\Http\Controllers\Auth\CONCAT()

------
Update:
I change my code and manage to stop the above error. But getting new error.

->where('hashkey', '=', DB::raw('concat(admin_id,"$password")'))

Column not found: 1054 Unknown column 'password123' in 'where clause' (SQL: select * from user_main_tbl where username = xxx and hashkey = concat(admin_id,password123) limit 1)

------
Update [Solve]:
My bad on this one. It is just a simple string. Here is the solution for future reference, if any. Lol:

->where('hashkey', '=', DB::raw('concat(admin_id,"'.$password.'")'))

Can someone help to point how can I do it right?
Many thanks.

you don't have a function CONCAT. if you mean mysql CONCAT... then that CONCAT part should be included inside the single quote. — barudo
– barudo, Commented Nov 4, 2016 at 2:44
Thanks @barudo for your respond. I've update my question as per your feedback. And got another error. How can I make the password become string not as column? — Akira Kronic
– Akira Kronic, Commented Nov 4, 2016 at 3:01
you can write it as ->where('hashkey', '=', DB::raw("concat(admin_id,'$password')")) — jagzviruz
– jagzviruz, Commented Nov 4, 2016 at 6:44
Thanks for your respond @jagzviruz. I tried that, it only return as $password instead of data inside it. I have updated the solution. — Akira Kronic
– Akira Kronic, Commented Nov 4, 2016 at 8:49
Warning: Do NOT do it the way described in @jagzviruz's comment. Never use a variable directly in an SQL query that way or you will likely enable injection attacks, and even something unintentional like a single quote in the $password value will cause it to fail or cause possible data loss. Use proper parameter binding. — orrd
– orrd, Commented May 19, 2017 at 22:12

Saurabh · Accepted Answer · 2016-11-04 07:16:04Z

1

I know you have solved the issue, but your solution can open up possibilities for SQL Injection if you don't escape the user input. One way to tackle this is by adding a binding.

UserMainTbl::where('username', '=', $username)
  ->where('hashkey', '=',DB::raw('concat(admin_id,"?")'))
  ->addBinding($password);

https://laravel.com/docs/5.3/queries#raw-expressions

answered Nov 4, 2016 at 7:16

Saurabh

4694 silver badges5 bronze badges

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

Comments

Rav · Accepted Answer · 2016-11-04 08:04:14Z

1

Always be careful in using RAW method in query builder since it's prone to SQL injections. I suggest to separate the concatenation of the "admin_id" and "password" and use the standard WHERE method to avoid the problem.

answered Nov 4, 2016 at 8:04

Rav

1,3833 gold badges19 silver badges36 bronze badges

4 Comments

Akira Kronic Over a year ago

Thanks @ronald. Do you mean use the whereRaw ?

Akira Kronic Over a year ago

Yeah, I still using the DB::raw.

Rav Over a year ago

@Quzaimer: that's why I suggest on doing the concatenation on a separate variable and just use it on your where clause to avoid SQL injections. :)

Akira Kronic Over a year ago

Noted. Thank you for your suggestion @ronald

Collectives™ on Stack Overflow

Laravel: using WHERE CONCAT in query

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related