When automating database operations like applying patches through PHP scripts, developers often face the security warning from MySQL:

Warning: Using a password on the command line interface can be insecure

This occurs when credentials are passed directly in commands like:

system('mysql --user=dev_user --password=P@ssw0rd123 < patch.sql');

Command-line arguments are visible in:

• Process lists (ps/pstree commands)
• Shell history files
• System logs
• MySQL's general query log

Option 1: MySQL Configuration Files

Create a secure .my.cnf file:

[client]
user = dev_user
password = P@ssw0rd123
host = localhost

Then modify your PHP script:

system('mysql --defaults-extra-file=/path/to/.my.cnf < patch.sql');

Option 2: Environment Variables

Set credentials in environment variables (safer than command line):

putenv('MYSQL_USER=dev_user');
putenv('MYSQL_PWD=P@ssw0rd123');

system('mysql < patch.sql');

Option 3: MySQL Native Auth

Use PHP's MySQLi with prepared statements:

$mysqli = new mysqli('localhost', 'dev_user', 'P@ssw0rd123', 'db_name');
if ($mysqli->multi_query(file_get_contents('patch.sql'))) {
    do {
        if ($result = $mysqli->store_result()) {
            $result->free();
        }
    } while ($mysqli->more_results() && $mysqli->next_result());
}

File Permissions

For configuration files:

chmod 600 /path/to/.my.cnf
chown www-data:www-data /path/to/.my.cnf

Connection Security

Always use SSL for remote connections:

[client]
ssl-ca=/etc/mysql/ca-cert.pem
ssl-cert=/etc/mysql/client-cert.pem
ssl-key=/etc/mysql/client-key.pem

To suppress the warning (not recommended):

system('mysql --user=dev_user --password=P@ssw0rd123 < patch.sql 2>/dev/null');

Or redirect stderr in PHP:

system('mysql --user=dev_user --password=P@ssw0rd123 < patch.sql 2>&1');

---

When automating database operations through PHP scripts, many developers use the direct command-line approach:

// Insecure method triggering MySQL warning
system('mysql --user=dbuser --password=plaintextpass < patch.sql');

MySQL 5.6+ rightfully warns about this practice because:

• Passwords appear in process listings (ps aux)
• They're stored in shell history files
• Visible in error logs when commands fail

Option 1: Using MySQL Configuration Files

Create a protected .my.cnf file with restricted permissions (600):

[client]
user = dbuser
password = your_secure_password
host = localhost

Then modify your PHP script:

// Secure method using config file
system('mysql --defaults-extra-file=/path/to/.my.cnf dbname < patch.sql');

Option 2: Environment Variables

Set credentials before execution:

// Using environment variables
putenv('MYSQL_USER=dbuser');
putenv('MYSQL_PWD=securepass');
system('mysql dbname < patch.sql');

Option 3: MySQL Native Authentication

For PHP scripts, consider using MySQLi or PDO:

// Using MySQLi with prepared statements
$mysqli = new mysqli('localhost', 'dbuser', 'securepass', 'dbname');
$mysqli->multi_query(file_get_contents('patch.sql'));

To suppress the warning while keeping the insecure method (not recommended):

// Redirect stderr to suppress warning
system('mysql --user=xxx --password=xxx < patch.sql 2> /dev/null');

For production systems, consider these professional approaches:

// Using PHP's vault extension
$creds = vault_get('mysql/prod');
$mysqli = new mysqli(
    $creds['host'],
    $creds['username'],
    $creds['password'],
    $creds['database']
);

Or implement temporary credential generation:

// Using AWS IAM database authentication
$token = $rdsClient->generateAuthToken([
    'Hostname' => 'db-hostname',
    'Port' => 3306,
    'Username' => 'iam_user'
]);

$mysqli = new mysqli('db-hostname', 'iam_user', $token, 'dbname', 3306);