April 2011 - Aur-dev - lists.archlinux.org

[aur-dev] [PATCH 1/1] add SQL_DEBUG variable and database logging
by elij 27 Apr '11

27 Apr '11

add a hook to db_query to log all sql queries when SQL_DEBUG is set Additionally, provide better logging for sql error situations (provide backtrace as well as error message). --- web/lib/aur.inc | 12 ++++++++++++ web/lib/config.inc.proto | 4 ++++ 2 files changed, 16 insertions(+), 0 deletions(-) diff --git a/web/lib/aur.inc b/web/lib/aur.inc index 4e94340..2c6cd0d 100644 --- a/web/lib/aur.inc +++ b/web/lib/aur.inc @@ -238,10 +238,22 @@ function db_query($query="", $db_handle="") { if (!$query) { return FALSE; } + if (!$db_handle) { die("DB handle was not provided to db_query"); } + + if (SQL_DEBUG == 1) { + $bt = debug_backtrace(); + error_log("DEBUG: '".$bt[0]['file'].":".$bt[0]['line']." query: $query\n"); + } + $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; } diff --git a/web/lib/config.inc.proto b/web/lib/config.inc.proto index 80a7e54..43c64d2 100644 --- a/web/lib/config.inc.proto +++ b/web/lib/config.inc.proto @@ -20,6 +20,10 @@ define( "PASSWD_MAX_LEN", 128 ); # Default language for displayed messages in the web interface. define("DEFAULT_LANG", "en"); +# Enable debug sql output. This sends each query to error_log. Useful for +# development. Should not be enabled in production. Default to 0 (off). +define("SQL_DEBUG", 0); + # Languages we have translations for $SUPPORTED_LANGS = array( "ca" => "Català", -- 1.7.4.1

2 5

[aur-dev] [PATCH 1/1] create variable before referencing it with .=
by elij 27 Apr '11

27 Apr '11

fixes php notice level error: Undefined variable: whovoted in ../tu.php --- web/html/tu.php | 1 + 1 files changed, 1 insertions(+), 0 deletions(-) diff --git a/web/html/tu.php b/web/html/tu.php index dc1c5e3..c5cc36b 100644 --- a/web/html/tu.php +++ b/web/html/tu.php @@ -46,6 +46,7 @@ if ($atype == "Trusted User" OR $atype == "Developer") { ORDER BY Username"; $result = db_query($qwhoVoted,$dbh); if (mysql_num_rows($result) > 0) { + $whovoted = ''; while ($who = mysql_fetch_assoc($result)) { $whovoted.= '<a href="account.php?Action=AccountInfo&ID='.$who['UserID'].'">'.$who['Username'].'</a> '; } -- 1.7.4.1

2 1

[aur-dev] some notes from converting the aur from mysql to postgresql
by elij 27 Apr '11

27 Apr '11

While converting the aur codebase from mysql to postgresql (just for fun/to see if I could), I ran into a few mysql specific gotchas, and a few oddities. Here are some of my notes from the experience. * I ran into a few sql integer comparisons that are using string comparisons. postgres didn't like this, but mysql was fine with it ex: `$q .= "WHERE s.UsersId = '" . $userID . "' ";` * using the limit offset format of `"LIMIT x,y"` postgresql doesn't support this format, so I had to track each occurrence down and change it to `"Limit x OFFSET y"` * A few instances of `"LIMIT 0,10"`. A `"LIMIT 10"` would have been sufficient. Not sure why offset was specified in these few instances. * A pair of column names were reserved words. "user' and 'end' in the TU_VoteInfo table. I changed the column names to username and ending, to avoid issues and/or having to quote the name all the time. * postgresql table and column names are lowercase (when not quoted). This required me to either quote all instances of table and column names (ugh) or change the fetch_assoc php references to lowercase. I chose the latter, even though it was tedious to track down all instances and change them. * postgres has no mysql_insert_id counterpart. Instead I appended to the query "RETURNING ID" and then used pg_fetch_result to get the ID that was created. * postgres LIKE and ILIKE queries did full table scans, and was quite slow. I think mysql was caching these queries when I was doing my performance tests. The end result was postgres performing poorly in my load test for LIKE/ILIKE operations. So I created a postgres fulltext search column for the packages table, indexed it, defined triggers to keep it udpated, and converted to using postgres fulltext searching `"@@ to_tsquery('term')"`. The result was quite speedy and gave nice results. The syntax was a bit different for 'prefix searching' though, and the results were not the same as the LIKE/ILIKE queries, because fulltext uses stemming instead of partial string comparison. * There is quite a bit of sql code sprinkled all over the place. It might make sense to try and consolidate some of the sql to fewer files. * It might make sense to generalize the 'mysql_real_escape_string' instances to a wrapper 'db_escape' or something similar. This would could make things a bit cleaner, ease possible future porting to other databases (fewer things to track down and change individually). * postgres doesn't support `"UNIX_TIMESTAMP()"`. Instead of changing all instances of it, I defined a few convenience sql functions. -- -- helper functions to ease conversion from mysql -- from: http://janusz.slota.name/blog/2009/06/mysql-from_unixtime-and-unix_timestam… -- -- no params CREATE OR REPLACE FUNCTION unix_timestamp() RETURNS BIGINT AS ' SELECT EXTRACT(EPOCH FROM CURRENT_TIMESTAMP(0))::bigint AS result; ' LANGUAGE 'SQL'; -- timestamp without time zone (i.e. 1973-11-29 21:33:09) CREATE OR REPLACE FUNCTION unix_timestamp(TIMESTAMP) RETURNS BIGINT AS ' SELECT EXTRACT(EPOCH FROM $1)::bigint AS result; ' LANGUAGE 'SQL'; -- timestamp with time zone (i.e. 1973-11-29 21:33:09+01) CREATE OR REPLACE FUNCTION unix_timestamp(TIMESTAMP WITH TIME zone) RETURNS BIGINT AS ' SELECT EXTRACT(EPOCH FROM $1)::bigint AS result; ' LANGUAGE 'SQL'; I think that is it.

4 12

[aur-dev] [PATCH 1/1] use convenience wrapper for mysql_real_escape_string to aid database portability
by elij 26 Apr '11

26 Apr '11

when converting to postgres, each mysql_real_escape_string instance had to be changed, which was tedious. Centralizing the escape mechanism code would allow for much easier porting, in the same way that db_query provides a lightweight query abstraction. --- web/html/account.php | 2 +- web/html/addvote.php | 10 +++++----- web/html/logout.php | 2 +- web/html/passreset.php | 4 ++-- web/html/pkgsubmit.php | 36 ++++++++++++++++++------------------ web/html/voters.php | 2 +- web/lib/acctfuncs.inc | 26 +++++++++++++------------- web/lib/aur.inc | 30 ++++++++++++++++++------------ web/lib/aurjson.class.php | 8 ++++---- web/lib/pkgfuncs.inc | 12 ++++++------ web/lib/stats.inc | 2 +- web/template/pkg_comment_form.php | 2 +- 12 files changed, 71 insertions(+), 65 deletions(-) diff --git a/web/html/account.php b/web/html/account.php index afb0d7c..029ae79 100644 --- a/web/html/account.php +++ b/web/html/account.php @@ -111,7 +111,7 @@ if (isset($_COOKIE["AURSID"])) { $q.= "WHERE AccountTypes.ID = Users.AccountTypeID "; $q.= "AND Users.ID = Sessions.UsersID "; $q.= "AND Sessions.SessionID = '"; - $q.= mysql_real_escape_string($_COOKIE["AURSID"])."'"; + $q.= db_escape($_COOKIE["AURSID"])."'"; $result = db_query($q, $dbh); if (!mysql_num_rows($result)) { print __("Could not retrieve information for the specified user."); diff --git a/web/html/addvote.php b/web/html/addvote.php index 5936d56..e039d06 100644 --- a/web/html/addvote.php +++ b/web/html/addvote.php @@ -20,13 +20,13 @@ if ($atype == "Trusted User" OR $atype == "Developer") { $error = ""; if (!empty($_POST['user'])) { - $qcheck = "SELECT * FROM Users WHERE Username = '" . mysql_real_escape_string($_POST['user']) . "'"; + $qcheck = "SELECT * FROM Users WHERE Username = '" . db_escape($_POST['user']) . "'"; $check = mysql_num_rows(db_query($qcheck, $dbh)); if ($check == 0) { $error.= __("Username does not exist."); } else { - $qcheck = "SELECT * FROM TU_VoteInfo WHERE User = '" . mysql_real_escape_string($_POST['user']) . "'"; + $qcheck = "SELECT * FROM TU_VoteInfo WHERE User = '" . db_escape($_POST['user']) . "'"; $qcheck.= " AND End > UNIX_TIMESTAMP()"; $check = mysql_num_rows(db_query($qcheck, $dbh)); @@ -55,9 +55,9 @@ if ($atype == "Trusted User" OR $atype == "Developer") { if (!empty($_POST['addVote']) && empty($error)) { $q = "INSERT INTO TU_VoteInfo (Agenda, User, Submitted, End, SubmitterID) VALUES "; - $q.= "('" . mysql_real_escape_string($_POST['agenda']) . "', "; - $q.= "'" . mysql_real_escape_string($_POST['user']) . "', "; - $q.= "UNIX_TIMESTAMP(), UNIX_TIMESTAMP() + " . mysql_real_escape_string($len); + $q.= "('" . db_escape($_POST['agenda']) . "', "; + $q.= "'" . db_escape($_POST['user']) . "', "; + $q.= "UNIX_TIMESTAMP(), UNIX_TIMESTAMP() + " . db_escape($len); $q.= ", " . uid_from_sid($_COOKIE["AURSID"]) . ")"; db_query($q, $dbh); diff --git a/web/html/logout.php b/web/html/logout.php index 95cf460..ffde619 100644 --- a/web/html/logout.php +++ b/web/html/logout.php @@ -12,7 +12,7 @@ include_once("acctfuncs.inc"); # access AUR common functions if (isset($_COOKIE["AURSID"])) { $dbh = db_connect(); $q = "DELETE FROM Sessions WHERE SessionID = '"; - $q.= mysql_real_escape_string($_COOKIE["AURSID"]) . "'"; + $q.= db_escape($_COOKIE["AURSID"]) . "'"; db_query($q, $dbh); # setting expiration to 1 means '1 second after midnight January 1, 1970' setcookie("AURSID", "", 1, "/"); diff --git a/web/html/passreset.php b/web/html/passreset.php index 2c7801d..daba63e 100644 --- a/web/html/passreset.php +++ b/web/html/passreset.php @@ -40,8 +40,8 @@ if (isset($_GET['resetkey'], $_POST['email'], $_POST['password'], $_POST['confir Salt = '$salt', ResetKey = '' WHERE ResetKey != '' - AND ResetKey = '".mysql_real_escape_string($resetkey)."' - AND Email = '".mysql_real_escape_string($email)."'"; + AND ResetKey = '".db_escape($resetkey)."' + AND Email = '".db_escape($email)."'"; $result = db_query($q, $dbh); if (!mysql_affected_rows($dbh)) { $error = __('Invalid e-mail and reset key combination.'); diff --git a/web/html/pkgsubmit.php b/web/html/pkgsubmit.php index 5797626..177f5e1 100644 --- a/web/html/pkgsubmit.php +++ b/web/html/pkgsubmit.php @@ -294,7 +294,7 @@ if ($_COOKIE["AURSID"]): $dbh = db_connect(); - $q = "SELECT * FROM Packages WHERE Name = '" . mysql_real_escape_string($new_pkgbuild['pkgname']) . "'"; + $q = "SELECT * FROM Packages WHERE Name = '" . db_escape($new_pkgbuild['pkgname']) . "'"; $result = db_query($q, $dbh); $pdata = mysql_fetch_assoc($result); @@ -313,7 +313,7 @@ if ($_COOKIE["AURSID"]): # If a new category was chosen, change it to that if ($_POST['category'] > 1) { $q = sprintf( "UPDATE Packages SET CategoryID = %d WHERE ID = %d", - mysql_real_escape_string($_REQUEST['category']), + db_escape($_REQUEST['category']), $packageID); db_query($q, $dbh); @@ -321,12 +321,12 @@ if ($_COOKIE["AURSID"]): # Update package data $q = sprintf("UPDATE Packages SET ModifiedTS = UNIX_TIMESTAMP(), Name = '%s', Version = '%s-%s', License = '%s', Description = '%s', URL = '%s', OutOfDateTS = NULL, MaintainerUID = '%d' WHERE ID = %d", - mysql_real_escape_string($new_pkgbuild['pkgname']), - mysql_real_escape_string($new_pkgbuild['pkgver']), - mysql_real_escape_string($new_pkgbuild['pkgrel']), - mysql_real_escape_string($new_pkgbuild['license']), - mysql_real_escape_string($new_pkgbuild['pkgdesc']), - mysql_real_escape_string($new_pkgbuild['url']), + db_escape($new_pkgbuild['pkgname']), + db_escape($new_pkgbuild['pkgver']), + db_escape($new_pkgbuild['pkgrel']), + db_escape($new_pkgbuild['license']), + db_escape($new_pkgbuild['pkgdesc']), + db_escape($new_pkgbuild['url']), uid_from_sid($_COOKIE["AURSID"]), $packageID); @@ -337,13 +337,13 @@ if ($_COOKIE["AURSID"]): # This is a brand new package $q = sprintf("INSERT INTO Packages (Name, License, Version, CategoryID, Description, URL, SubmittedTS, ModifiedTS, SubmitterUID, MaintainerUID) VALUES ('%s', '%s', '%s-%s', %d, '%s', '%s', UNIX_TIMESTAMP(), UNIX_TIMESTAMP(), %d, %d)", - mysql_real_escape_string($new_pkgbuild['pkgname']), - mysql_real_escape_string($new_pkgbuild['license']), - mysql_real_escape_string($new_pkgbuild['pkgver']), - mysql_real_escape_string($new_pkgbuild['pkgrel']), - mysql_real_escape_string($_REQUEST['category']), - mysql_real_escape_string($new_pkgbuild['pkgdesc']), - mysql_real_escape_string($new_pkgbuild['url']), + db_escape($new_pkgbuild['pkgname']), + db_escape($new_pkgbuild['license']), + db_escape($new_pkgbuild['pkgver']), + db_escape($new_pkgbuild['pkgrel']), + db_escape($_REQUEST['category']), + db_escape($new_pkgbuild['pkgdesc']), + db_escape($new_pkgbuild['url']), $uid, $uid); @@ -364,8 +364,8 @@ if ($_COOKIE["AURSID"]): $q = sprintf("INSERT INTO PackageDepends (PackageID, DepName, DepCondition) VALUES (%d, '%s', '%s')", $packageID, - mysql_real_escape_string($deppkgname), - mysql_real_escape_string($depcondition)); + db_escape($deppkgname), + db_escape($depcondition)); db_query($q, $dbh); } @@ -375,7 +375,7 @@ if ($_COOKIE["AURSID"]): foreach ($sources as $src) { if ($src != "" ) { $q = "INSERT INTO PackageSources (PackageID, Source) VALUES ("; - $q .= $packageID . ", '" . mysql_real_escape_string($src) . "')"; + $q .= $packageID . ", '" . db_escape($src) . "')"; db_query($q, $dbh); } } diff --git a/web/html/voters.php b/web/html/voters.php index 6a16818..384a782 100644 --- a/web/html/voters.php +++ b/web/html/voters.php @@ -5,7 +5,7 @@ include('pkgfuncs.inc'); function getvotes($pkgid) { $dbh = db_connect(); - $pkgid = mysql_real_escape_string($pkgid); + $pkgid = db_escape($pkgid); $result = db_query("SELECT UsersID,Username FROM PackageVotes LEFT JOIN Users on (UsersID = ID) WHERE PackageID = $pkgid ORDER BY Username", $dbh); return $result; diff --git a/web/lib/acctfuncs.inc b/web/lib/acctfuncs.inc index f07c1fc..a0efa6e 100644 --- a/web/lib/acctfuncs.inc +++ b/web/lib/acctfuncs.inc @@ -225,7 +225,7 @@ function process_account_form($UTYPE,$TYPE,$A,$U="",$T="",$S="",$E="", # NOTE: a race condition exists here if we care... # $q = "SELECT COUNT(*) AS CNT FROM Users "; - $q.= "WHERE Username = '".mysql_real_escape_string($U)."'"; + $q.= "WHERE Username = '".db_escape($U)."'"; if ($TYPE == "edit") { $q.= " AND ID != ".intval($UID); } @@ -243,7 +243,7 @@ function process_account_form($UTYPE,$TYPE,$A,$U="",$T="",$S="",$E="", # NOTE: a race condition exists here if we care... # $q = "SELECT COUNT(*) AS CNT FROM Users "; - $q.= "WHERE Email = '".mysql_real_escape_string($E)."'"; + $q.= "WHERE Email = '".db_escape($E)."'"; if ($TYPE == "edit") { $q.= " AND ID != ".intval($UID); } @@ -265,7 +265,7 @@ function process_account_form($UTYPE,$TYPE,$A,$U="",$T="",$S="",$E="", # no errors, go ahead and create the unprivileged user $salt = generate_salt(); $P = salted_hash($P, $salt); - $escaped = array_map('mysql_real_escape_string', + $escaped = array_map('db_escape', array($U, $E, $P, $salt, $R, $L, $I)); $q = "INSERT INTO Users (" . "AccountTypeID, Suspended, Username, Email, Passwd, Salt" . @@ -289,7 +289,7 @@ function process_account_form($UTYPE,$TYPE,$A,$U="",$T="",$S="",$E="", # no errors, go ahead and modify the user account $q = "UPDATE Users SET "; - $q.= "Username = '".mysql_real_escape_string($U)."'"; + $q.= "Username = '".db_escape($U)."'"; if ($T) { $q.= ", AccountTypeID = ".intval($T); } @@ -298,15 +298,15 @@ function process_account_form($UTYPE,$TYPE,$A,$U="",$T="",$S="",$E="", } else { $q.= ", Suspended = 0"; } - $q.= ", Email = '".mysql_real_escape_string($E)."'"; + $q.= ", Email = '".db_escape($E)."'"; if ($P) { $salt = generate_salt(); $hash = salted_hash($P, $salt); $q .= ", Passwd = '$hash', Salt = '$salt'"; } - $q.= ", RealName = '".mysql_real_escape_string($R)."'"; - $q.= ", LangPreference = '".mysql_real_escape_string($L)."'"; - $q.= ", IRCNick = '".mysql_real_escape_string($I)."'"; + $q.= ", RealName = '".db_escape($R)."'"; + $q.= ", LangPreference = '".db_escape($L)."'"; + $q.= ", IRCNick = '".db_escape($I)."'"; $q.= " WHERE ID = ".intval($UID); $result = db_query($q, $dbh); if (!$result) { @@ -372,19 +372,19 @@ function search_results_page($UTYPE,$O=0,$SB="",$U="",$T="", $search_vars[] = "S"; } if ($U) { - $q.= "AND Username LIKE '%".mysql_real_escape_string($U)."%' "; + $q.= "AND Username LIKE '%".db_escape($U)."%' "; $search_vars[] = "U"; } if ($E) { - $q.= "AND Email LIKE '%".mysql_real_escape_string($E)."%' "; + $q.= "AND Email LIKE '%".db_escape($E)."%' "; $search_vars[] = "E"; } if ($R) { - $q.= "AND RealName LIKE '%".mysql_real_escape_string($R)."%' "; + $q.= "AND RealName LIKE '%".db_escape($R)."%' "; $search_vars[] = "R"; } if ($I) { - $q.= "AND IRCNick LIKE '%".mysql_real_escape_string($I)."%' "; + $q.= "AND IRCNick LIKE '%".db_escape($I)."%' "; $search_vars[] = "I"; } switch ($SB) { @@ -716,7 +716,7 @@ function valid_user( $user ) if ( $user ) { $dbh = db_connect(); $q = "SELECT ID FROM Users WHERE Username = '" - . mysql_real_escape_string($user). "'"; + . db_escape($user). "'"; $result = mysql_fetch_row(db_query($q, $dbh)); diff --git a/web/lib/aur.inc b/web/lib/aur.inc index 744b31e..f0a07a7 100644 --- a/web/lib/aur.inc +++ b/web/lib/aur.inc @@ -26,7 +26,7 @@ function check_sid() { # $dbh = db_connect(); $q = "SELECT LastUpdateTS, UNIX_TIMESTAMP() FROM Sessions "; - $q.= "WHERE SessionID = '" . mysql_real_escape_string($_COOKIE["AURSID"]) . "'"; + $q.= "WHERE SessionID = '" . db_escape($_COOKIE["AURSID"]) . "'"; $result = db_query($q, $dbh); if (mysql_num_rows($result) == 0) { # Invalid SessionID - hacker alert! @@ -50,7 +50,7 @@ function check_sid() { # session id timeout was reached and they must login again. # $q = "DELETE FROM Sessions WHERE SessionID = '"; - $q.= mysql_real_escape_string($_COOKIE["AURSID"]) . "'"; + $q.= db_escape($_COOKIE["AURSID"]) . "'"; db_query($q, $dbh); setcookie("AURSID", "", 1, "/"); @@ -66,7 +66,7 @@ function check_sid() { # overwritten. if ($last_update < time() + $LOGIN_TIMEOUT) { $q = "UPDATE Sessions SET LastUpdateTS = UNIX_TIMESTAMP() "; - $q.= "WHERE SessionID = '".mysql_real_escape_string($_COOKIE["AURSID"])."'"; + $q.= "WHERE SessionID = '".db_escape($_COOKIE["AURSID"])."'"; db_query($q, $dbh); } } @@ -110,7 +110,7 @@ function username_from_id($id="") { return ""; } $dbh = db_connect(); - $q = "SELECT Username FROM Users WHERE ID = " . mysql_real_escape_string($id); + $q = "SELECT Username FROM Users WHERE ID = " . db_escape($id); $result = db_query($q, $dbh); if (!$result) { return "None"; @@ -131,7 +131,7 @@ function username_from_sid($sid="") { $q = "SELECT Username "; $q.= "FROM Users, Sessions "; $q.= "WHERE Users.ID = Sessions.UsersID "; - $q.= "AND Sessions.SessionID = '" . mysql_real_escape_string($sid) . "'"; + $q.= "AND Sessions.SessionID = '" . db_escape($sid) . "'"; $result = db_query($q, $dbh); if (!$result) { return ""; @@ -151,7 +151,7 @@ function email_from_sid($sid="") { $q = "SELECT Email "; $q.= "FROM Users, Sessions "; $q.= "WHERE Users.ID = Sessions.UsersID "; - $q.= "AND Sessions.SessionID = '" . mysql_real_escape_string($sid) . "'"; + $q.= "AND Sessions.SessionID = '" . db_escape($sid) . "'"; $result = db_query($q, $dbh); if (!$result) { return ""; @@ -173,7 +173,7 @@ function account_from_sid($sid="") { $q.= "FROM Users, AccountTypes, Sessions "; $q.= "WHERE Users.ID = Sessions.UsersID "; $q.= "AND AccountTypes.ID = Users.AccountTypeID "; - $q.= "AND Sessions.SessionID = '" . mysql_real_escape_string($sid) . "'"; + $q.= "AND Sessions.SessionID = '" . db_escape($sid) . "'"; $result = db_query($q, $dbh); if (!$result) { return ""; @@ -193,7 +193,7 @@ function uid_from_sid($sid="") { $q = "SELECT Users.ID "; $q.= "FROM Users, Sessions "; $q.= "WHERE Users.ID = Sessions.UsersID "; - $q.= "AND Sessions.SessionID = '" . mysql_real_escape_string($sid) . "'"; + $q.= "AND Sessions.SessionID = '" . db_escape($sid) . "'"; $result = db_query($q, $dbh); if (!$result) { return 0; @@ -245,6 +245,12 @@ function db_query($query="", $db_handle="") { return $result; } +# database escape abstraction, to make porting +# to alternate databases slightly easier +function db_escape($unescaped) { + return mysql_real_escape_string($unescaped); +} + # set up the visitor's language # function set_lang() { @@ -272,7 +278,7 @@ function set_lang() { $q = "SELECT LangPreference FROM Users, Sessions "; $q.= "WHERE Users.ID = Sessions.UsersID "; $q.= "AND Sessions.SessionID = '"; - $q.= mysql_real_escape_string($_COOKIE["AURSID"])."'"; + $q.= db_escape($_COOKIE["AURSID"])."'"; $result = db_query($q, $dbh); if ($result) { @@ -332,7 +338,7 @@ function can_submit_pkg($name="", $sid="") { if (!$name || !$sid) {return 0;} $dbh = db_connect(); $q = "SELECT MaintainerUID "; - $q.= "FROM Packages WHERE Name = '".mysql_real_escape_string($name)."'"; + $q.= "FROM Packages WHERE Name = '".db_escape($name)."'"; $result = db_query($q, $dbh); if (mysql_num_rows($result) == 0) {return 1;} $row = mysql_fetch_row($result); @@ -403,7 +409,7 @@ function uid_from_username($username="") return ""; } $dbh = db_connect(); - $q = "SELECT ID FROM Users WHERE Username = '".mysql_real_escape_string($username) + $q = "SELECT ID FROM Users WHERE Username = '".db_escape($username) ."'"; $result = db_query($q, $dbh); if (!$result) { @@ -422,7 +428,7 @@ function uid_from_email($email="") return ""; } $dbh = db_connect(); - $q = "SELECT ID FROM Users WHERE Email = '".mysql_real_escape_string($email) + $q = "SELECT ID FROM Users WHERE Email = '".db_escape($email) ."'"; $result = db_query($q, $dbh); if (!$result) { diff --git a/web/lib/aurjson.class.php b/web/lib/aurjson.class.php index 50cf6d0..6719206 100644 --- a/web/lib/aurjson.class.php +++ b/web/lib/aurjson.class.php @@ -136,7 +136,7 @@ class AurJSON { if (is_numeric($arg)) { $id_args[] = intval($arg); } else { - $escaped = mysql_real_escape_string($arg, $this->dbh); + $escaped = db_escape($arg, $this->dbh); $name_args[] = "'" . $escaped . "'"; } } @@ -154,7 +154,7 @@ class AurJSON { return $this->json_error('Query arg too small'); } - $keyword_string = mysql_real_escape_string($keyword_string, $this->dbh); + $keyword_string = db_escape($keyword_string, $this->dbh); $keyword_string = addcslashes($keyword_string, '%_'); $where_condition = "( Name LIKE '%{$keyword_string}%' OR " . @@ -177,7 +177,7 @@ class AurJSON { } else { $where_condition = sprintf("Name=\"%s\"", - mysql_real_escape_string($pqdata, $this->dbh)); + db_escape($pqdata, $this->dbh)); } return $this->process_query('info', $where_condition); @@ -220,7 +220,7 @@ class AurJSON { * @return mixed Returns an array of value data containing the package data **/ private function msearch($maintainer) { - $maintainer = mysql_real_escape_string($maintainer, $this->dbh); + $maintainer = db_escape($maintainer, $this->dbh); $where_condition = "Users.Username = '{$maintainer}'"; diff --git a/web/lib/pkgfuncs.inc b/web/lib/pkgfuncs.inc index 25a09c4..f16d2be 100644 --- a/web/lib/pkgfuncs.inc +++ b/web/lib/pkgfuncs.inc @@ -94,7 +94,7 @@ function package_exists($name="") { if (!$name) {return NULL;} $dbh = db_connect(); $q = "SELECT ID FROM Packages "; - $q.= "WHERE Name = '".mysql_real_escape_string($name)."' "; + $q.= "WHERE Name = '".db_escape($name)."' "; $result = db_query($q, $dbh); if (!$result) {return NULL;} $row = mysql_fetch_row($result); @@ -127,7 +127,7 @@ function package_required($name="") { $dbh = db_connect(); $q = "SELECT p.Name, PackageID FROM PackageDepends pd "; $q.= "JOIN Packages p ON pd.PackageID = p.ID "; - $q.= "WHERE DepName = '".mysql_real_escape_string($name)."' "; + $q.= "WHERE DepName = '".db_escape($name)."' "; $q.= "ORDER BY p.Name"; $result = db_query($q, $dbh); if (!$result) {return array();} @@ -216,7 +216,7 @@ function pkgvotes_from_sid($sid="") { $q.= "FROM PackageVotes, Users, Sessions "; $q.= "WHERE Users.ID = Sessions.UsersID "; $q.= "AND Users.ID = PackageVotes.UsersID "; - $q.= "AND Sessions.SessionID = '".mysql_real_escape_string($sid)."'"; + $q.= "AND Sessions.SessionID = '".db_escape($sid)."'"; $result = db_query($q, $dbh); if ($result) { while ($row = mysql_fetch_row($result)) { @@ -237,7 +237,7 @@ function pkgnotify_from_sid($sid="") { $q.= "FROM CommentNotify, Users, Sessions "; $q.= "WHERE Users.ID = Sessions.UsersID "; $q.= "AND Users.ID = CommentNotify.UserID "; - $q.= "AND Sessions.SessionID = '".mysql_real_escape_string($sid)."'"; + $q.= "AND Sessions.SessionID = '".db_escape($sid)."'"; $result = db_query($q, $dbh); if ($result) { while ($row = mysql_fetch_row($result)) { @@ -267,7 +267,7 @@ function pkgname_from_id($pkgid=0) { # function pkgname_is_blacklisted($name) { $dbh = db_connect(); - $q = "SELECT COUNT(*) FROM PackageBlacklist WHERE Name = '" . mysql_real_escape_string($name) . "'"; + $q = "SELECT COUNT(*) FROM PackageBlacklist WHERE Name = '" . db_escape($name) . "'"; $result = db_query($q, $dbh); if (!$result) return false; @@ -432,7 +432,7 @@ function pkg_search_page($SID="") { } if (isset($_GET['K'])) { - $_GET['K'] = mysql_real_escape_string(trim($_GET['K'])); + $_GET['K'] = db_escape(trim($_GET['K'])); # Search by maintainer if (isset($_GET["SeB"]) && $_GET["SeB"] == "m") { diff --git a/web/lib/stats.inc b/web/lib/stats.inc index 75b2537..b825b2e 100644 --- a/web/lib/stats.inc +++ b/web/lib/stats.inc @@ -53,7 +53,7 @@ function updates_table($dbh) function user_table($user, $dbh) { global $apc_prefix; - $escuser = mysql_real_escape_string($user); + $escuser = db_escape($user); $base_q = "SELECT count(*) FROM Packages,Users WHERE Packages.MaintainerUID = Users.ID AND Users.Username='" . $escuser . "'"; $maintainer_unsupported_count = db_cache_value($base_q, $dbh, diff --git a/web/template/pkg_comment_form.php b/web/template/pkg_comment_form.php index e52c92d..eab32b4 100644 --- a/web/template/pkg_comment_form.php +++ b/web/template/pkg_comment_form.php @@ -7,7 +7,7 @@ if (isset($_REQUEST['comment'])) { $q = 'INSERT INTO PackageComments '; $q.= '(PackageID, UsersID, Comments, CommentTS) VALUES ('; $q.= intval($_REQUEST['ID']) . ', ' . uid_from_sid($_COOKIE['AURSID']) . ', '; - $q.= "'" . mysql_real_escape_string($_REQUEST['comment']) . "', "; + $q.= "'" . db_escape($_REQUEST['comment']) . "', "; $q.= 'UNIX_TIMESTAMP())'; db_query($q, $dbh); -- 1.7.4.1

3 3

[aur-dev] [PATCH 1/1] convert mysql specific offset/limit syntax to more standardized format
by elij 26 Apr '11

26 Apr '11

--- web/html/rss.php | 2 +- web/html/tu.php | 2 +- web/lib/acctfuncs.inc | 2 +- web/lib/pkgfuncs.inc | 2 +- 4 files changed, 4 insertions(+), 4 deletions(-) diff --git a/web/html/rss.php b/web/html/rss.php index cb0bf40..0547815 100644 --- a/web/html/rss.php +++ b/web/html/rss.php @@ -32,7 +32,7 @@ $rss->image = $image; $dbh = db_connect(); $q = "SELECT * FROM Packages "; $q.= "ORDER BY SubmittedTS DESC "; -$q.= "LIMIT 0 , 20"; +$q.= "LIMIT 20"; $result = db_query($q, $dbh); while ($row = mysql_fetch_assoc($result)) { diff --git a/web/html/tu.php b/web/html/tu.php index 941e6ed..dc1c5e3 100644 --- a/web/html/tu.php +++ b/web/html/tu.php @@ -119,7 +119,7 @@ if ($atype == "Trusted User" OR $atype == "Developer") { } $order = ($by == 'asc') ? 'ASC' : 'DESC'; - $lim = ($limit > 0) ? " LIMIT $off, $limit" : ""; + $lim = ($limit > 0) ? " LIMIT $limit OFFSET $off" : ""; $by_next = ($by == 'desc') ? 'asc' : 'desc'; $q = "SELECT * FROM TU_VoteInfo WHERE End > " . time() . " ORDER BY Submitted " . $order; diff --git a/web/lib/acctfuncs.inc b/web/lib/acctfuncs.inc index fe1cfb1..f07c1fc 100644 --- a/web/lib/acctfuncs.inc +++ b/web/lib/acctfuncs.inc @@ -405,7 +405,7 @@ function search_results_page($UTYPE,$O=0,$SB="",$U="",$T="", break; } $search_vars[] = "SB"; - $q.= "LIMIT ". $OFFSET . ", " . $HITS_PER_PAGE; + $q.= "LIMIT {$HITS_PER_PAGE} OFFSET {$OFFSET}"; $dbh = db_connect(); diff --git a/web/lib/pkgfuncs.inc b/web/lib/pkgfuncs.inc index 8f90d0e..25a09c4 100644 --- a/web/lib/pkgfuncs.inc +++ b/web/lib/pkgfuncs.inc @@ -501,7 +501,7 @@ function pkg_search_page($SID="") { break; } - $q_limit = "LIMIT ".$_GET["O"].", ".$_GET["PP"]; + $q_limit = "LIMIT {$_GET["PP"]} OFFSET {$_GET["O"]}"; $q = $q_select . $q_from . $q_from_extra . $q_where . $q_sort . $q_limit; $q_total = "SELECT COUNT(*) " . $q_from . $q_where; -- 1.7.4.1

3 3

[aur-dev] [PATCH] Add a .mailmap file
by Dan McGee 19 Apr '11

19 Apr '11

This takes `git shortlog -sen | wc -l` length from 69 to 56 authors for me, fixing a lot of the author fields that have snuck in over time, and allows credit to be given where due for some contributors that couldn't pick a single email address in the past. Signed-off-by: Dan McGee <dan(a)archlinux.org> --- .mailmap | 15 +++++++++++++++ 1 files changed, 15 insertions(+), 0 deletions(-) create mode 100644 .mailmap diff --git a/.mailmap b/.mailmap new file mode 100644 index 0000000..7f227a3 --- /dev/null +++ b/.mailmap @@ -0,0 +1,15 @@ +Andrea Scarpino <andrea(a)archlinux.org> <bash.lnx(a)gmail.com> +Cilyan Olowen <gaknar(a)gmail.com> <come(a)cilyan.local> +Denis Kobozev <d.v.kobozev(a)gmail.com> +eliott <eliott(a)cactuswax.net> <elij.mx(a)gmail.com> +Giovanni Scafora <giovanni(a)archlinux.org> <linuxmania(a)gmail.com> +Jason Chu <jason(a)archlinux.org> <jchu> +Laszlo Papp <djszapi(a)archlinux.us> <djszapi2(a)gmail.com> +Mod Gao <mod.gao(a)gmail.com> +Mod Gao <mod.gao(a)gmail.com> <mod.gao@gmail,com> +Paul Mattal <paul(a)mattal.com> <pjmattal> +Paul Mattal <paul(a)mattal.com> <pjmattal(a)brahms.out.elys.com> +Paul Mattal <paul(a)mattal.com> <pjmattal(a)pedantic.in.mattal.com> +Sergej Pupykin <sergej.pupykin(a)lx-ltd.ru> <ps(a)lx-ltd.ru> +Simo Leone <simo(a)archlinux.org> <simo> +Thayer Williams <thayer(a)archlinux.org> <thayerw(a)gmail.com> -- 1.7.4.4

2 1

[aur-dev] [PATCH 1/4] Don't allow dl() of json module
by Dan McGee 16 Apr '11

16 Apr '11

You need this enabled for the AUR, period. No need for this BS. Signed-off-by: Dan McGee <dan(a)archlinux.org> --- web/lib/aurjson.class.php | 4 ---- 1 files changed, 0 insertions(+), 4 deletions(-) diff --git a/web/lib/aurjson.class.php b/web/lib/aurjson.class.php index 3570909..bc26826 100644 --- a/web/lib/aurjson.class.php +++ b/web/lib/aurjson.class.php @@ -4,10 +4,6 @@ * * This file contains the AurRPC remote handling class **/ -if (!extension_loaded('json')) { - dl('json.so'); -} - include_once("aur.inc"); /** -- 1.7.4.4

7 20

[aur-dev] AUR new package notification
by Lukas Fleischer 13 Apr '11

13 Apr '11

I'm not subscribed to new package notifications but it seems like AUR this feature has been disabled for a while now. At least, I did neither find a cronjob running "newpackage-notify" nor the "tupkgs.conf" configuration file that is required to run this script. Browsing the aur-dev archives, I also found a mail [1] from 2008 reporting that the cronjob was obviously disabled. Do we still need this at all? Given that there were no complaints for more than two years and that there's a RSS feed [2] for new packages, I'd say we can drop the notification script as well as the field on the account page. Objections? [1] http://mailman.archlinux.org/pipermail/aur-dev/2008-June/000323.html [2] https://aur.archlinux.org/rss.php

3 3

[aur-dev] [PATCH] Fix performance issues with new PackageDepends lookups
by Dan McGee 12 Apr '11

12 Apr '11

We do a lookup by DepName in the package details view, but I made the silly mistake of forgetting this index addition in the upgrade steps. Signed-off-by: Dan McGee <dan(a)archlinux.org> --- Lukas- said missing index was the cause of the increased CPU usage/load on sigurd, it appears. I already created this index in production as I was the one who forgot it in my last set of patches, whoops! -Dan UPGRADING | 4 ++++ support/schema/aur-schema.sql | 1 + 2 files changed, 5 insertions(+), 0 deletions(-) diff --git a/UPGRADING b/UPGRADING index da85a8d..7878f63 100644 --- a/UPGRADING +++ b/UPGRADING @@ -7,6 +7,10 @@ From 1.8.2 to 1.9.0 1. Translation files are now gettext compatible and need to be compiled after each AUR upgrade by running `make install` in the "po/" directory. +2. Fix up issues with depends performance on large dataset. + +ALTER TABLE PackageDepends ADD INDEX (DepName); + From 1.8.1 to 1.8.2 ------------------- diff --git a/support/schema/aur-schema.sql b/support/schema/aur-schema.sql index d8c8560..da6a1f9 100644 --- a/support/schema/aur-schema.sql +++ b/support/schema/aur-schema.sql @@ -125,6 +125,7 @@ CREATE TABLE PackageDepends ( DepName VARCHAR(64) NOT NULL, DepCondition VARCHAR(20), INDEX (PackageID), + INDEX (DepName), FOREIGN KEY (PackageID) REFERENCES Packages(ID) ON DELETE CASCADE, ) ENGINE = InnoDB; -- 1.7.4.4

3 4

[aur-dev] Testing patches introducing database layout changes
by Lukas Fleischer 12 Apr '11

12 Apr '11

Hi, folks! Just as a recommendation to (regular) contributors: Any patches changing the database layout or the way information is stored in the database should be tested by rebuilding and reloading dummy data into the database in addition to usual testing before submitting them to aur-dev: ---- $ cd support/schema/ $ ./gendummydata.py dummy-data.sql && ./reloadtestdb.sh ---- This way, we can ensure that the patch does neither break the schema file nor the dummy data generation script. Maybe we could also make "./gendummydata.py" a bit more modular and run a slimmed down version to avoid delays here. I came up with this cause I just pushed two patches fixing regressions in "aur-schema.sql" and "gendummydata.py" once again. Please don't get the wrong end of the stick: This is no accusation but a simple proposition to improve our workflow. Affected patches are broken because those changes are hard to spot by just skimming through the source code and because I forgot to do these tests before signing off and pushing.

1 0