How to do not add ' character when using parameters with sql query

Question

Sometimes when I used qa_db_query_sub() function to create a sql query, I like to use columns as parameters

 

    $sql = "select # from ^users";

    qa_db_query_sub( $sql, 'id' );

The problem here is Q2A adds ' character to parameters and make

 

    "select 'id' from db_users"

That is wrong. I don't want those characters. How could I get rid of them?

Answer 1

The # and $ placeholders are for parameters (data) to the query only.

If you know you want the id field, just put that in the query:

    $sql = "select id from ^users";

If you are taking user input then the best idea is to check for the field against a list of known values:

    if ($field == 'id' || $field == 'other') {
      $sql = "select `$field` from ^users";
    }

If you have a lot of fields it would be better to check the field matches a basic pattern:

    if (preg_match('/[A-Za-z0-9_]/', $field)) { ... }

Comments

the problem is when you use 'id' (or 'other') in this manner, in resulted query there are two ' character around the id (or other).

$sql = "select `$field` from ^users"; converts to  "select 'id' from ^users";

Those  single quotation marks shouldn't be there.

---

Did you type it in correctly? I was using backticks around $field, not single quotation marks: ` instead of '

You don't always need backticks, they are for when the field name is also a reserved word like `where` or `order`. If you are not using any special words as field names then you don't need to put those in.