Can Read but can't Create/Update using PHP MySQLi

Question

I have managed to successfully executed a server-side database read (SELECT) request from a php script via ajax on the client side and through the local host using chromium on a Linux Virtual Machine. The data is returned as expected, no problems here.

By following the same process, I have tried making both CREATE and UPDATE requests, but nothing happens. Literally nothing from what I can see. No errors in the apache log. Database remains unchanged. Javascript does not continue to execute if I use a .then() after the AJAX call.

In the follow code below, I have boiled down the problem to just one line of code: $result = $conn->query($query); Oddly enough, if I set $result to just a string (e.g. $result = "hi";) and leave the $conn->query($query); on a seperate line, the code request is considered successful and javascript resume the .then() block.

Here is my PHP code.

<?php
    ini_set('display_errors', 'On');
    error_reporting(E_ALL);

    $executionStartTime = microtime(true);

    include("config.php");
    header('Content-Type: application/json; charset=UTF-8');

    $conn = new mysqli($cd_host, $cd_user, $cd_password, $cd_dbname, $cd_port, $cd_socket);

    if (mysqli_connect_errno()) {
        $output['status']['code'] = "300";
        $output['status']['name'] = "failure";
        $output['status']['description'] = "database unavailable";
        $output['status']['returnedIn'] = (microtime(true) - $executionStartTime) / 1000 . " ms";
        $output['data'] = [];

        mysqli_close($conn);
        echo json_encode($output);
        exit;
    }   

    $name = "test";
    $query = "INSERT INTO location (name) VALUES (" . $name . ")";

    $result = $conn->query($query);
    
    if (!$result) {
        $output['status']['code'] = "400";
        $output['status']['name'] = "executed";
        $output['status']['description'] = "query failed";  
        $output['data'] = [];

        mysqli_close($conn);
        echo json_encode($output); 
        exit;
    }

    $output['status']['code'] = "200";
    $output['status']['name'] = "ok";
    $output['status']['description'] = "success";
    $output['status']['returnedIn'] = (microtime(true) - $executionStartTime) / 1000 . " ms";
    $output['data'] = [];
    
    mysqli_close($conn);
    echo json_encode($output); 
?>

The last error in my apache2 error log is as follows and has since been fixed. This was the last useful message I received or could find:

[Sat Jan 09 19:08:53.816594 2021] [proxy_fcgi:error] [pid 6970] [client 127.0.0.1:39884] AH01071: Got error 'PHP message: PHP Notice:  Undefined variable: result in /var/www/html/php/insertLocation.php on line 27', referer: http://127.0.0.1/

I used the following commands in the terminal in case it was permissions related.

Add www-data to osboxes group sudo usermod -a -G www-data osboxes

Add osboxes to www-data group (Did it both ways for good measure). sudo usermod -a -G osboxes www-data

set osboxes (user) to own www-data sudo chown -R osboxes:www-data /var/www/html

sudo chgrp -R www-data /var/www/html

set access rights for apache sudo chmod 777 -R /var/www/html

Terminal output of ls -al /var/www/html

total 36
drwxrwsrwx 7 www-data www-data 4096 Dec 28 13:46 .
drwxr-xr-x 3 root     root     4096 Dec 22 10:11 ..
drwxrwsrwx 4 osboxes  www-data 4096 Dec 29 09:49 css
-rwxrwxrwx 1 osboxes  www-data 5599 Jan  5 18:11 index.html
drwxrwsrwx 4 osboxes  www-data 4096 Dec 28 13:46 js
drwxrwsrwx 3 osboxes  www-data 4096 Dec 28 13:52 libs
drwxrwsrwx 2 osboxes  www-data 4096 Jan  9 17:46 php
drwxrwsrwx 2 osboxes  www-data 4096 Dec 21 12:05 sql

There's a syntax error in your query. You need to wrap your value in quotes: `VALUES ('" . $name . "')";`. Note that building queries this way is susceptible to SQL Injection. You should move to prepared statements. See [this question](https://stackoverflow.com/q/60174/14853083) — Tangentially Perpendicular, Jan 10 '21 at 00:36
I can't believe this was the issue all along... The solution doesn't even seem correct to me, as it's turning the variable into a string? Eitherway, it works and I feel pretty stupid. Thank you so much.... Please put this up as the answer and I gladly mark it as being such. — Matminster, Jan 10 '21 at 00:42
Does this answer your question? [How to include a PHP variable inside a MySQL statement](https://stackoverflow.com/questions/7537377/how-to-include-a-php-variable-inside-a-mysql-statement) — Dharman, Jan 10 '21 at 14:02
It would have if I knew what I was looking for in the first instance. In hind site, the suggested thread is very useful and I'll probably use it in future. However, it might have taken me significant time to find it. Disagree that this post demonstrates lack of research effort. Sometimes you just need a helping hand to start asking the right questions. — Matminster, Jan 10 '21 at 16:54

score -1 · Accepted Answer · answered Jan 10 '21 at 00:55

In these lines you're building an SQL query:

$name = "test";
$query = "INSERT INTO location (name) VALUES (" . $name . ")";

The query you are creating will be this:

INSERT INTO location (name) VALUES (test)

This will fail because the SQL syntax requires quotes around your string data, so what you need to create is

INSERT INTO location (name) VALUES ('test')

And for that you need

$query = "INSERT INTO location (name) VALUES ('" . $name . "')";
                                              ^             ^
                                                Quotes here

This is a dangerous way to create SQL queries. If the data you receive contains a quote it will terminate the string and if you're lucky you'll just get a syntax error:

INSERT INTO location (name) VALUES ('O'Leary')

But suppose someone crafts a special string, like ','test1','test2','test3 Your query becomes

INSERT INTO location (name) VALUES (' ','test1','test2','test3')

And now you've created three additional rows. There are much worse things that can be done this way.

See this question for more details.

Thank you again. This not only solved my problem, but also addressed an immediate problem that was about to happen. — Matminster, Jan 10 '21 at 11:20

Can Read but can't Create/Update using PHP MySQLi

1 Answers1