2

I've been trying to retrieve all site_keywords from the database, using where site_keywords in $keyword. But it doesn't show any error or output.

$user_query = $_REQUEST['user_query'];
$search=preg_split('/\s+/',$user_query);
$keywords = join(",",$search); 
$query = "select * from sites where site_keywords in ('%$keywords%') order by rank DESC ";

Can anyone help me with this?

zt1983811
  • 1,011
  • 3
  • 14
  • 34
user8214181
  • 45
  • 3
  • 2
    Your script is at risk of [SQL Injection Attack](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) Have a look at what happened to [Little Bobby Tables](http://bobby-tables.com/) Even [if you are escaping inputs, its not safe!](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) Use [prepared parameterized statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). – John Conde Jul 07 '17 at 11:41
  • please execute your query using `mysqli_query()`. – urfusion Jul 07 '17 at 11:42
  • `$_REQUEST['user_query']` is array ? – Jaydeep Mor Jul 07 '17 at 11:46

4 Answers4

3

There are some missing single quotes in the join (implode) function:

$user_query = $_REQUEST['user_query'];
$search=preg_split('/\s+/',$user_query);
$keywords = join("','",$search); 
$query = "select * from sites where site_keywords in ('%$keywords%') order by rank DESC ";

Query Without these quotes:

...where site_keywords in ('one,two,three')...

This will not produce any output or error as there are no valid results. The search query is treated as one long string.

Query With these quotes:

...where site_keywords in ('one','two','three')...

Here each query is correctly split in multiple search values.

Cagy79
  • 1,610
  • 1
  • 19
  • 25
  • Suppose user_query="got a doubt in this", then if we're using join function, $keywords becomes got','a','doubt','in','this -- then the query is not searching for got and this. If i'm appending ' before and after, then query is failed. Then how am I supposed to do this? – user8214181 Jul 10 '17 at 04:05
0
$query = "select * from sites where site_keywords in (".implode(",",$keywords).") order by rank DESC ";
Ninad Ramade
  • 256
  • 2
  • 9
0

IN does a literal search, to do a "fuzzy" search you need to do something like:

$query = "SELECT * FROM sites WHERE ".implode(" OR ", array_fill(0,count($search),"site_keywords LIKE ?"); 
 //Query looks like SELECT * FROM sites WHERE site_keywords LIKE ? OR site_keywords LIKE ?

$search = array_map(function ($v) { 
    return "%$v%";
},$search);

Now for the binding, it depends what you're using:

//MySQLi 
$stmt = mysqli_prepare($connection, $query);
mysqli_stmt_bind_param($stmt, array_fill(0,count($search),"s"), ...$search); //Note, you may bet some issues with references here. 
mysqli_stmt_execute($stmt);

//PDO
$stmt = $connection->prepare($query); 
for ($i = 0;$i< $search;$i++) {
    $stmt->bindValue($i+1,$search[$i]);
} 
$stmt->execute();
apokryfos
  • 38,771
  • 9
  • 70
  • 114
0

Always use prepared statements to prevent SQL injection. The following code can be used as a starting point to solve your problem (needs the PDO library, http://php.net/manual/en/book.pdo.php).

$user_query = $_REQUEST['user_query'];                      // you should better use $_GET or $_POST explicitly
$user_query = preg_replace('#\s{2,}#', ' ', $user_query);   // replace multiple spaces with a single space
$keywords = explode(' ', $user_query);                      // create the keywords array
$placeholders = array_fill(0, count($keywords), '?');       // create the placeholders array

$sql = 'SELECT *
        FROM sites
        WHERE site_keywords IN (' . implode(', ', $placeholders) . ')
        ORDER BY rank DESC';

$stmt = $db->prepare($sql);
$stmt->execute($keywords);
$result = $stmt->fetchAll();
gvre
  • 3
  • 1
  • 1