Dynamic value in sql query using php

Question

I want to search a certain string in all the columns of different tables, so I am looping the query through every column name. but if i give it as dynamic value it does not seem to work. what is wrong?

<?php

$search = $_POST['search'];

$columns = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'feedback'";
$columns_result = $conn->query($columns);
$columns_array = array();
if (!$columns_result) {
    echo $conn->error;
} else {
    while ($row = $columns_result->fetch_assoc()) {
        //var_dump($row);
        //echo $row['COLUMN_NAME']."</br>";
        array_push($columns_array, $row['COLUMN_NAME']);
    }
}
var_dump($columns_array);
$row_result = array();
for ($i = 0; $i < count($columns_array); $i++) {
    echo $columns_array[$i] . "</br>";
    $name = "name";
    // $sql = 'SELECT * FROM feedback WHERE "'.$search.'" in ("'.$columns_array[$i].'")';
    $sql = 'SELECT * FROM feedback WHERE ' . $name . ' like "' . $search . '"';
    $result = $conn->query($sql);
    if (!$result) {
        echo "hi";
        echo $conn->error;
    } else {
        foreach ($result as $row) {
            array_push($row_result, $row);
            echo "hey";
        }
    }
}
var_dump($row_result);

I am getting the column names of the table and looping through them because I have so many other tables which I need to search that given string. I don't know if it is optimal I did not have any other solution in my mind. If someone can tell a good way I will try that.

A) What type of SQL is this (they are different on things like this)? And B) What tool/language are you using to execute it? If a client-language then please also provide the client-code lines — RBarryYoung, Feb 10 '20 at 16:20
@Dharman can you provide an alternate solution. im a beginner. — Manoj, Feb 10 '20 at 16:24
@Manoj I recommend reading [this](https://www.php.net/manual/en/pdo.prepared-statements.php) and implementing it in your code ASAP due to the security risks in your code. If you prefer mysqli then read [this](https://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php) instead — Alon Eitan, Feb 10 '20 at 16:25
Use WHERE IN in SQL and bind the value using prepared statements. — Dharman, Feb 10 '20 at 16:25
Where did you get an idea to do it like this from? Was it some bad tutorial? — Dharman, Feb 10 '20 at 16:29
yes i know how to use prepared statements. thanks for telling me. — Manoj, Feb 10 '20 at 16:29
no i just want to test my logic first, was not looking at security risks, i will keep that in mind. — Manoj, Feb 10 '20 at 16:29
can someone tell if it is a good logic for searching something in all tables in sql? — Manoj, Feb 10 '20 at 16:32
It's not good logic. See this https://stackoverflow.com/q/2514548/1839439 — Dharman, Feb 10 '20 at 16:34
You can also check out [fulltext index](https://stackoverflow.com/questions/3645746/what-is-a-fulltext-index-and-when-should-i-use-it) — Alon Eitan, Feb 10 '20 at 16:35
In my case, i have different tables with different columns and different names so i am extracting column names and searching through them individually. if i have an array of different number of column names, how can i include that in the query? — Manoj, Feb 10 '20 at 16:43

Carsten Massmann · Accepted Answer · 2020-02-11T19:50:43.983

It looks to me that you want to generate a where clause that looks at any available nvarchar column of your table for a possible match. Maybe something like the following is helpful to you?

I wrote the following with SQL-Server in mind since at the beginning the question wasn't clearly tagged as MySql. However, it turns out that with a few minor changes the query work for MySql too (nvarchar needs to become varchar):

$search='%';$tbl='feedback';
if (isset($_POST['search'])) $search = $_POST['search'];
$columns = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS 
            WHERE TABLE_NAME = '$tbl' AND DATA_TYPE ='nvarchar'";
$columns_result = $conn->query($columns);
$columns_array = array();
if(!$columns_result) print_r($conn->errorInfo());
else while ($row = $columns_result->fetch(PDO::FETCH_ASSOC)) 
   array_push($columns_array, "$row[COLUMN_NAME] LIKE ?");

$where = join("\n OR ",$columns_array);
$sth = $conn->prepare("SELECT * FROM $tbl WHERE $where");
for ($i=count($columns_array); $i;$i--) $sth->bindParam($i, $search);
$sth->execute();

$result = $sth->fetchAll(PDO::FETCH_ASSOC);

print_r($result);

The above is a revised version using prepared statements. I have now tested this latest version using PHP 7.2.12 and SQL-Server. It turned out that I had to rewrite my parameter binding part. Matching so many columns is not a very elegant way of doing queries anyway. But it has been a nice exercise.

Glad you liked my contribution. However, I would like to point out that this code is still wide open to injection. Maybe you should have a look at this in order to help you come up with a safer version: https://stackoverflow.com/questions/40336368/php-pdo-with-sql-server-and-prepared-statements — Carsten Massmann, Feb 10 '20 at 22:30
Please look again, I changed my solution and made it injection-safe. — Carsten Massmann, Feb 11 '20 at 06:56
Hey, thanks @Dharman! Yes, of course, the PDO error function is `PDO::errorInfo()`. I didn't run into an SQL error - that is why I didn't notice. — Carsten Massmann, Feb 11 '20 at 19:50

score 1 · Answer 2 · answered Feb 11 '20 at 13:02

It looks like you are using mysqli, so I wanted to give another way of doing it via mysqli.

It does more or less the same as cars10m solution.

$search = $_POST['search'];

$columns = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'feedback'";
$columns_result = $conn->query($columns)->fetch_all(MYSQLI_ASSOC);

// Here dynamically prepare WHERE with all the columns joined with OR
$sql = 'SELECT * FROM feedback WHERE ';
$arrayOfWHERE = [];
foreach($columns_result as $col){
    $arrayOfWHERE[] = '`'.$col['COLUMN_NAME'].'` LIKE ?';
}
$sql .= implode(' OR ', $arrayOfWHERE);

// prepare/bind/execute
$stmt = $conn->prepare($sql);
$stmt->bind_param(str_repeat("s", count($arrayOfWHERE)), ...array_fill(0, count($arrayOfWHERE), $search));
$stmt->execute();
$result = $stmt->get_result();
$row_result = $result->fetch_all(MYSQLI_ASSOC);

var_dump($row_result);

Of course this will search for this value in every column of the table. It doesn't consider data type. And as always I have to point out the using PDO is better than mysqli. If you can switch to PDO.

Dynamic value in sql query using php

2 Answers2