My question has to do with :
(a) Can this be made more secure?
(b) Are there best practices in relation to setting timezone?
(c) What best approach should be taken defining use of utf8?
$my_success = 0;
$my_message = '';
$my_id = 0;
try {
# declare & set variables using $my_env
list ($db_dbdriver, $db_hostname, $db_database, $db_username, $db_password) = Connection::dbConnect($my_env);
# set default timezone
date_default_timezone_set('America/Los_Angeles');
# create & set connection
$db_options = array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8');
$conn = new PDO($db_dbdriver . ":host=" . $db_hostname . ";dbname=" . $db_database . ";charset=utf8", $db_username, $db_password, $db_options);
$conn->exec("set names utf8");
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$conn->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
# Prepare an SQL statement
$my_sql = 'INSERT INTO Role (Role_Name, Role_CDate, Role_CBy, Role_EDate, Role_EBy)
VALUES (:Role_Name, NOW(), :Role_CBy, NOW(), :Role_EBy)';
$stmt = $conn->prepare($my_sql);
# bind parameters to prevent SQL Injection
$stmt->bindValue(':Role_Name', $role_name);
# defaults to Now()...
$stmt->bindValue(':Role_CBy', $role_cby);
# defaults to Now()...
$stmt->bindValue(':Role_EBy', $role_cby);
# execute the SQL statement
$stmt->execute();
# closes the cursor, enabling the statement to be executed again
$stmt->closeCursor();
# grab ID value of the new record
$my_id = $conn->lastInsertId();
# close & unset the connection
$stmt = null;
unset($stmt);
$conn = null;
unset($conn);
# Set success value to TRUE
$my_success = 1;
$my_message = 'success';
} catch(PDOException $e) {
# Catch error message
$error_message = $e->getMessage();
# Append error message to error log
# ...
# Set success value to FALSE & return an error message
$my_success = -1;
$my_message = $error_message;
$my_id = 0;
}