0

I have been working on a database management page using PHP. Recently I finished the page, and was working on the security aspect, when I ran into a problem with my first SQL query. It was not prepared, and could have empty input. When I tried using the following code to prepare it, it gave an error.

$id_query = "SELECT sp.name as name, sp.sku_id as sku_id, sp.id as prod_id FROM shop_product as sp WHERE scanbarcode_barcode='?';";
$stmt1 = $conn->prepare($id_query);
$stmt1->bind_param("s", $barcode);
$stmt1->execute();
$result1 = $stmt1->get_result();
$stmt1->close();

As I understand, it was because it was not recognising the '?' as a place for parameter binding.
I then tried the following code:

if(isset($_GET["barcode"])) {
    $barcode = $_GET["barcode"];
}else{
    $barcode = "";
}
# array definition using $barcode
if(isset($_GET["barcode"])) {
    $id_query = "SELECT sp.name as name, sp.sku_id as sku_id, sp.id as prod_id FROM shop_product as sp WHERE scanbarcode_barcode=?;";
    $stmt1 = $conn->prepare($id_query);
    $stmt1->bind_param("s", $barcode);
}else{
    $id_query = "SELECT sp.name as name, sp.sku_id as sku_id, sp.id as prod_id FROM shop_product as sp WHERE scanbarcode_barcode='';";
    $stmt1 = $conn->prepare($id_query);
}
$stmt1->execute();
$result1 = $stmt1->get_result();
$stmt1->close();
$result1 = mysqli_fetch_assoc($result1);

And the page just doesn't load.
I am using a Xampp localhost Apache and MySQL server.
If I don't use the last line, $result1 = mysqli_fetch_assoc($result1);, it fails on the line after, as it can't interpret a mysqli_result as an array. (The next line is a while loop, not shown here, as it's irrelevant)
I have thought about just adding some checks (the only thing that is passed in is an EAN13 barcode, and using normal

$result1 = mysqli_query($conn, $id_query);
$result1 = mysqli_fetch_assoc($result1);

but would prefer to use prepared statements.
Note: If I print_r() the $result1 before using the mysqli_fetch_assoc() on it, I get ~960 rows. It worked fine and fast before I tried preparing the statement.
Minimal reproducible example as requested:

<form action="dash.php" method="get">
    Barcode: <input type="text" name="barcode">
    <input type="submit">
</form>
<?php # Gets info about the product
$conn = mysqli_connect($hostname, $username, $password, $database);
if(isset($_GET["barcode"])) {
    $barcode = $_GET["barcode"];
}else{
    $barcode = "";
}
if(isset($_GET["barcode"])) {
    $id_query = "SELECT sp.name as name, sp.sku_id as sku_id, sp.id as prod_id FROM shop_product as sp WHERE scanbarcode_barcode=?;";
    $stmt1 = $conn->prepare($id_query);
    $stmt1->bind_param("s", $barcode);
}else{
    $id_query = "SELECT sp.name as name, sp.sku_id as sku_id, sp.id as prod_id FROM shop_product as sp WHERE scanbarcode_barcode='';";
    $stmt1 = $conn->prepare($id_query);
}
$stmt1->execute();
$result1 = $stmt1->get_result();
$result1 = mysqli_fetch_assoc($result1);
var_dump($result1);
$stmt1->close();
PythonSnek
  • 542
  • 4
  • 21
  • Check your error logs. PHP is trying to tell you what's wrong. – John Conde Mar 07 '21 at 22:35
  • While it's not a requirement, I recommend you use OO syntax consistently, so it should be `$result1->fetch_assoc()`. – Barmar Mar 07 '21 at 22:36
  • Don't call `$stmt1->close()` before calling `mysqli_fetch_assoc()` – Barmar Mar 07 '21 at 22:36
  • Don't use quotes, the bind does this for strings `scanbarcode_barcode=?` – AbraCadaver Mar 07 '21 at 22:37
  • @AbraCadaver He's not using quotes in the 2nd version. – Barmar Mar 07 '21 at 22:37
  • By default the $barcode is equal to None, as the $_Get is empty on first loadup. The SQL gives an error if it sees `WHERE scancode_barcode=;`. – PythonSnek Mar 07 '21 at 22:37
  • Avoid reusing variable names, it gets confusing. Use `$row = mysqli_fetch_assoc($result1);` – Barmar Mar 07 '21 at 22:38
  • Is `scanbarcode_barcode=''` suppose to be empty, or anything? – user3783243 Mar 07 '21 at 22:38
  • @user3783243 if you don't enter a barcode into a searchbar, it will be empty. – PythonSnek Mar 07 '21 at 22:39
  • What error do you get when you run this code? What happens? Have you got mysqli error reporting enabled? [How to get the error message in MySQLi?](https://stackoverflow.com/a/22662582/1839439) – Dharman Mar 07 '21 at 22:39
  • 1
    If you're setting `$barcode = ''` when `$_GET['barcode']` isn't set, you don't need two versions of the query. – Barmar Mar 07 '21 at 22:39
  • 1
    You can go back to the original code, just don't put quotes around `?`. That was your original problem. – Barmar Mar 07 '21 at 22:40
  • @Barmar I get `$barcode = None` when it isn't set. and a warning that it's missing from get – PythonSnek Mar 07 '21 at 22:40
  • 1
    That's impossible. – Barmar Mar 07 '21 at 22:41
  • It sounds like the form sends `None` by default when the user doesn't scan a barcode. – Barmar Mar 07 '21 at 22:43
  • You mean it can't return `None` or an equivalent? I am more used to Python definitions, as PHP is my third programming language. Yes, that is the case(to your last comment) – PythonSnek Mar 07 '21 at 22:44
  • In PHP if you try to use an unset variable you get `null`. – Barmar Mar 07 '21 at 22:44
  • That is exactly what happens. my form is
    Barcode:
    – PythonSnek Mar 07 '21 at 22:45
  • 2
    If you leave it blank, `$_GET['barcode']` will be set to an empty string. – Barmar Mar 07 '21 at 22:46
  • Il be back in a sec from a pc where I can run the code. I don't have a server or php on this machine. – PythonSnek Mar 07 '21 at 22:47
  • Yes Im back, and I don't see any errors in the logs – PythonSnek Mar 07 '21 at 22:52
  • What is the output of `var_dump(mysqli_fetch_assoc($result1))`? Did you enable mysqli error reporting already? – Dharman Mar 07 '21 at 22:56
  • @Barmar I have tried putting the `fetch_assoc()` before the close, and it still doesn't work. – PythonSnek Mar 07 '21 at 22:57
  • @Dharman I have added `mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);` into my code. my error.log from the Apache server doesn't show any errors – PythonSnek Mar 07 '21 at 23:00
  • Did you check Apache error log or PHP error log? – Dharman Mar 07 '21 at 23:01
  • Also I cannot see the output, as the page doesn't load if I have the `fetch_assoc()` Yes, the Apache doesn't have any errors, and the php error log doesn't exist according to Xampp – PythonSnek Mar 07 '21 at 23:02
  • 1
    Please update the code in question and show us proper [MCVE]. I tried that same code on my machine and it works perfectly fine. – Dharman Mar 07 '21 at 23:03
  • @Dharman does your query have an empty input? because think that that is the culprit of all of these errors – PythonSnek Mar 07 '21 at 23:10
  • 1
    Yes, I tried all variants. I also tried the new code you posted in the question. After fixing the syntax typo with the missing semicolon everything worked fine. – Dharman Mar 07 '21 at 23:11
  • I would have originally done this whole project in python, but the database is only accessible from the inside, so I had to quickly learn php – PythonSnek Mar 07 '21 at 23:15
  • @Dharman here is the var_dump: array(3) { ["name"]=> string(25) "????????. ??????. 8-9 ???" ["sku_id"]=> int(29704) ["prod_id"]=> int(29694) } mysqli_result Object ( [current_field] => 0 [field_count] => 3 [lengths] => Array ( [0] => 25 [1] => 0 [2] => 0 ) [num_rows] => 980 [type] => 0 ) all it took was a couple of server restarts – PythonSnek Mar 07 '21 at 23:19

1 Answers1

0

I have figured it out. It was me being a little stupid and using a bunch of while loops where they ended up not being needed. The page wasn't loading because it was executing infinite while loops. Special thanks to Dharman and Barmar for giving me ideas.

PythonSnek
  • 542
  • 4
  • 21