sentence WHERE with a field concat

Question

I currently have this query.

SELECT
    CONCAT(`codeDoc`, '-', `prefixDoc`) AS `docId`,
    `descriptionDoc`,
    `statusDoc`
    FROM
    `BD`.`Table`
    WHERE (
       ...
    )

that query expects a request GET with a parameter called searchParam, I try this.

$searchParam = $_GET['searchParam'];
WHERE (
   `docId` LIKE '%".$searchParam."%'"
)

But it does not work like i was expected, and i undertand because docId is not a field in my table, it only is an alias (AS docId).

The question is how to achieve that ?

you can repeat the concat, `where CONCAT(`codeDoc`, '-', `prefixDoc`) = $your_param` — Nerea, Sep 17 '18 at 13:19
Your variable is missing it's `$` and it won't replace to its value while being held inside single quotes Instead: `"WHERE docid LIKE '%".$searchParam."%'"` BUT don't do that either as you are wide open for a SQL injection attack. Like.. .scary wide open. Instead [check out this very similar question for a better way of doing this](https://stackoverflow.com/questions/18527659/php-mysqli-prepared-statement-like) using prepared statements with [`mysqli::bind_param()`](http://php.net/manual/en/mysqli-stmt.bind-param.php) — JNevill, Sep 17 '18 at 13:19
@JNevill sorry, I copy and paste wrong, but that is not the error — jjoselon, Sep 17 '18 at 13:40
This should still 100% be rewritten to parameterize your query using `mysqli::bind_param`. If your `searchparam` from your user comes through as `%'); DROP TABLE BD.Table; --` you are sunk. — JNevill, Sep 17 '18 at 13:50

score 2 · Accepted Answer · answered Sep 17 '18 at 13:19

You cannot use the aliased expressions/fields in WHERE clause. Use the expression directly instead. Do the following:

$searchParam = $_GET['searchParam'];
WHERE (
   CONCAT(`codeDoc`, '-', `prefixDoc`) LIKE '%$searchParam%'
)

SideNote: You should switch to Prepared statements, to prevent SQL injection related issues.

sentence WHERE with a field concat

1 Answers1