PHP won't interface with MySQL in Apache on Raspberry Pi

Question

I installed MySql on my Raspberry Pi 2 Model B+ a few days ago to see if I could use it, PHP, phpmyadmin, and Apache to make an accessible database to organize and catalog books that are around the house. I have a table in a MySQL database set up as a prototype with three columns; Booknumber (set to auto-increment), title, and authorLastName. I'm trying to use a form to insert books into table beta, in database bookProof. Here's the code for the form:

<html>
<body>

<form action="catalog.php" method="POST">
<p>Book Title: <input type="text" name="title"></p>
<p>Author's Last Name: <input type="text name="authorlastname"></p>
</form>

</body>
</html>

Which links to "catalog.php", which is:

<?php

define('DB_NAME', 'bookProof');
define('DB_USER', 'root');
define('DB_PASSWORD', 'root');
define('DB_HOST', 'localhost');

$conn = new mysqli(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);

if ($conn->connect_error) {
    die("Could not connect: " . $conn->connect_error);
}

$value = $_POST["title"]
$value2 = $_POST["authorlastname"]


$sql = "INSERT INTO beta ('title', 'authorLastName') VALUES ('".$value."', '".$value2."')"

$query = mysqli_query($conn,$sql);

if ($conn->($sql) === TRUE) {
    echo "New entry completed successfully";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

$conn->close();
?>

When demoform.php is opened, it functions normally, but when the "Add Books" button is clicked, it goes to catalog.php as intended, but the catalog.php page is blank, the table is unchanged, and Google Chrome's "Inspect" tool gives the error:

POST http://192.168.254.11/Library/catalog.php 500 (Internal Server Error)     catalog.php:1

If anyone knows how to get the input to the database, please let me know.

Note: This is just a home system, so security is not a priority (I don't need SQL code injection protection).

this sounds like an Apache misconfiguration, code 500 is server side error. do you have any .htaccess rules that happen on the catalog.php page? — Elzo Valugi, Jul 19 '17 at 22:08
@mayur beldar I'm using a VNC connection to access my Raspberry Pi, so I had to type the entire code again instead of copy/pasting it over here. The code is correct on the actual server. — Rob, Jul 19 '17 at 22:39
@elzo Valugi I don't have any .htaccess rules on that page. Do I need to implement those for this page's functionality? If so, can you provide and resources as to how to do that? Thank you for your time. — Rob, Jul 19 '17 at 22:41
no you dont need that. log in and execute the script from command line /bin/php /path/to/Library/catalog.php. Also see if you have errors in /var/log/apache/error.log or /var/log/httpd/error.log (depends on OS) — Elzo Valugi, Jul 19 '17 at 22:46
**WARNING**: When using `mysqli` you should be using [parameterized queries](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and [`bind_param`](http://php.net/manual/en/mysqli-stmt.bind-param.php) to add user data to your query. **DO NOT** use string interpolation or concatenation to accomplish this because you have created a severe [SQL injection bug](http://bobby-tables.com/). **NEVER** put `$_POST`, `$_GET` or **any** user data directly into a query, it can be very harmful if someone seeks to exploit your mistake. — tadman, Jul 19 '17 at 22:57

Qirel · Answer 1 · 2017-07-19T22:53:05.570

Your note, "...security is not a priority (I don't need SQL code injection protection)" - you might think that, but you should do it anyways. Not only does it protect your database should your system be exposed (or made public at a later time), it will handle strings automatically for you, so that your query won't break if your strings have quotes ' in them.

One issue is that you're using singlequotes around column and table names. This should be backticks, or none at all. Then you were missing a semicolon ; after defining your $value, $value2 and $sql strings.

Then you're doing something a bit odd - which is also causing a parse-error (Had you enabled error-reporting and checked your logs, you'd see a "Parse error: syntax error, unexpected (" error in your logs), you're querying the table with mysqli_query(), but then you try to do it again - except you're trying to query on the querystring, and not the query method. Note the comments I've added in the code below.

// Don't use singlequotes ' for columns and table-names
// Use backticks ` - quotes are for strings 
$sql = "INSERT INTO beta (`title`, `authorLastName`) VALUES ('".$value."', '".$value2."')"; // You were also missing a semicolon here!

// $query = mysqli_query($conn,$sql); // Remove this line, as you're attempting to query it twice 

if ($conn->query($sql) === TRUE) { // You're missing the query() method here
    echo "New entry completed successfully";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

Using prepared statements won't be that much of a difference, and you really should do it. There's absolutely no reason to not use prepared statements! Look how little changes that have to be made!

$sql = "INSERT INTO beta (title, authorLastName) VALUES (?, ?)";

if ($stmt = $conn->prepare($sql)) { 
    $stmt->bind_param("ss", $value, $value2);
    $stmt->execute();
    $stmt->close(); 
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

You've also got some invalid HTML which would cause issues - the following line had a missing quote to close off the type attribute.

<input type="text" name="authorlastname">

I suggest you read the following documentation and articles

As a final note, you should check that the form was submitted and that it has values before inserting into the database. Also, using variable-names like $value and $value2 are not really descriptive - you should avoid it and use proper names for your variables.

For the sake of developing my knowledge, "There's absolutely no reason to not use prepared statements" -> There is. Using multiple named parameters is not possible. Also, I do find it hard to develop/debug/imporove a query which has named or unnamed parameters because I have to replace the text. With sqp variables (@) I can simply overwrite it in the query. — Jannick Breunis, Mar 02 '22 at 10:25

PHP won't interface with MySQL in Apache on Raspberry Pi

1 Answers1