From e171f6f34eeacf35cf7142b4788d43e7d0978546 Mon Sep 17 00:00:00 2001 From: canyonknight Date: Wed, 8 Aug 2012 18:09:51 -0400 Subject: Migrate all DB code to use PDO All DB code currently uses the quickly aging mysql_* functions. These functions are strongly discouraged and may eventually be deprecated. Transition all code to utilize the PDO data access abstraction layer. PDO allows for consistent query code across multiple databases. This could potentially allow for someone to use a database other than MySQL with minimal code changes. All functions and behaviors are reproduced as faithfully as possible with PDO equivalents and some changes in code. Signed-off-by: canyonknight Signed-off-by: Lukas Fleischer --- web/lib/aur.inc.php | 154 ++++++++++++++++++---------------------------------- 1 file changed, 53 insertions(+), 101 deletions(-) (limited to 'web/lib/aur.inc.php') diff --git a/web/lib/aur.inc.php b/web/lib/aur.inc.php index 6dcbb34..d26bdf2 100644 --- a/web/lib/aur.inc.php +++ b/web/lib/aur.inc.php @@ -30,14 +30,15 @@ function check_sid($dbh=NULL) { $dbh = db_connect(); } $q = "SELECT LastUpdateTS, UNIX_TIMESTAMP() FROM Sessions "; - $q.= "WHERE SessionID = '" . db_escape_string($_COOKIE["AURSID"]) . "'"; - $result = db_query($q, $dbh); - if (mysql_num_rows($result) == 0) { + $q.= "WHERE SessionID = " . $dbh->quote($_COOKIE["AURSID"]); + $result = $dbh->query($q); + $row = $result->fetch(PDO::FETCH_NUM); + + if (!$row[0]) { # Invalid SessionID - hacker alert! # $failed = 1; } else { - $row = mysql_fetch_row($result); $last_update = $row[0]; if ($last_update + $LOGIN_TIMEOUT <= $row[1]) { $failed = 2; @@ -68,8 +69,8 @@ function check_sid($dbh=NULL) { # overwritten. if ($last_update < time() + $LOGIN_TIMEOUT) { $q = "UPDATE Sessions SET LastUpdateTS = UNIX_TIMESTAMP() "; - $q.= "WHERE SessionID = '".db_escape_string($_COOKIE["AURSID"])."'"; - db_query($q, $dbh); + $q.= "WHERE SessionID = " . $dbh->quote($_COOKIE["AURSID"]); + $dbh->exec($q); } } } @@ -119,12 +120,12 @@ function username_from_id($id="", $dbh=NULL) { if(!$dbh) { $dbh = db_connect(); } - $q = "SELECT Username FROM Users WHERE ID = " . db_escape_string($id); - $result = db_query($q, $dbh); + $q = "SELECT Username FROM Users WHERE ID = " . $dbh->quote($id); + $result = $dbh->query($q); if (!$result) { return "None"; } - $row = mysql_fetch_row($result); + $row = $result->fetch(PDO::FETCH_NUM); return $row[0]; } @@ -142,12 +143,12 @@ function username_from_sid($sid="", $dbh=NULL) { $q = "SELECT Username "; $q.= "FROM Users, Sessions "; $q.= "WHERE Users.ID = Sessions.UsersID "; - $q.= "AND Sessions.SessionID = '" . db_escape_string($sid) . "'"; - $result = db_query($q, $dbh); + $q.= "AND Sessions.SessionID = " . $dbh->quote($sid); + $result = $dbh->query($q); if (!$result) { return ""; } - $row = mysql_fetch_row($result); + $row = $result->fetch(PDO::FETCH_NUM); return $row[0]; } @@ -164,12 +165,12 @@ function email_from_sid($sid="", $dbh=NULL) { $q = "SELECT Email "; $q.= "FROM Users, Sessions "; $q.= "WHERE Users.ID = Sessions.UsersID "; - $q.= "AND Sessions.SessionID = '" . db_escape_string($sid) . "'"; - $result = db_query($q, $dbh); + $q.= "AND Sessions.SessionID = " . $dbh->quote($sid); + $result = $dbh->query($q); if (!$result) { return ""; } - $row = mysql_fetch_row($result); + $row = $result->fetch(PDO::FETCH_NUM); return $row[0]; } @@ -188,12 +189,12 @@ function account_from_sid($sid="", $dbh=NULL) { $q.= "FROM Users, AccountTypes, Sessions "; $q.= "WHERE Users.ID = Sessions.UsersID "; $q.= "AND AccountTypes.ID = Users.AccountTypeID "; - $q.= "AND Sessions.SessionID = '" . db_escape_string($sid) . "'"; - $result = db_query($q, $dbh); + $q.= "AND Sessions.SessionID = " . $dbh->quote($sid); + $result = $dbh->query($q); if (!$result) { return ""; } - $row = mysql_fetch_row($result); + $row = $result->fetch(PDO::FETCH_NUM); return $row[0]; } @@ -210,12 +211,12 @@ function uid_from_sid($sid="", $dbh=NULL) { $q = "SELECT Users.ID "; $q.= "FROM Users, Sessions "; $q.= "WHERE Users.ID = Sessions.UsersID "; - $q.= "AND Sessions.SessionID = '" . db_escape_string($sid) . "'"; - $result = db_query($q, $dbh); + $q.= "AND Sessions.SessionID = " . $dbh->quote($sid); + $result = $dbh->query($q); if (!$result) { return 0; } - $row = mysql_fetch_row($result); + $row = $result->fetch(PDO::FETCH_NUM); return $row[0]; } @@ -223,66 +224,16 @@ function uid_from_sid($sid="", $dbh=NULL) { # connect to the database # function db_connect() { - $handle = mysql_connect(AUR_db_host, AUR_db_user, AUR_db_pass); - if (!$handle) { - die("Error connecting to AUR database: " . mysql_error()); - } - - mysql_select_db(AUR_db_name, $handle) or - die("Error selecting AUR database: " . mysql_error()); - - db_query("SET NAMES 'utf8' COLLATE 'utf8_general_ci';", $handle); - - return $handle; -} - -# Escape strings for SQL query usage. -# Wraps the database driver's provided method (for convenience and porting). -function db_escape_string($string) { - return mysql_real_escape_string($string); -} - -# Escape strings for usage in SQL LIKE operators. -function db_escape_like($string) { - return addcslashes(mysql_real_escape_string($string), '%_'); -} - -# disconnect from the database -# this won't normally be needed as PHP/reference counting will take care of -# closing the connection once it is no longer referenced -# -function db_disconnect($db_handle="") { - if ($db_handle) { - mysql_close($db_handle); - return TRUE; + try { + $dbh = new PDO(AUR_db_DSN_prefix . ":" . AUR_db_host . ";dbname=" . AUR_db_name, AUR_db_user, AUR_db_pass); } - return FALSE; -} - -# wrapper function around db_query in case we want to put -# query logging/debugging in. -# -function db_query($query="", $db_handle="") { - if (!$query) { - return FALSE; + catch (PDOException $e) { + echo "Error - Could not connect to AUR database: " . $e->getMessage(); } - if (!$db_handle) { - die("DB handle was not provided to db_query"); - } - - if (defined('SQL_DEBUG') && SQL_DEBUG == 1) { - $bt = debug_backtrace(); - error_log("DEBUG: ".$bt[0]['file'].":".$bt[0]['line']." query: $query\n"); - } + $dbh->exec("SET NAMES 'utf8' COLLATE 'utf8_general_ci';"); - $result = @mysql_query($query, $db_handle); - if (!$result) { - $bt = debug_backtrace(); - error_log("ERROR: near ".$bt[0]['file'].":".$bt[0]['line']." in query: $query\n -> ".mysql_error($db_handle)); - } - - return $result; + return $dbh; } # common header @@ -313,10 +264,13 @@ function can_submit_pkg($name="", $sid="", $dbh=NULL) { $dbh = db_connect(); } $q = "SELECT MaintainerUID "; - $q.= "FROM Packages WHERE Name = '".db_escape_string($name)."'"; - $result = db_query($q, $dbh); - if (mysql_num_rows($result) == 0) {return 1;} - $row = mysql_fetch_row($result); + $q.= "FROM Packages WHERE Name = " . $dbh->quote($name); + $result = $dbh->query($q); + $row = $result->fetch(PDO::FETCH_NUM); + + if (!$row[0]) { + return 1; + } $my_uid = uid_from_sid($sid, $dbh); if ($row[0] === NULL || $row[0] == $my_uid) { @@ -385,13 +339,12 @@ function uid_from_username($username="", $dbh=NULL) { if(!$dbh) { $dbh = db_connect(); } - $q = "SELECT ID FROM Users WHERE Username = '".db_escape_string($username) - ."'"; - $result = db_query($q, $dbh); + $q = "SELECT ID FROM Users WHERE Username = " . $dbh->quote($username); + $result = $dbh->query($q); if (!$result) { return "None"; } - $row = mysql_fetch_row($result); + $row = $result->fetch(PDO::FETCH_NUM); return $row[0]; } @@ -405,13 +358,12 @@ function uid_from_email($email="", $dbh=NULL) { if(!$dbh) { $dbh = db_connect(); } - $q = "SELECT ID FROM Users WHERE Email = '".db_escape_string($email) - ."'"; - $result = db_query($q, $dbh); + $q = "SELECT ID FROM Users WHERE Email = " . $dbh->quote($email); + $result = $dbh->query($q); if (!$result) { return "None"; } - $row = mysql_fetch_row($result); + $row = $result->fetch(PDO::FETCH_NUM); return $row[0]; } @@ -461,11 +413,11 @@ function get_salt($user_id, $dbh=NULL) { if(!$dbh) { $dbh = db_connect(); } - $salt_q = "SELECT Salt FROM Users WHERE ID = " . $user_id; - $result = db_query($salt_q, $dbh); + $q = "SELECT Salt FROM Users WHERE ID = " . $user_id; + $result = $dbh->query($q); if ($result) { - $salt_row = mysql_fetch_row($result); - return $salt_row[0]; + $row = $result->fetch(PDO::FETCH_NUM); + return $row[0]; } return; } @@ -476,9 +428,9 @@ function save_salt($user_id, $passwd, $dbh=NULL) { } $salt = generate_salt(); $hash = salted_hash($passwd, $salt); - $salting_q = "UPDATE Users SET Salt = '" . $salt . "', " . - "Passwd = '" . $hash . "' WHERE ID = " . $user_id; - return db_query($salting_q, $dbh); + $q = "UPDATE Users SET Salt = " . $dbh->quote($salt) . ", "; + $q.= "Passwd = " . $dbh->quote($hash) . " WHERE ID = " . $user_id; + $result = $dbh->exec($q); } function generate_salt() { @@ -519,21 +471,21 @@ function begin_atomic_commit($dbh=NULL) { if(!$dbh) { $dbh = db_connect(); } - db_query("BEGIN", $dbh); + $dbh->beginTransaction(); } function end_atomic_commit($dbh=NULL) { if(!$dbh) { $dbh = db_connect(); } - db_query("COMMIT", $dbh); + $dbh->commit(); } function last_insert_id($dbh=NULL) { if(!$dbh) { $dbh = db_connect(); } - return mysql_insert_id($dbh); + return $dbh->lastInsertId(); } function latest_pkgs($numpkgs, $dbh=NULL) { @@ -544,10 +496,10 @@ function latest_pkgs($numpkgs, $dbh=NULL) { $q = "SELECT * FROM Packages "; $q.= "ORDER BY SubmittedTS DESC "; $q.= "LIMIT " .intval($numpkgs); - $result = db_query($q, $dbh); + $result = $dbh->query($q); if ($result) { - while ($row = mysql_fetch_assoc($result)) { + while ($row = $result->fetch(PDO::FETCH_ASSOC)) { $packages[] = $row; } } -- cgit v1.2.3-70-g09d2