How can I make queries secret?

Question

I have a project whose activity I need to keep private, and I'm not the server administrator.

If my app updates a value for the user with id 1, it would probably say something like UPDATE 'users' SET 'Name' = 'George' WHERE 'Id' = 1; and that will show in the Apache log.

What can I do to avoid this?

Answer 1

The Apache logs show what the Apache Web Server has been asked to do.

It generally knows (nor cares) not a jot about SQL. Of any flavour.

Please, please, please tell me you're not submitting raw SQL statements to your web application through the URL and/or QueryString and executing them!

Obligatory XKCD Reference: Little Bobby Tables.

This is a huge Security Risk and, if Apache really is logging queries, including Personal Data into its own, largely unsecured, log file then you're heading into very dangerous waters with regards to Data Protection legislation (e.g. GDPR).

Answer 2

Move the logic to the server side.

Create stored procedure which performs needed action. Then call this procedure from your code. For example:

-- on the server
CREATE 
DEFINER = 'dbadmin@localhost'
PROCEDURE update_user_name (userid BIGINT, newname VARCHAR(255))
SQL SECURITY DEFINER
UPDATE users
SET name = newname
WHERE id = userid;

-- on the client
CALL update_user_name (1, 'George');

That's all. Apache logs will show only CALL statement.

Main improvements:

1. Client have no permissions to look at SP text - hence he cannot see what this SP is performed, what table is updated, what additional checking logic is realized in SP.

2. Client may have no permissions to access the data at all - even if imagine that he guesses what SQL is performed, the attempt to execute UPDATE query used in SP will cause "You have no permissions to access the table users". Because only 'dbadmin@localhost' has data access permissions, and SP is executed using this account whereas the query is executing using user account.

3. If you need to alter the logic (for example, add some restrictions or improvements) then you simply alter SP's text, and you do not need to renew client software on all client workstations.

Answer 3

You could run a PHP script, maybe turn what your doing into a web page.

The contains of the script does not get displayed in the apache logs.

It just contains the fact the script was run.

Inside the script it uses variables to represent the values.

Make sure your use prepare statements to prevent bad guys from injecting things into your DB.

"UPDATE 'users' SET 'Name' = 'George' WHERE 'Id' = 1;

This is NOT a complete program.

/* Prepared statement, stage 1: prepare */
if (!($stmt = $mysqli->prepare("UPDATE 'users' set 'name'=name where 'id'=id"))) {
    echo "Prepare failed: (" . $mysqli->errno . ") " . $mysqli->error;
}
?>
$id = 1;
if (!$stmt->bind_param("s", $name)) {
    echo "Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error;
}
if (!$stmt->bind_param("i", $id)) {
    echo "Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error;
}

if (!$stmt->execute()) {
    echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
}
?>

Of course you would have to setup a web browser GUI to get the name and id, but it would all be done inside PHP. None of the PHP activities are logged in apache just when the script runs.