linux - 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

question from:https://stackoverflow.com/questions/65649037/can-read-but-cant-create-update-using-php-mysqli

Answer 1

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.