In PHP, what are some effective ways to handle database queries and transactions to ensure data integrity and security?
To ensure data integrity and security when handling database queries and transactions in PHP, it is important to use prepared statements to prevent SQL injection attacks, validate user input before executing queries, and handle errors gracefully to prevent data corruption.
// Establish a database connection
$dsn = 'mysql:host=localhost;dbname=mydatabase';
$username = 'username';
$password = 'password';
$options = array(
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_EMULATE_PREPARES => false
);
try {
$pdo = new PDO($dsn, $username, $password, $options);
} catch (PDOException $e) {
die('Database connection failed: ' . $e->getMessage());
}
// Prepare a SQL statement
$stmt = $pdo->prepare('SELECT * FROM users WHERE username = :username');
// Bind parameters and execute the statement
$username = 'john_doe';
$stmt->bindParam(':username', $username, PDO::PARAM_STR);
$stmt->execute();
// Fetch results
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($results as $row) {
echo $row['username'] . '<br>';
}
Related questions
-
file uploads PHP numerically numbered
-
HTTPS password transmission security risks
-
includes template system scalability