-1

The following SQL PHP query shows error, why ?

$plays = array("bionic","user54");
// Attempt select query execution
$sql = "SELECT * FROM tbl_product WHERE status='winning' AND agent IN (" . implode(",",  $plays) . ");";

Error

ERROR: Could not able to execute SELECT * FROM tbl_product WHERE status='winning' AND agent IN (bionic,user54);. Unknown column 'bionic' in 'where clause'

it says column 'bionic' in error, but bionic is not a column, its just a username in column agent

Instead of array if I use agent='bionic' it will show results.

nightowl
  • 25
  • 3

1 Answers1

0

You have to implode them with quotes around the individual values. The shortest way I can think of is:

$values = "'". implode("','", $plays)."'"

$sql = "SELECT * FROM tbl_product WHERE status='winning' AND agent IN (" . $values . ");";

Essentially you were generating sql like:

IN ( bionic, user54 )

Note the lack of quotation marks which means it's trying to use those names as column names.

You want to generate:

IN ('bionic','user54') for it to compare values.

But I will offer you a word of warning: You should always try to use an ORM or escape utilities to prevent SQL injection.

MiltoxBeyond
  • 2,683
  • 1
  • 13
  • 12
  • `escape utilities`? I think you meant `prepared statements` though. – DarkBee Oct 21 '22 at 05:39
  • Prepared statements are some of the methods. But at the very least they should use escape methods to sanitize the query. This code looks like beginner level programming so I didn't want to throw something too advanced at the OP – MiltoxBeyond Oct 21 '22 at 05:43
  • Escaping your strings is not the way to go though - [More information](https://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) – DarkBee Oct 21 '22 at 05:47
  • There are libraries to handle that all, frameworks as well, hence utilities. I just am warning them about the SQL Injection risk, not giving a full discourse on how to avoid it – MiltoxBeyond Oct 21 '22 at 05:51