public function testDbSqlDecodeInvalidValues()
{
// unquoted string
$this->assertEquals("abc", db_sql_decode("abc"));
// single quote
$this->assertEquals("", db_sql_decode("'"));
// missing start quote
$this->assertEquals("abc", db_sql_decode("abc'"));
// missing end quote
$this->assertEquals("abc", db_sql_decode("'abc"));
// containing unencoded values
$this->assertEquals("a&bc", db_sql_decode("a&bc"));
$this->assertEquals("a'bc", db_sql_decode("a'bc"));
$this->assertEquals("a\rbc", db_sql_decode("a\rbc"));
$this->assertEquals("a\nbc", db_sql_decode("a\nbc"));
$this->assertEquals("a& ;bc", db_sql_decode("a& ;bc"));
}
public static function findByReferenceId($tripId = '', $referenceId = '')
{
if (!$tripId || !$referenceId) {
return null;
}
$tripIdValue = db_sql_encode($tripId);
$referenceIdValue = db_sql_encode($referenceId);
$query = "" . "SELECT t2.commentId " . "FROM blogComment " . "INNER JOIN (" . "SELECT " . "MAX(t1.updated) AS updated, " . "t1.tripId as tripId, " . "t1.commentId as commentId " . "FROM blogComment " . "AS t1 " . "GROUP BY t1.tripId, t1.commentId " . "HAVING t1.tripId = {$tripIdValue} " . ") AS t2 " . "WHERE blogComment.tripId = t2.tripId " . "AND blogComment.commentId = t2.commentId " . "AND blogComment.updated = t2.updated " . "AND blogComment.deleted != 'Y' " . "AND blogComment.referenceId = {$referenceIdValue} " . "ORDER BY blogComment.created ASC ";
// print $query . "\n";
$result = mysql_query($query);
if (!$result) {
// Error executing the query
print $query . "<br/>";
print " --> error: " . mysql_error() . "<br/>\n";
return null;
}
if (mysql_num_rows($result) <= 0) {
// Comment does not exist
return null;
}
$list = array();
$count = 0;
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
$list[$count++] = db_sql_decode($line['commentId']);
}
return $list;
}
/**
* Extra test: password migration.
* Manually insert an old-style password hash in the database, then
* make sure that the password field in the database gets updated when
* the migration function is invoked.
* @depends testLegacyPassword
*/
public function testPasswordMigration()
{
global $testUserId1;
$password = '******';
$oldPasswordHash = '$0$6cc7c5a5a21978e5587a59186cadb5e3';
$object = new User($testUserId1);
$object->save();
// Update the database and check for match
$query = "UPDATE blogUser " . "SET password='******' " . "WHERE userId='{$testUserId1}'";
mysql_query($query);
$object->load($testUserId1);
$rows = $this->countTestRows();
$this->assertTrue($object->checkPassword($password));
$object->updatePasswordHash($password);
// make sure a new row has been inserted
$this->assertEquals($rows + 1, $this->countTestRows());
// Check that the password has been re-encoded in the
// in the database
$updated = $object->getUpdated();
$query = "SELECT password " . "FROM blogUser " . "WHERE userId='{$testUserId1}' " . "AND updated='{$updated}'";
// print "$query\n";
$result = mysql_query($query);
if ($result) {
$this->assertTrue(mysql_num_rows($result) === 1);
$line = mysql_fetch_array($result);
$newPasswordHash = db_sql_decode($line[0]);
$this->assertNotEquals($oldPasswordHash, $newPasswordHash);
} else {
$this->assertFalse(true, "Got error in mySQL query '{$query}'");
}
// After the password has been re-encoded, make sure it still matches
$this->assertTrue($object->checkPassword($password));
// Make sure repeated calls to updatePasswordHash succeed
$object->updatePasswordHash($password);
}
function db_sql_recode($value)
{
return db_sql_encode(db_sql_decode($value));
}
/**
* Load the object from the result of a MySQL query.
*/
protected function loadFromResult($result)
{
$line = mysql_fetch_array($result, MYSQL_ASSOC);
$this->tripId = db_sql_decode($line["tripId"]);
$this->mediaId = db_sql_decode($line["mediaId"]);
$this->created = db_sql_decode($line["created"]);
if (!isset($this->created) || $this->created === "") {
// For timestamp, default is null rather than empty string
$this->created = null;
}
$this->latestUpdated = db_sql_decode($line["updated"]);
if (!isset($this->latestUpdated) || $this->latestUpdated === "") {
// For timestamp, default is null rather than empty string
$this->latestUpdated = null;
}
$this->updated = null;
$this->type = db_sql_decode($line["type"]);
$this->caption = db_sql_decode($line["caption"]);
$this->timestamp = db_sql_decode($line["timestamp"]);
$this->location = db_sql_decode($line["location"]);
$this->width = db_sql_decode($line['width']);
$this->height = db_sql_decode($line['height']);
$this->deleted = db_sql_decode($line['deleted']);
$this->hash = db_sql_decode($line["hash"]);
$this->latestHash = $this->hash;
return true;
}
/**
* Load the object from the result of a MySQL query.
*/
protected function loadFromResult($result)
{
$line = mysql_fetch_array($result, MYSQL_ASSOC);
$this->tripId = db_sql_decode($line['tripId']);
$this->name = db_sql_decode($line["name"]);
$this->created = db_sql_decode($line["created"]);
if (!isset($this->created) || $this->created === "") {
// For timestamp, default is null rather than empty string
$this->created = null;
}
$this->latestUpdated = db_sql_decode($line["updated"]);
if (!isset($this->latestUpdated) || $this->latestUpdated === "") {
// For timestamp, default is null rather than empty string
$this->latestUpdated = null;
}
$this->updated = null;
$this->value = db_sql_decode($line["value"]);
$this->deleted = db_sql_decode($line["deleted"]);
$this->hash = db_sql_decode($line["hash"]);
$this->latestHash = $this->hash;
return true;
}
public static function getList($tripId, $referenceId)
{
$tripId = db_sql_encode($tripId);
$referenceId = db_sql_encode($referenceId);
$query = "" . "SELECT " . "blogFeedback.tripId, " . "blogFeedback.referenceId, " . "blogFeedback.userId, " . "blogFeedback.type " . "FROM blogFeedback " . "INNER JOIN (" . "SELECT " . "MAX(t1.updated) AS updated, " . "t1.tripId AS tripId, " . "t1.referenceId AS referenceId, " . "t1.userId AS userId " . "FROM blogFeedback " . "AS t1 " . "GROUP BY " . "t1.tripId, " . "t1.referenceId, " . "t1.userId " . "HAVING " . "t1.tripId={$tripId} " . "AND t1.referenceId={$referenceId} " . ") AS t2 " . "WHERE blogFeedback.tripId = t2.tripId " . "AND blogFeedback.referenceId = t2.referenceId " . "AND blogFeedback.userId = t2.userId " . "AND blogFeedback.updated = t2.updated " . "AND blogFeedback.deleted != 'Y' " . "ORDER BY blogFeedback.userId";
$result = mysql_query($query);
if (!$result) {
// Error executing the query
print $query . "<br/>";
print " --> error: " . mysql_error() . "<br/>\n";
return false;
}
$list = array();
if (mysql_num_rows($result) > 0) {
$count = 0;
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
$tripId = db_sql_decode($line["tripId"]);
$referenceId = db_sql_decode($line['referenceId']);
$userId = db_sql_decode($line['userId']);
$userName = '';
$type = db_sql_decode($line['type']);
$user = new User($userId);
if ($user) {
$userName = $user->getName();
}
$list[$count++] = array('tripId' => $tripId, 'referenceId' => $referenceId, 'userId' => $userId, 'userName' => $userName, 'type' => $type);
}
}
return $list;
}
/**
* Load the object from the result of a MySQL query.
*/
protected function loadFromResult($result)
{
$line = mysql_fetch_array($result, MYSQL_ASSOC);
$this->authId = db_sql_decode($line['authId']);
$this->created = db_sql_decode($line["created"]);
if (!isset($this->created) || $this->created === "") {
// For timestamp, default is null rather than empty string
$this->created = null;
}
$this->latestUpdated = db_sql_decode($line["updated"]);
if (!isset($this->latestUpdated) || $this->latestUpdated === "") {
// For timestamp, default is null rather than empty string
$this->latestUpdated = null;
}
$this->updated = null;
$this->userId = db_sql_decode($line['userId']);
$this->expiration = db_sql_decode($line["expiration"]);
return true;
}
/**
* Load the object from the result of a MySQL query.
*/
protected function loadFromResult($result)
{
$line = mysql_fetch_array($result, MYSQL_ASSOC);
$this->tripId = db_sql_decode($line["tripId"]);
$this->journalId = db_sql_decode($line["journalId"]);
$this->created = db_sql_decode($line["created"]);
if (!isset($this->created) || $this->created === "") {
// For timestamp, default is null rather than empty string
$this->created = null;
}
$this->latestUpdated = db_sql_decode($line["updated"]);
if (!isset($this->latestUpdated) || $this->latestUpdated === "") {
// For timestamp, default is null rather than empty string
$this->latestUpdated = null;
}
$this->updated = null;
$this->userId = db_sql_decode($line["userId"]);
$this->journalDate = db_sql_decode($line["journalDate"]);
$this->journalTitle = db_sql_decode($line["journalTitle"]);
$this->journalText = db_sql_decode($line["journalText"]);
$this->deleted = db_sql_decode($line['deleted']);
$this->hash = db_sql_decode($line["hash"]);
$this->latestHash = $this->hash;
return true;
}
static function findCurrentTrip()
{
$query = "" . "SELECT blogTrip.tripId, blogTrip.updated, blogTrip.startDate " . "FROM blogTrip " . "INNER JOIN (" . "SELECT " . "MAX(t1.updated) AS updated, " . "t1.tripId as tripId " . "FROM blogTrip " . "AS t1 " . "GROUP BY t1.tripId" . ") AS t2 " . "WHERE blogTrip.tripId = t2.tripId " . "AND blogTrip.updated = t2.updated " . "AND blogTrip.deleted != 'Y' " . "AND blogTrip.startDate < now() " . "ORDER BY blogTrip.startDate DESC " . "LIMIT 1";
$result = mysql_query($query);
if (!$result) {
// Error executing the query
print $query . "<br/>";
print " --> error: " . mysql_error() . "<br/>\n";
return false;
}
if (mysql_num_rows($result) <= 0) {
// Trip does not exist
return false;
}
$line = mysql_fetch_array($result, MYSQL_ASSOC);
$tripId = db_sql_decode($line["tripId"]);
return $tripId;
}
/**
* Get the value of the setting with the given name. An empty string
* is returned if the setting is not found, or no name is given.
*/
public static function get($name = '')
{
if (!isset($name) || $name === '') {
return '';
}
$query = "SELECT value FROM blogSetting " . "WHERE name=" . db_sql_encode($name);
$result = mysql_query($query);
if (!$result) {
// Error executing the query
print $query . "<br/>";
print " --> error: " . mysql_error() . "<br/>\n";
return '';
}
if (mysql_num_rows($result) <= 0) {
return '';
}
$line = mysql_fetch_array($result, MYSQL_ASSOC);
return db_sql_decode($line["value"]);
}
/**
* Load the user by email address. This will load the information about
* the user identified by the email address, if the email address is
* current for any user. Returns true on success, false if the email
* address is not the current address for any user.
* If there are multiple users that have this email address, the most
* recently updated user with this email address is returned.
*/
public function loadByEmail($email)
{
$this->eraseObject();
if (!isset($email) || $email === "") {
return false;
}
$emailValue = db_sql_encode($email);
$query = "" . "SELECT blogUser.userId, blogUser.email, blogUser.updated " . "FROM blogUser " . "INNER JOIN (" . "SELECT " . "MAX(t1.updated) AS updated, " . "t1.userId as userId " . "FROM blogUser " . "AS t1 " . "GROUP BY t1.userId" . ") " . "AS t2 " . "WHERE blogUser.userId=t2.userId " . "AND blogUser.updated=t2.updated " . "AND blogUser.email={$emailValue} " . "ORDER BY blogUser.updated DESC " . "LIMIT 1";
// print "\n$query\n";
$result = mysql_query($query);
if (!$result) {
// Error executing the query
print $query . "<br/>";
print " --> error: " . mysql_error() . "<br/>\n";
return false;
}
if (mysql_num_rows($result) <= 0) {
// User does not exist
return false;
}
$line = mysql_fetch_array($result, MYSQL_ASSOC);
$userId = db_sql_decode($line["userId"]);
return $this->load($userId);
}
