Take search term, check for it in database, returning matching term MySQL php

Question

this seems like such a simple error, but i'm completely stuck. Here is the code:

 if (!empty ($_POST['searchbird'])){
        $searchbird=rawurlencode(trim($_POST['searchbird']));
        $result = mysql_query("SELECT `sciname` FROM `wp_birds` WHERE $searchbird = `comname`");
    if (!$result) {
        echo 'Could not run query: ' . mysql_error();
        exit;
    }
    $searchterm = mysql_fetch_row($result);

the SQL database is setup Col 4 is the comname and col 5 is the sciname

and here is the error Could not run query: Unknown column 'this is the searchterm' in 'where clause'

You have your column name and the value you're searching for the wrong way round. — andrewsi, Apr 03 '13 at 14:24

score 2 · Accepted Answer · edited May 23 '17 at 10:25

2

This is your current query,

SELECT `sciname` FROM `wp_birds` WHERE $searchbird = `comname`

if the data type of comName is string, the value should be enclosed with single quotes since they are literals.

SELECT `sciname` FROM `wp_birds` WHERE  `comname` = '$searchbird'

As a sidenote, the query is vulnerable with SQL Injection if the value(s) of the variables came from the outside. Please take a look at the article below to learn how to prevent from it. By using PreparedStatements you can get rid of using single quotes around values.

How to prevent SQL injection in PHP?

edited May 23 '17 at 10:25

Community

1
1

answered Apr 03 '13 at 14:27

John Woo

258,903
69
498
492

Thanks for this mysql injection point, i'll research and stick that in there as well. This solved my problem however, now the $searchterm (after the mysql_fetch_row () variable is returning 'array' not the searched for term. – Jonathan Coffey Apr 03 '13 at 14:50
it returns a resource which you need to iterate, see here how, http://php.net/manual/en/function.mysql-fetch-row.php – John Woo Apr 03 '13 at 14:51
thank you so much for the help, I have so much to learn! – Jonathan Coffey Apr 03 '13 at 14:55

nicbou · Answer 2 · 2013-04-03T14:36:55.057

First of all, you are exposing yourself to SQL injection. Before doing anything else, you will need to cover this. The PDO library makes it very easy and safe.

Regarding your question, the final query is the following:

SELECT sciname FROM wp_birds WHERE this is the searchterm = 'comname'

What you are looking for is the following (note the quotation marks):

SELECT sciname FROM wp_birds WHERE comname = 'this is the searchterm'

But you should not do that, because you are still exposed to SQL injection.

Take search term, check for it in database, returning matching term MySQL php

2 Answers2