PHP - Retrieve data using MySQL

Question

I got a problem retrieving data from a database. So I have a page that has a search box. A user types in an item number and gets redirected to a single item page with transferring that number using form . Then a script picks that number and shows only a result where that number equals a result number. PROBLEM: everything works well if that number consists of numbers only. If we add letters it goes to "PHP Fatal error: Call to a member function execute() on a non-object". In a database I set this be TEXT, not NUMBER.

Here is the code on a single item page:

So first it gets that number from top URL (part.php?partnumber=WB20K10023+):

$item_number = $_GET['item_number'];
$allItems = fetchAllItems($item_number);

Then it runs this function:

function fetchAllItems($item_number) {      
        global $mysqli,$db_table_prefix; 
        $stmt = $mysqli->prepare("SELECT            
            *
            FROM ".$db_table_prefix."items
            WHERE
            item_number = $item_number
            LIMIT 1
            ");
        $stmt->execute(); // <- This line shows in an error_log
        $stmt->bind_result($id);
        while ($stmt->fetch()){
            $row[] = array('id' => $id);
        }
        $stmt->close();
        return ($row);      
    }

Note that LIMIT without ORDER BY is pretty much meaningless. Other than that, you're simply missing a pair of inverted commas — Strawberry, Jul 23 '14 at 21:39
`partNumber = '$partNumber'`. Strings need to be enclosed in quotes. Since you are using `mysqli` and `prepare()` you really should be using a placeholder `partNumber = ?` and `$stmt->bind_param('s',$partNumber);`. — Sean, Jul 23 '14 at 21:43
Ref. http://stackoverflow.com/a/60496/2864740 - the code is not correctly using parameterized queries, the `$identifier` is interpolated and does *not* represent a placeholder. — user2864740, Jul 23 '14 at 21:43
@Art Adding the quotes results in poor code.. make sure to properly bind. — user2864740, Jul 23 '14 at 21:45

Nafis Abdullah Khan · Answer 1 · 2023-08-04T02:32:58.150

-1

If your partNumber field in the MySQL table is of string/text/varchar data type, then in the query string, you should write partNumber = '$partNumber' instead of writing partNumber = $partNumber.

Edit:

Need to use mysql_real_escape_string() to prevent sql injection. So it would be like this:

"SELECT * FROM ".$db_table_prefix."items
WHERE
item_number = '{mysql_real_escape_string($item_number)}'"

For MySQLi though, prepare function would do the escape work to prevent SQL injections. Only the inverted commas would help like this:

"SELECT * FROM ".$db_table_prefix."items
WHERE
item_number = '$item_number'"

edited Aug 04 '23 at 02:32

answered Jul 23 '14 at 23:09

Nafis Abdullah Khan

2,062
3
22
39

While that may "fix" the problem it is a poor approach that leads to SQL Injection. The OP should use placeholders, especially considering that the code *already* uses MySQLi. – user2864740 Jul 24 '14 at 03:39

PHP - Retrieve data using MySQL

1 Answers1