1

I have a PHP script that I use to run a query in mysql and export a file. I have the login credentials within the php file. It will be invoked by a user visiting the page and the file automatically downloads. I have read that you should write the credentials in another file and include it in the file. I don't know enough about php to know where to store this file securely and how to properly include it. I had a friend help me write the file.
The php script is as follows. 

<?php
header("Access-Control-Allow-Origin: *");
$db_user = "user"; //replace with your mysql username
$db_pass = "password"; //replace with your mysql password
$db_source = "database"; //replace with your database name
$mysqli = mysqli_connect("localhost", $db_user, $db_pass, $db_source);

if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
} else {
// place sql query between quotes for $sql -- do not end with ;
    $sql = " SQL Statement Here ";
    $res = mysqli_query($mysqli, $sql);
    $rows = array();
    if ($res) {
        while ($r = mysqli_fetch_assoc($res)) {
            $rows[] = $r;
        }
    } else {
        printf("Could not retrieve records: %s\n", mysqli_error($mysqli));
    }

    $return = array(
        $rows
    );

    array_to_csv_download($rows);

    mysqli_free_result($res);
    mysqli_close($mysqli);
    unset ($db_user, $db_pass, $db_source);
}


function array_to_csv_download($array, $filename = "export.csv", $delimiter=",") {
    header('Content-Type: application/csv');
    header('Content-Disposition: attachment; filename="'.$filename.'";');

    $f = fopen('php://output', 'w');

    foreach ($array as $line) {
        fputcsv($f, $line, $delimiter);
    }
}

?>
Improve this question

3 Answers 3

Reset to default
4

When putting PHP file on a properly set up server, the users will not be able to access the PHP source code. Therefore, if you trust the server, you can put the database credentials in the code without any problem.

However, there are a few other problems that you might encounter relating security:

1. When you put a website on a server, you must ensure that this server is secure. Someone from the outside should not be able to read the php source files from the server. Depending on your hosting provider, the server might be already all secured for you so you should not worry too much about that (as long as you keep to minimum the number of people having access to the server).

2. You're right that you need to put the credentials in an external file. However, this is not related to security on the server. It is useful when you are working on a project with multiple other programmers and you want to keep your database credentials private. Usually you want to store all the private data inside one file that you are not going to share with anyone else. For example, if you are using a version control system, such as git, you don't want to track this file and every programmer should have his own version of the file. This reduces the risk of widespread of sensitive information.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

This is sort of true. It's still better to keep it in a separate file outside of version control, so something like accidentally making your Github private for a little while doesn't expose it. See, for example, the .env file in Laravel.
@ceejayoz true! I'll add it to my answer. Thanks!
1

Even if you put it in an include they can still get the password by echoing it out so that won't work for you.

If you're sending an untrusted source a database password, you're doing it very wrong. What you probably want to do is turn that script in to a REST API and there are lot of tutorials on that. Then, they never get to see your password. All they would have to do is send a limited set of commands and your script would return the required data to them. The REST API acts kind of like a black box and the end users only have access to a limited set of functions.

Improve this answer

5 Comments

Could you please explain how anyone could "echo out" the password from a PHP file that they never get to see the source of?
If you have a file called connect.php for example with your database connection info in it and then you do include 'connect.php then all someone has to do is write echo $password; assuming that $password is a variable in connect.php
Yes, but this "someone" would have to have read and write access to the directory where the PHP source files are kept. As far as I know, a web user has no way of inserting PHP statements in a PHP file.
I am also interested in how this would occur, I have been reading that the php should not be visible to anyone, I just want to make certain that it isn't. Understanding how it would be accessed would help.
I was under the impression that you were sending someone the actual code. If you have a php file that only has <php echo "<h1>HELLO WORLD!</h1>"; ?> They're never going to see the PHP, only the rendered html. They never get to see the PHP.
0

If you have full server access or access to directories behind the WWW dir, write a file with the SQL credentials and store it behind the www directory, then give -rwx to everyone and +r to the Apache/Nginx user (usually www-data). Set the www-data (or the httpd server user) shell to /bin/false and secure root with a private key. Then include the file with the hard path. Also, never uses the root user on MySQL. Create a specific user and give it only needed permission.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.