Daily Blog Post: July 24th, 2023

July 24th, 2023

Secure Database Connections: Connecting to MySQL Using PHP PDO

Welcome back to our SQL blog series! In the last post, we covered how to use prepared statements in SQL to help prevent SQL injection attacks. In this post, we will cover how to connect to a MySQL database using PHP.

Connecting to a MySQL Database

To connect to a MySQL database using PHP, you can use the PDO (PHP Data Objects) extension. Here's an example:

$host = 'localhost';
$dbname = 'mydatabase';
$username = 'myusername';
$password = 'mypassword';

$dsn = "mysql:host=$host;dbname=$dbname;charset=utf8mb4";

$options = [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES => false,
];

try {
    $pdo = new PDO($dsn, $username, $password, $options);
} catch (\PDOException $e) {
    throw new \PDOException($e->getMessage(), (int)$e->getCode());
}

In this example, we first define the host, database name, username, and password for our MySQL database. We then create a Data Source Name (DSN) string that contains the necessary information to connect to our database.

Next, we define some options for our PDO connection, including setting the error mode to throw exceptions, setting the default fetch mode to associative arrays, and disabling emulation of prepared statements.

Finally, we create a new PDO object with our DSN, username, password, and options, and catch any exceptions that may be thrown during the connection process.

Using the Connection

Once we have established a connection to our MySQL database, we can execute SQL queries using prepared statements. Here's an example:

$stmt = $pdo->prepare('SELECT * FROM mytable WHERE name = :name');
$stmt->execute(['name' => $name]);
$rows = $stmt->fetchAll();

In this example, we use the prepare() method to create a prepared statement that selects all rows from the "mytable" table where the "name" column matches the "name" parameter. We then use the execute() method to execute the prepared statement with the "name" parameter. Finally, we use the fetchAll() method to retrieve all rows returned by the query.

Conclusion

In this post, we covered how to connect to a MySQL database using PHP and the PDO extension. By establishing a secure connection to our database, we can execute SQL queries using prepared statements and prevent SQL injection attacks. In the next post, we will cover how to use PHP to create a simple web application that interacts with a MySQL database. Stay tuned!