PDO populate autocomplete with wildcard

Question

I'm currently changing all my mysql code to pdo to make it injection proof, but I'm farely new to both. Now I got a problem I can't wrap my head around. I try to populate autocomplete for an input-field through ajax using the following code:

ajax

$('#input').autocomplete({
     source: function (request, response) {
          $.ajax({
               url: 'fc_autocomplete.php',
               type: 'POST',
               data: {
                    term: request.term,
                    column: searchColumn,
                    table: searchTable
               },
               dataType: 'json',
               success: function (data) {
                    response(data);
               },
               error: function (response) {
                    alert('failed task');
               }
          });
     },
     minLength: 0,
     autoFocus: true,
     select: function (event, ui) {
          value = ui.item.value;
          $('#input').val(value);
          $(this).blur();
     },
     close: function (event, ui) {
          if ($('#input').val()) {
               $('#input').val(value);
          };
     }
}).focus(function () {
     $(this).data("uiAutocomplete").search($(this).val());
});

php/pdo

include_once('connect-db.php');

$term = trim($_REQUEST['term']);
//$term = str_replace('*', '\%', $term);
//$term = "$term%";                   => First try
$column = $_REQUEST['column'];
$tb = $_REQUEST['table'];

$column2 = $column;
$stmt = $conn->prepare('SELECT '.$column.' FROM '.$tb.' WHERE :column LIKE :term ORDER BY :column2 ASC LIMIT 50');
$stmt->bindValue(':column', $column);
$stmt->bindValue(':term', $term."%");
$stmt->bindValue(':column2', $column2);
$stmt->execute();
//$stmt->execute(array(":column" => $column, ":term" => $term, ":column2" => $column2)); => First try

$response = array();
    
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
     $response[] = $row[$column];
};
    
echo json_encode($response);

The commented out two commands were my first try. The code works perfectly as long as the input is empty (It shows all rows of the db. But as soon as I start typing, the list disappears completely (as if there aren't any results found). As you can see, I want it to search instead of the pure $term, for the $term with the % wildcard at the end. Why doesn't this work, though?

Also, preferably when the user enters a "*" somewhere in the term, I would like it to be switched into a "%" so that he can also find results not knowing the exact name. Is this even possible with prepared statements? Or does that work with the first commented out line?

Thanks for everyone's help.

Your query still will not be injection proof since you're getting the column and table from the user and not checking it against valid values. Also, columns cannot be parameterized. — aynber, Sep 22 '21 at 13:34
@KenLee Not really, I tried what he's writing in the post and put the % in the execute array. But the list still disappears after I type in the first character. — iMorces, Sep 22 '21 at 13:37
@aynber Thanks for the info. I'm aware that the column and table are posted and therefore not bulletproof. But the values posted come from the html source code (dependend on what input they fill out). So I feel like for a company internal program where none of the colleagues really knows anything about computers it's good enough for now. About the not parameterized column: Can this be the problem? Because it still loads the entire table when "term" is empty. — iMorces, Sep 22 '21 at 13:41
It can be, because at the beginning it's comparing `'string' LIKE ''` instead of `column LIKE ''`. When term is not empty, it's comparing `'string' LIKE 'term'`, which obviously aren't alike. And it will be ordering by `'string2'` instead of an actual column — aynber, Sep 22 '21 at 13:44
"I'm currently changing all my code to make it injection proof" and "none of the colleagues really knows anything about computers it's good enough for now" sounds a bit inconsistent to my taste. — Your Common Sense, Sep 22 '21 at 13:53
@aynber What do you mean? If, lets say, $column would be filled with the posted value 'title' and the table is 'product' my query would be `SELECT title FROM product WHERE title LIKE xxx ORDER BY xxx ASC LIMIT 50` wouldn't it? Since `$column2 = $column`. Or am I missing something here? — iMorces, Sep 22 '21 at 13:55
Parameterizing columns means that the value you're passing in will be a string value and not a column name. So if $column is `title`, then the query will act like `SELECT title FROM product WHERE 'title' LIKE 'xxx' ORDER BY 'xxx' ASC LIMIT 50` instead of `SELECT title FROM product WHERE \`title\` LIKE 'xxx' ORDER BY \`xxx\` ASC LIMIT 50`. See https://stackoverflow.com/questions/182287/can-php-pdo-statements-accept-the-table-or-column-name-as-parameter for more information — aynber, Sep 22 '21 at 13:59
@aynber It works! Thank you so much. I'm trying to solve this for the last couple days and couldn't wrap my hand around it. Definitely have to look more into paramaterized statements. Stupid mistakes that happen when you're new at this but are supposed to know everything. — iMorces, Sep 22 '21 at 14:05

PDO populate autocomplete with wildcard

0 Answers0