public function checkLogin($username, $password) { $ps = new PreparedStatement('SELECT group_id FROM members WHERE username = ? AND password = ?'); $ps->setString(1, $username); $ps->setString(2, $password); $rs = $this->ds->execute($ps->getSql()); $ret = 0; if ($row = mysql_fetch_array($rs)) { $ret = (int) $row['group_id']; } mysql_free_result($rs); return $ret; }
public function findByDateRange($fromDate, $toDate) { $sql = 'SELECT * FROM carts WHERE checkout_datetime >= ? AND checkout_datetime <= ? ORDER BY checkout_datetime'; $ps = new PreparedStatement($sql); $ps->setString(1, $fromDate); $ps->setString(2, $toDate); $rs = $this->ds->execute($ps->getSql()); $carts = array(); while ($row = mysql_fetch_array($rs)) { $carts[$row['id']] = $row['checkout_datetime']; } mysql_free_result($rs); return $carts; }
public function insert($name) { $ps = new PreparedStatement('INSERT categories(name) VALUES(?)'); $ps->setString(1, $this->ds->escape($name)); if ($this->ds->execute($ps->getSql())) { return $this->ds->getLastId(); } else { return -1; } }
function update($id, $full_name, $birthday, $address, $email, $city_id, $note, $phone) { $sql = 'UPDATE customers ' . 'SET full_name = ?, birthday = ?, address = ?, email = ?, city_id = ?, note = ?, phone = ? ' . 'WHERE id = ?'; $ps = new PreparedStatement($sql); $ps->setString(1, $this->ds->escape($full_name)); if (!isset($birthday) || strlen($birthday) == 0) { $ps->setNull(2); } else { $ps->setString(2, $this->ds->escape($birthday)); } if (!isset($address) || strlen($address) == 0) { $ps->setNull(3); } else { $ps->setString(3, $this->ds->escape($address)); } if (!isset($email) || strlen($email) == 0) { $ps->setNull(4); } else { $ps->setString(4, $this->ds->escape($email)); } if (!isset($city_id) || $city_id <= 0) { $ps->setNull(5); } else { $ps->setInt(5, $city_id); } if (!isset($note) || strlen($note) == 0) { $ps->setNull(6); } else { $ps->setString(6, $this->ds->escape($note)); } if (!isset($phone) || strlen($phone) == 0) { $ps->setNull(7); } else { $ps->setString(7, $this->ds->escape($phone)); } $ps->setInt(8, $id); return $this->ds->execute($ps->getSql()); }
public function validate($db, &$row) { $sql = sprintf('select %s from %s', $this->foreignKeyMapping[0]->foreign, $this->foreignTable); $sep = ' where '; foreach ($this->foreignKeyMapping as $fkm) { $sql .= sprintf('%s%s = ?', $sep, $fkm->foreign); if ($sep != ' and ') { $sep = ' and '; } } $ps = new PreparedStatement($sql, 0, 1); foreach ($this->foreignKeyMapping as $fkm) { $vn = $fkm->local; $val = property_exists($row, $vn) ? $row->{$vn} : ''; // If we're set to allow nulls and any value is null, don't validate. if ($this->allowNULL && $val === null) { return ''; } switch ($fkm->type) { case 'int': $ps->setInt($val); break; case 'float': $ps->setFloat($val); break; case 'double': $ps->setDouble($val); break; case 'boolean': $ps->setBoolean($val); break; case 'string': $ps->setString($val); break; case 'binary': $ps->setBinary($val); break; default: throw new Exception(sprintf('Unexpected PreparedStatement data type: %s', $fkm->type)); } } if (!$db->fetchObject($db->executeQuery($ps), true)) { if ($this->errorMsg != '') { return $this->errorMsg; } return _t('ForeignKeyValidator.class.errorMsg.mustMatchAnExistingEntry'); } return ''; }
function deleteCheckHook() { global $db, $id, $result, $RESERVED_PERM_NAMES; $apppermDAO = new ApppermDAO($db); if (!($perm = $apppermDAO->load($id))) { $result->errorMsg .= "This Permission cannot be deleted because it does not exist.\n"; return; } if (in_array($perm->perm_name, $RESERVED_PERM_NAMES)) { $result->errorMsg .= "This is a reserved Permission, and cannot be deleted.\n"; return; } $ps = new PreparedStatement('select * from apppageuriperm where perm_name = ?', 0, 1); $ps->setString($perm->perm_name); if ($db->fetchObject($db->executeQuery($ps), true)) { $result->errorMsg .= "This Permission cannot be deleted because it is referenced by one or more Page URIs.\n"; } $ps = new PreparedStatement('select * from approleperm where perm_name = ?', 0, 1); $ps->setString($perm->perm_name); if ($db->fetchObject($db->executeQuery($ps), true)) { $result->errorMsg .= "This Permission cannot be deleted because it is referenced by one or more Roles.\n"; } }
public static function inScriptPermissionsCheck($user_id, $showMenuIfFailed) { $pageURI = $_SERVER['REQUEST_URI']; if (!Permissions::hasPermissionsForScript($user_id, $pageURI)) { if ($showMenuIfFailed) { include dirname(dirname(__FILE__)) . '/include/header.include.php'; } else { echo '<html><head></head><body>'; } echo '<h3>You need the following permissions to use this page:</h3>'; echo '<ul>'; $fileCache = self::createFileCache(); $db = null; $ps = new PreparedStatement('select description from appperm where perm_name = ?'); foreach (Permissions::getRequiredPermissionsForScript($pageURI) as $p) { $cacheKey = sprintf('apppermDesc%s', $p); if (($desc = $fileCache->get($cacheKey)) === false) { if ($db === null) { $db = ConnectionFactory::getConnection(); } $ps->clearParams(); $ps->setString($p); if (($row = $db->fetchObject($db->executeQuery($ps), true)) !== false) { $desc = $row->description; } else { $desc = $p; } $fileCache->set($cacheKey, $desc); } echo '<li>'; echo htmlspecialchars($desc); echo '</li>'; } if ($db !== null) { $db->close(); } echo '</ul>'; if ($showMenuIfFailed) { include dirname(dirname(__FILE__)) . '/include/footer.include.php'; } else { echo '</body></html>'; } exit; } }
$ps->setInt($queryCol == '' || $queryCol == 'pri.description' ? 1 : 0); $ps->setString('%' . $query . '%'); $ps->setInt($queryCol == '' || $queryCol == 'pri.normal_sign' ? 1 : 0); $ps->setString('%' . $query . '%'); $row = $db->fetchObject($db->executeQuery($ps), true); $rowCount = isset($row->rowCount) ? (int) $row->rowCount : 0; printf('{"sEcho": %d, "iTotalRecords": %d, "iTotalDisplayRecords": %d, "aaData": [', $sEcho, $rowCount, $rowCount); // Get actual rows. $ps = new PreparedStatement(<<<EOF select pri.* EOF . $sqlTail . $orderBy, $offset, $limit); $ps->setInt($queryCol == '' || $queryCol == 'pri.id' ? 1 : 0); $ps->setInt($query); $ps->setInt($queryCol == '' || $queryCol == 'pri.description' ? 1 : 0); $ps->setString('%' . $query . '%'); $ps->setInt($queryCol == '' || $queryCol == 'pri.normal_sign' ? 1 : 0); $ps->setString('%' . $query . '%'); $rows = $db->fetchAllObjects($db->executeQuery($ps), true); $sep = ''; foreach ($rows as $row) { $arr = array(); foreach ($returnColumns as $dc) { $arr[] = isset($row->{$dc}) ? $row->{$dc} : ''; } echo $sep; echo json_encode($arr); if ($sep == '') { $sep = ','; } }
public function isExistedSeoUrl_Except($seo_url, $id) { $sql = "SELECT id FROM products WHERE seo_url = ? AND id <> ?"; $ps = new PreparedStatement($sql); $ps->setString(1, $seo_url); $ps->setInt(2, $id); $rs = $this->ds->execute($ps->getSql()); $b = FALSE; if (mysql_fetch_array($rs)) { $b = TRUE; } mysql_free_result($rs); return $b; }
public function findByDescriptionPS($description, $queryOperator = '=', $orderBy = null, $offset = 0, $limit = 0) { if (!in_array($queryOperator, self::$ALLOWED_STRING_QUERY_OPERATORS)) { $queryOperator = self::$ALLOWED_STRING_QUERY_OPERATORS[0]; } if ($queryOperator == 'beginsWith' || $queryOperator == 'endsWith' || $queryOperator == 'contains') { $sqlQueryOperator = $this->connection->likeOperator; $needLower = !$this->connection->hasCaseInsensitiveLike; } else { $sqlQueryOperator = $queryOperator; $needLower = false; } $ps = new PreparedStatement("select * from appperm where " . ($needLower ? 'lower(description)' : 'description') . ' ' . $sqlQueryOperator . ' ' . ($needLower ? 'lower(?)' : '?') . ($orderBy !== null && $orderBy != '' ? ' order by ' . $orderBy : ''), $offset, $limit); if ($queryOperator == 'beginsWith') { $ps->setString($description . '%'); } else { if ($queryOperator == 'endsWith') { $ps->setString('%' . $description); } else { if ($queryOperator == 'contains') { $ps->setString('%' . $description . '%'); } else { $ps->setString($description); } } } return $ps; }
} else { $id = isset($params['id']) ? (int) trim($params['id']) : 0; $sqlTail = <<<EOF from appuser pri where pri.id = ? EOF; $offset = 0; $limit = 1; } $ps = new PreparedStatement(<<<EOF select pri.* EOF . $sqlTail, $offset, $limit); if ($query !== null) { $ps->setString($query . '%'); if ($canDoFulltextSearch) { $ps->setString($ftquery); $ps->setString($ftquery); } else { $ps->setString('%' . $query . '%'); } if ($canDoFulltextSearch) { $ps->setString($ftquery); $ps->setString($ftquery); } else { $ps->setString('%' . $query . '%'); } } else { $ps->setInt($id); }
// by running searchgen, passing in the table name. if (isset($command) && $command == 'loadApprole') { header('Content-Type: application/json'); $db = ConnectionFactory::getConnection(); $approleDAO = new ApproleDAO($db); $apppermDAO = new ApppermDAO($db); $id = isset($params['id']) ? (int) trim($params['id']) : 0; if ($id <= 0) { $rows = array(Approle::createDefault()); } else { $sql = <<<EOF select * from approle pri where pri.id = ? EOF; $ps = new PreparedStatement($sql, 0, 1); $ps->setInt($id); $rows = $approleDAO->findWithPreparedStatement($ps); } $ps1 = new PreparedStatement(<<<EOF select distinct p.* from approleperm r_p inner join appperm p on p.perm_name = r_p.perm_name where r_p.role_name = ? order by p.perm_name EOF , 0, 0); foreach ($rows as &$row) { $ps1->clearParams(); $ps1->setString($row->role_name); $row->perms = $id <= 0 ? array() : $apppermDAO->findWithPreparedStatement($ps1); } unset($row); echo json_encode($rows); $db->close(); exit; }
public function update($col, $content) { $ps = new PreparedStatement('UPDATE global SET ' . $col . ' = ?'); $ps->setString(1, $this->ds->escape($content)); return $this->ds->execute($ps->getSql()); }
public function validate($db, &$row) { $sql = sprintf('select %s from %s', $this->fields[0]->field, $this->table); $sep = ' where '; foreach ($this->fields as $fld) { $qo = $fld->queryOperator; if ($qo == 'beginsWith' || $qo == 'contains' || $qo == 'endsWith') { $qo = 'like'; } $sql .= sprintf('%s%s %s ?', $sep, $fld->field, $qo); if ($sep != ' and ') { $sep = ' and '; } } $ps = new PreparedStatement($sql, 0, 1); foreach ($this->fields as $fld) { $vn = $fld->field; $val = property_exists($row, $vn) ? $row->{$vn} : ''; // If we're set to allow nulls and any value is null, don't validate. if ($this->allowNULL && $val === null) { return ''; } switch ($fld->type) { case 'int': $ps->setInt($val); break; case 'float': $ps->setFloat($val); break; case 'double': $ps->setDouble($val); break; case 'boolean': $ps->setBoolean($val); break; case 'string': switch ($fld->queryOperator) { case 'beginsWith': $ps->setString($val . '%'); break; case 'contains': $ps->setString('%' . $val . '%'); break; case 'endsWith': $ps->setString('%' . $val); break; default: $ps->setString($val); break; } break; case 'binary': $ps->setBinary($val); break; default: throw new Exception(sprintf('Unexpected PreparedStatement data type: %s', $fld->type)); } } if ($db->fetchObject($db->executeQuery($ps), true)) { if ($this->errorMsg != '') { return $this->errorMsg; } return _t('NoDuplicatesValidator.class.errorMsg.anEntryAlreadyExistsWithThisValue'); } return ''; }
public static function isView($db, $tableName, $dbName = '') { switch ($db->getDialect()) { case 'mysql': $dbNamePlaceholder = $dbName != '' ? '?' : 'database()'; $ps = new PreparedStatement("select TABLE_NAME from information_schema.VIEWS where TABLE_SCHEMA = {$dbNamePlaceholder} and TABLE_NAME = ?"); if ($dbName != '') { $ps->setString($dbName); } $ps->setString($tableName); return $db->fetchObject($db->executeQuery($ps), true) !== false; case 'pgsql': $dbNamePlaceholder = $dbName != '' ? '?' : 'current_schema()'; $ps = new PreparedStatement("select viewname from pg_views where schemaname = {$dbNamePlaceholder} and viewname = ?"); if ($dbName != '') { $ps->setString($dbName); } $ps->setString($tableName); return $db->fetchObject($db->executeQuery($ps), true) !== false; } }