0

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?

6
  • Use bactics around table and column names, not apostrophes. – Rick James Dec 2 '20 at 0:46
  • What client are you using to MySQL? Apache does not normally show any SQL. – Rick James Dec 2 '20 at 0:47
  • @RickJames if I use backticks here it would look funky since that's the way you define the code samples. Anyways, maybe I'm confused, in the server I do administer I have seen the apache logs showing SQL code, was it because the mariaDB engine does log SQL statements when executing them through phpmyadmin? – newbie Dec 2 '20 at 1:38
  • 1
    If you're not the server administrator, and that administrator knows what they is doing, there is no way for you to hide your activities on the server. Your only hope is that the admin is not worth their pay or they doesn't care. – mustaccio Dec 2 '20 at 3:00
  • 1
    An admin has significant more ability that reading apache logs to identify what's going on on their server. If you don't trust them, you shouldn't be using the server. – danblack Dec 2 '20 at 3:22
4

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).

1
  • This thoroughly answers one aspect of the question, but to add on that anyone with administrative access to the database can theoretically peek in on any query being run or, of course, any data being stored. So even though your query won't be (or shouldn't be) stored in the Apache error logs, you can't really protect yourself from a nosy database administrator. – ibennetch Dec 4 '20 at 4:00
1

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.

2
  • Hey @akina, thanks for your reply. I thought of that, of course, still the server administrator would be able to see that because the stored procedure would have to be created using mysql statements which would be shown in the logs if I'm not mistaken. – newbie Dec 2 '20 at 10:04
  • You want to hide the SQL text fully? From the server administrator? seriously? If so I can suggest the only way. Save SP creation script into the file. Move this file to some place available for acessing from shell/CLI. Execute it using CLI command SOURCE. Remove it. And hope that its copy is not stored in shadow server copy... – Akina Dec 2 '20 at 10:10
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.