PHP database security - design, connection and encryption

Design the database

The first step is usually to create a database, unless you are using a third-party database service. When a database is created, an owner is assigned to execute and create new statements. Normally, only the owner (or superuser) has the authority to perform arbitrary operations on objects in the database. If you want other users to use it, you must give them permission.

Applications should never use the database owner or superuser account to connect to the database, because these accounts can perform arbitrary operations, such as modifying the database structure (such as deleting a table) or clearing the entire database contents.

Separate database accounts should be created for each aspect of the program and given very limited permissions on database objects. Assign only the permissions required to complete their function, preventing the same user from being able to complete another user's tasks. In this way, even if an attacker exploits a program vulnerability to gain access to the database, he or she can only have the same scope of impact as the program.

Encourage users not to implement all transaction logic in web applications (i.e. user scripts). This is best done at the database level using views, triggers or rules. When the system is upgraded, new interfaces need to be opened for the database, and all database clients must be redone. In addition to this, triggers can handle fields transparently and automatically and provide useful information when debugging programs and tracing facts.

Connect to the database

Establishing the connection based on SSL encryption technology can increase the security of communication between the client and the server, or SSH can also be used to encrypt the connection between the client and the database. If these techniques are used, it will be difficult for an attacker to monitor server communications or obtain database information.

Encrypted storage model

SSL/SSH can protect the data exchanged between the client and the server, but SSL/SSH cannot protect the data already in the database. SSL is simply a protocol for encrypting network data streams.

If an attacker gains direct access to the database (bypassing the web server), sensitive data may be exposed or misused unless the database itself protects this information. Encrypting data within a database is an effective way to reduce such risks, but few databases provide these encryption features.

There is a simple solution to this problem, which is to create your own encryption mechanism and use it within the PHP program. PHP has several extension libraries that can do this, such as Mcrypt and Mhash, which contain a variety of encryption algorithms. The script encrypts the data before inserting it into the database and then decrypts it when extracting it later.

For some truly hidden data, if it does not need to exist in the form of plain text (that is, it does not need to be displayed), you can consider using a hash algorithm. The most common example of using a hashing algorithm is to store the MD5-encrypted hash of the password in the database to replace the original plaintext password.

Example #1 Hash encryption of password field

<?php
    // 存储密码散列
    $query = sprintf("INSERT INTO users(name,pwd) VALUES(&#39;%s&#39;,&#39;%s&#39;);",
    pg_escape_string($username), md5($password));
    $result = pg_query($connection, $query);
    // 发送请求来验证用户密码
    $query = sprintf("SELECT 1 FROM users WHERE name=&#39;%s&#39; AND pwd=&#39;%s&#39;;",
    pg_escape_string($username), md5($password));
    $result = pg_query($connection, $query);
    if (pg_num_rows($result) > 0) {
        echo &#39;Welcome, $username!&#39;;
    } else {
        echo &#39;Authentication failed for $username.&#39;;
    }
?>