Results of my php system only comes when adding space before input, and unsafe codes

Question

I'm working on a simple search engine for my website, I managed to make one with php and a MySQL database and it works but it will only show results if I first put a space and then the input, directly giving the input results in my own made message that their where no search results.

Example, above is how it works now and below is an example that it doesnt without a space work :

Image link where it becomes clear: https://i.stack.imgur.com/mD0rO.png

Here is my code:

<!DOCTYPE html>

<html>

<head>

    <title>Search</title>   

</head>

<body>

<center>

<form action='search.php' method='get'>

    <input type='text' name='k' placeholder='Search' size='50' />
    <input type='submit' value='Search'>

</form>

<?php 

    $k = $_GET['k'];
    $terms = explode(" ", $k);
    $query = "SELECT * FROM search_engine WHERE ";

    foreach ($terms as $each){

        $i++; 

        if ($i == 1) 
            $query .= "keywords LIKE '%$each$' ";
        else 
            $query .= "OR keywords LIKE '%$each%' ";

    } 

    // connect

    mysql_connect ("***", "***", "***") or die("Could not connect to database!");
    mysql_select_db ("***") or die("Could not find database!");

    $query = mysql_query($query);
    $numrows = mysql_num_rows($query);
    if ($numrows > 0) {

        while ($row = mysql_fetch_assoc($query)){

            $id = $row['id'];
            $title = $row['title'];
            $description = $row['description'];
            $keywords = $row['keywords'];
            $link = $row['link'];

            echo "<br><br> Results: $title <br> Description: $description <br> Link: $link ";

        }

    }

    else 

        echo "<br><br>No results found for $k";

    //

?>

</center>

</body>

</html>

Does someone have an idea why it only works with the space before the input? Also I heard form some people that the codes are not save, can someone tell me if it is possible to make it saver without many changes to my code.

Thanks in advance!

This smells of sql injection you allow. Everyone can take over your system with that code. — arkascha, Jun 27 '15 at 11:54
Sure there is. You have to use the current db layer ("mysqli"), not the old, outdated and deprecated one you still use ("mysql") and you should read about "prepared statements" and "parameter binding". Sometimes you also read that you can use escaping functions to secure your code, but that is just the "poor mans version". Don't fall for it. Invest in a sounds security concept once instead of always having to fix stuff again and again. — arkascha, Jun 27 '15 at 11:57

Jocke Med Kniven · Accepted Answer · 2015-06-27T12:26:51.820

0

You should probably clean up your code a little bit, and secure it against injections and whatnot.

But these changes to your code should get the desired results:

<?php 

$results = '';

if(!empty($_GET['k'])) {

    $search = trim($_GET['k']);

    $sql = explode(" ", $search);
    $sql = "keywords LIKE '%" . implode("%' OR keywords LIKE '%", $sql) . "%'";
    $sql = "SELECT * FROM search_engine WHERE " . $sql;

    mysql_connect ("***", "***", "***") or die("Could not connect to database!");
    mysql_select_db ("***") or die("Could not find database!");

    $query = mysql_query($sql);
    $numrows = mysql_num_rows($query);

    if ($numrows > 0) {

        while ($row = mysql_fetch_assoc($query)){

            $id = $row['id'];
            $title = $row['title'];
            $description = $row['description'];
            $keywords = $row['keywords'];
            $link = $row['link'];

            $results .= "<br><br> Results: {$title} <br> Description: {$description} <br> Link: {$link}";

        }

    }

    else {

        $results = "<br><br>No results found for {$search}";
    }
}

?><!DOCTYPE html>
<html>
<head>
    <title>Search</title>   
</head>
<body>
<center>
<form action='search.php' method='get'>
    <input type='text' name='k' placeholder='Search' size='50' />
    <input type='submit' value='Search'>
</form>
<?php echo $results; ?>
</center>
</body>
</html>

edited Jun 27 '15 at 12:26

answered Jun 27 '15 at 11:56

Jocke Med Kniven

3,909
1
22
23

I tried this but it only makes it worse, it gives the message that their are no results al the time – JohanStaart Jun 27 '15 at 12:03
Try it again now. Found a typo. – Jocke Med Kniven Jun 27 '15 at 12:03
This time it displays the results all the time, even without putting input – JohanStaart Jun 27 '15 at 12:06
But when I just type social without a space it does work and only show the results for social – JohanStaart Jun 27 '15 at 12:07
I've completely rewritten the code to make it somewhat better (still more to do). – Jocke Med Kniven Jun 27 '15 at 12:18
I really apreaciate your help! Sadly does the new code not work, it says that their are no results for social. – JohanStaart Jun 27 '15 at 12:24
Again I found a typo. It's really hard giving you code that I don't get to test. – Jocke Med Kniven Jun 27 '15 at 12:27
I totally understand, and I'm really greatfull that you help me anyways! And you know what? It works now! Now I'm going to try to learn more about prepared statements and parameter binding so I hope I can make my code saver. Many thanks! – JohanStaart Jun 27 '15 at 12:31

score 0 · Answer 2 · edited May 23 '17 at 12:30

An array index starts at zero, when you explode social you get:

[0 => 'social']

Exploding social preceded with a space gives you:

[
    0 => '',
    1 => 'social'
]

In your loop you are testing $i == 1, so of course it only works in the second instance.

Now, this is the wrong way to go about this anyway.

Your code is susceptible to SQL injection. See: http://php.net/manual/en/security.database.sql-injection.php and https://stackoverflow.com/a/60496/388566

Rather than building a query like this, use prepared statements, and regex, like so:

$stmt = $pdo->prepare('SELECT * FROM search_engine WHERE keywords REGEXP :keywords');
$stmt->execute(array('keywords' => 'social|something|something-else|etc'));

Ensure you properly validate your keywords:

$keywords = trim($_GET['k'];
if (!preg_match('/^[\w\s]*$/', $keywords)) {
    //validation failed, do no proceed
}

$keywords = implode('|', explode(' ', $keywords)));

This mysql injection etc are all new for me I worked with PHP but never in combination with a database so I'm trying to figure it all out now, thanks for your reaction — JohanStaart, Jun 27 '15 at 12:26

Results of my php system only comes when adding space before input, and unsafe codes

2 Answers2