PHP SQL - left join + where + or

Question

I'm improving my search bars in a project's back-office.
I want to make a research in the protfolio table, joining the categorytable to include the category's names in the available keywords. Seems simple, but the only message I'll get is :

Warning: PDOStatement::execute(): SQLSTATE[42S22]: Column not found: 1054 Unknown column 'search_0' in 'where clause' in [...]

I am creating the SQL query dynamically before executing it, like this :

$keywords = explode(" ", $_POST['search']);
$query = "SELECT * FROM portfolio LEFT JOIN category
          ON portfolio.portfolio_category_id = category.category_id
          WHERE ";

$query_array = array();
for ($i = 0; $i < count($keywords); $i += 1) {
    $query .= "portfolio.portfolio_title LIKE :search_" . $i;
    $query .= " OR category.category_name LIKE search_" . $i;

    if ($i != (count($keywords) - 1)) {
        $query .= " OR ";
    }
    $query_array['search_' . $i] = "%" . $keywords[$i] . "%";
}

$list_portf = $bdd->prepare($query);
$list_portf->execute($query_array);

I'm not a crack in SQL I don't really understand where is the error (I didn't find an answer in the question I found). Thanks.

Can you `echo $query_array` before executing query? That should help us find error — KuKeC, Jan 16 '17 at 14:20
@KuKeC I missed a colon (the answer I got where right). Sorry ! I'm closing the question — AymDev, Jan 16 '17 at 14:23
I feel I must note that the search term from the user should never be inserted directly into a SQL query without filtering. This is the definition of a SQL injection vulnerability. Perhaps see: http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php — pseudocoder, Jan 16 '17 at 15:28
@pseudocoder my query is prepared before executing. First the query is created in the string `$query` while the keywords are put into an array `$query_array`. Then the 2 variables are sent to the methods `->prepare()` and `->execute()` (at the end). I don't see where am I vulnerable to SQL injections ? — AymDev, Jan 16 '17 at 15:40
@AymDev maybe I'm missing something, but it appears you are inserting values from the user (`$_POST['search']`) directly into the query. That is the key characteristic of an injection vulnerability, since the user values could be anything, including statement terminations followed by additional statements such as DML, updates/inserts/deletes, etc. — pseudocoder, Jan 18 '17 at 15:01
@pseudocoder [pdo::prepare - php.net](http://php.net/manual/en/pdo.prepare.php) - [Are PDO prepared statements sufficient to prevent SQL injection - Stack Overflow](http://stackoverflow.com/questions/134099/are-pdo-prepared-statements-sufficient-to-prevent-sql-injection) Please tell me how should I proceed because I really don't understand where does this fail. Or maybe you read something wrong ? I hope so, I don't want to rewrite a complete project :-) — AymDev, Jan 18 '17 at 15:44

score 1 · Accepted Answer · answered Jan 16 '17 at 14:20

1

seems you missed colon : before your second ref to search_

   " OR category.category_name LIKE :search_" . $i;

answered Jan 16 '17 at 14:20

ScaisEdge

131,976
10
91
107

Okay I'm closing the question. Really sorry ! – AymDev Jan 16 '17 at 14:22
@AymDev nothing to sorry ..everyone do mistake ... well if my answer is right just mark it as accepted .. – ScaisEdge Jan 16 '17 at 14:24
I apparently can't before 6 min (but I'll do). But my question won't help anybody, I flagged it myself as I can't delete it. – AymDev Jan 16 '17 at 14:27
2

A question is a question .. It 'hard to say if it can be useful or not. often the syntax errors are time expensive. .. And finding a solution is a nice convenience – ScaisEdge Jan 16 '17 at 14:32

PHP SQL - left join + where + or

1 Answers1