public function testLists()
{
$list = ["a", "a\na", "a\ra", "a\ta", "a\\a", "a\\'a", "a\"a"];
$q = new SelectStatement();
$q->from('table1')->selectCount()->where(sqlstr('col1')->inList($list, SQLString::STRING_RENDERER()));
$count = self::$currentDatabase->executeScalar($q . '');
$this->assertEquals(count($list), $count);
}
public function testUpdate()
{
for ($a = 0; $a != 10; $a++) {
self::$currentDatabase->insert('table2', ['field1' => 'f1' . $a, 'field2' => $a]);
}
$sr = new StatementResult();
self::$currentDatabase->update('table2', ['field2' => 1000], sqlstr('id')->equalsTo(':p1'), [':p1' => 2], $sr);
$updateCounts = self::$currentDatabase->executeScalar('select count(*) from table2 where field2=1000');
$this->assertEquals(1, $updateCounts);
$this->assertEquals(1, $sr->getAffectedRecords());
}
function sqlvalue($val, $quote)
{
if ($quote) {
$tmp = sqlstr($val);
} else {
$tmp = $val;
}
if ($tmp == "") {
$tmp = "NULL";
} elseif ($quote) {
$tmp = "'" . $tmp . "'";
}
return $tmp;
}
public function testNulls()
{
$s = new SelectStatement();
$s->from('table1')->where(sqlstr('field1')->isNull())->andWhere(sqlstr('field2')->isNotNull())->selectAll();
$this->assertEquals('SELECT * FROM table1 WHERE field1 IS NULL AND field2 IS NOT NULL', $s . '');
}
<?
if (isset($_REQUEST[username]))
{
$username = sqlstr($_REQUEST[username]);
$password = sqlstr($_REQUEST[password]);
$hash = md5($password);
$userId = $conn->queryOne("SELECT user_id FROM users WHERE username = '******' AND password = '******'");
if ($userId > 0)
{
$_SESSION[userId] = $userId;
header("location: /p/resource");
}
}
?>
<?include("pages/header.php")?>
<div class='loginForm'>
<form method='post'>
<table>
<tr>
<td class='left'>Username:</td>
<td class='right'><input type='text' name='username' value='<?php
echo $username;
?>
'/></td>
</tr>
<tr>
<td class='left'>Password:</td>
function sql_getrecordcount()
{
global $conn;
global $order;
global $ordtype;
global $filter;
global $filterfield;
global $wholeonly;
$filterstr = sqlstr($filter);
if (!$wholeonly && isset($wholeonly) && $filterstr != '') {
$filterstr = "%" . $filterstr . "%";
}
$sql = "SELECT COUNT(*) FROM `general_structure`";
if (isset($filterstr) && $filterstr != '' && isset($filterfield) && $filterfield != '') {
$sql .= " where " . sqlstr($filterfield) . " like '" . $filterstr . "'";
} elseif (isset($filterstr) && $filterstr != '') {
$sql .= " where (`index_struct` like '" . $filterstr . "') or (`tabla` like '" . $filterstr . "') or (`campo` like '" . $filterstr . "') or (`valor` like '" . $filterstr . "') or (`tipo` like '" . $filterstr . "') or (`longitud` like '" . $filterstr . "') or (`descripcion` like '" . $filterstr . "') or (`titulo` like '" . $filterstr . "') or (`control` like '" . $filterstr . "') or (`sql_select` like '" . $filterstr . "') or (`orientacion` like '" . $filterstr . "') or (`order_index` like '" . $filterstr . "') or (`script_field` like '" . $filterstr . "') or (`help_text` like '" . $filterstr . "')";
}
$res = mysql_query($sql, $conn) or die(mysql_error());
$row = mysql_fetch_assoc($res);
reset($row);
return current($row);
}
function sql_getrecordcount()
{
global $conn;
global $order;
global $ordtype;
global $filter;
global $filterfield;
global $wholeonly;
$filterstr = sqlstr($filter);
if (!$wholeonly && isset($wholeonly) && $filterstr != '') {
$filterstr = "%" . $filterstr . "%";
}
$sql = "SELECT COUNT(*) FROM `tipocontato`";
if (isset($filterstr) && $filterstr != '' && isset($filterfield) && $filterfield != '') {
$sql .= " where " . sqlstr($filterfield) . " like '" . $filterstr . "'";
} elseif (isset($filterstr) && $filterstr != '') {
$sql .= " where (`codigoTipoContato` like '" . $filterstr . "') or (`descricao` like '" . $filterstr . "')";
}
$res = mysql_query($sql, $conn) or die(mysql_error());
$row = mysql_fetch_assoc($res);
reset($row);
return current($row);
}
function sql_getrecordcount()
{
global $conn;
global $order;
global $ordtype;
global $filter;
global $filterfield;
global $wholeonly;
$filterstr = sqlstr($filter);
if (!$wholeonly && isset($wholeonly) && $filterstr != '') {
$filterstr = "%" . $filterstr . "%";
}
$sql = "SELECT COUNT(*) FROM (SELECT t1.`idcontrol`, t1.`tipo_operacion`, t1.`numero_de_documento`, t1.`cuenta_bancaria`, lp3.`descripcion_cuenta` AS `lp_cuenta_bancaria`, t1.`recibo_relacionado`, t1.`fecha_expedicion`, t1.`beneficiario`, t1.`monto_descontado`, t1.`monto_real`, t1.`estatus`, t1.`idusuario`, lp10.`nombreusuario` AS `lp_idusuario`, t1.`usuario_autorizo`, lp11.`nombreusuario` AS `lp_usuario_autorizo`, t1.`eacp`, t1.`sucursal` FROM `bancos_operaciones` AS t1 LEFT OUTER JOIN `bancos_cuentas` AS lp3 ON (t1.`cuenta_bancaria` = lp3.`idbancos_cuentas`) LEFT OUTER JOIN `usuarios` AS lp10 ON (t1.`idusuario` = lp10.`idusuarios`) LEFT OUTER JOIN `usuarios` AS lp11 ON (t1.`usuario_autorizo` = lp11.`idusuarios`)) subq";
if (isset($filterstr) && $filterstr != '' && isset($filterfield) && $filterfield != '') {
$sql .= " where " . sqlstr($filterfield) . " like '" . $filterstr . "'";
} elseif (isset($filterstr) && $filterstr != '') {
$sql .= " where (`tipo_operacion` like '" . $filterstr . "') or (`numero_de_documento` like '" . $filterstr . "') or (`lp_cuenta_bancaria` like '" . $filterstr . "') or (`recibo_relacionado` like '" . $filterstr . "') or (`fecha_expedicion` like '" . $filterstr . "') or (`beneficiario` like '" . $filterstr . "') or (`monto_descontado` like '" . $filterstr . "') or (`monto_real` like '" . $filterstr . "') or (`estatus` like '" . $filterstr . "') or (`lp_idusuario` like '" . $filterstr . "') or (`lp_usuario_autorizo` like '" . $filterstr . "')";
}
$res = mysql_query($sql, $conn) or die(mysql_error());
$row = mysql_fetch_assoc($res);
reset($row);
return current($row);
}
<?
if (isset($_REQUEST[action]))
{
$username = sqlstr($_REQUEST[username]);
$password = sqlstr($_REQUEST[password]);
$password2 = sqlstr($_REQUEST[password2]);
$email = sqlstr($_REQUEST[email]);
$hash = md5($password);
$userExists = $conn->queryOne("SELECT user_id FROM users WHERE username = '******'");
$userLengthValid = strlen($username) >= 3;
$passwordLengthValid = strlen($password) >= 6;
$passwordMatches = $password == $password2;
$emailValid = validEmail($email);
switch ($_REQUEST[action])
{
case "checkusername":
if ($userExists)
echo "0|The username <b>$username</b> is already taken. Please choose another.";
else if (!$userLengthValid)
echo "0|Your chosen username is too short.";
else
echo "1|This username is valid!";
exit();
case "checkpassword":
echo $passwordLengthValid ? "1" : "0";
echo "|";
function sqlvalue($val, $quote)
{
if ($quote) {
$tmp = sqlstr($val);
} else {
$tmp = $val;
}
if ($tmp === "") {
$tmp = "NULL";
} elseif ($quote) {
$tmp = "'" . $tmp . "'";
//return str_replace("'", "\'", $tmp);
}
return $tmp;
}
function sql_getrecordcount()
{
global $conn;
global $order;
global $ordtype;
global $filter;
global $filterfield;
global $wholeonly;
$filterstr = sqlstr($filter);
if (!$wholeonly && isset($wholeonly) && $filterstr != '') {
$filterstr = "%" . $filterstr . "%";
}
$sql = "SELECT COUNT(*) FROM (SELECT t1.`ideacp_config_bases_de_integracion_miembros`, t1.`codigo_de_base`, lp1.`descripcion` AS `lp_codigo_de_base`, t1.`miembro`, lp2.`descripcion_operacion` AS `lp_miembro`, t1.`afectacion`, t1.`descripcion_de_la_relacion` FROM `eacp_config_bases_de_integracion_miembros` AS t1 LEFT OUTER JOIN `eacp_config_bases_de_integracion` AS lp1 ON (t1.`codigo_de_base` = lp1.`codigo_de_base`) LEFT OUTER JOIN `operaciones_tipos` AS lp2 ON (t1.`miembro` = lp2.`idoperaciones_tipos`)) subq";
if (isset($filterstr) && $filterstr != '' && isset($filterfield) && $filterfield != '') {
$sql .= " where " . sqlstr($filterfield) . " like '" . $filterstr . "'";
} elseif (isset($filterstr) && $filterstr != '') {
$sql .= " where (`ideacp_config_bases_de_integracion_miembros` like '" . $filterstr . "') or (`lp_codigo_de_base` like '" . $filterstr . "') or (`lp_miembro` like '" . $filterstr . "') or (`afectacion` like '" . $filterstr . "') or (`descripcion_de_la_relacion` like '" . $filterstr . "')";
}
$res = mysql_query($sql, $conn) or die(mysql_error());
$row = mysql_fetch_assoc($res);
reset($row);
return current($row);
}
public static function getAcceptTaskCountById($openid)
{
$openid_e = sqlstr($openid);
$sql = "SELECT COUNT(1) FROM Task WHERE accepter_openid={$openid_e} and status=3";
return db_fetch_value($sql);
}
/**
* Creates an condition from an associative array
* @param array $params
* @return array
*/
protected function createAndCondition(array $params)
{
$condition = sqlstr('');
$conditionParameters = [];
$first = true;
foreach ($params as $field => $value) {
if (!$first) {
$condition->append(' AND ');
}
$condition->append($field);
if (is_null($value)) {
$condition->isNull();
} else {
$param = ':cc_' . $field;
$condition->equalsTo($param);
$conditionParameters[$param] = $value;
}
$first = false;
}
return [$condition, $conditionParameters];
}
function sql_getrecordcount()
{
global $conn;
global $order;
global $ordtype;
global $filter;
global $filterfield;
global $wholeonly;
$filterstr = sqlstr($filter);
if (!$wholeonly && isset($wholeonly) && $filterstr != '') {
$filterstr = "%" . $filterstr . "%";
}
$sql = "SELECT COUNT(*) FROM (SELECT t1.`codigoEmail`, t1.`codigoTipoEmail`, lp1.`descricao` AS `lp_codigoTipoEmail`, t1.`endereco` FROM `email` AS t1 LEFT OUTER JOIN `tipoemail` AS lp1 ON (t1.`codigoTipoEmail` = lp1.`codigoTipoEmail`)) subq";
if (isset($filterstr) && $filterstr != '' && isset($filterfield) && $filterfield != '') {
$sql .= " where " . sqlstr($filterfield) . " like '" . $filterstr . "'";
} elseif (isset($filterstr) && $filterstr != '') {
$sql .= " where (`codigoEmail` like '" . $filterstr . "') or (`lp_codigoTipoEmail` like '" . $filterstr . "') or (`endereco` like '" . $filterstr . "')";
}
$res = mysql_query($sql, $conn) or die(mysql_error());
$row = mysql_fetch_assoc($res);
reset($row);
return current($row);
}
function sql_getrecordcount()
{
global $conn;
global $order;
global $ordtype;
global $filter;
global $filterfield;
global $wholeonly;
$filterstr = sqlstr($filter);
if (!$wholeonly && isset($wholeonly) && $filterstr != '') {
$filterstr = "%" . $filterstr . "%";
}
$sql = "SELECT COUNT(*) FROM (SELECT t1.`codigoContato`, lp0.`nome` AS `lp_codigoContato`, t1.`codigoTelefone`, lp1.`numero` AS `lp_codigoTelefone` FROM `contatotelefones` AS t1 LEFT OUTER JOIN `contato` AS lp0 ON (t1.`codigoContato` = lp0.`codigoContato`) LEFT OUTER JOIN `telefone` AS lp1 ON (t1.`codigoTelefone` = lp1.`codigoTelefone`)) subq";
if (isset($filterstr) && $filterstr != '' && isset($filterfield) && $filterfield != '') {
$sql .= " where " . sqlstr($filterfield) . " like '" . $filterstr . "'";
} elseif (isset($filterstr) && $filterstr != '') {
$sql .= " where (`lp_codigoContato` like '" . $filterstr . "') or (`lp_codigoTelefone` like '" . $filterstr . "')";
}
$res = mysql_query($sql, $conn) or die(mysql_error());
$row = mysql_fetch_assoc($res);
reset($row);
return current($row);
}
function checkpid($pid)
{
$imei = imei();
$pid_e = sqlstr($pid);
$login_key = "LOGIN-{$pid}";
$login_imeis = apcfetch($login_key);
if ($login_imeis === FALSE) {
$login_imeis = array();
$data = db_fetch_all("SELECT imei FROM login WHERE pid={$pid_e}");
foreach ($data as $e) {
$imei = $e["imei"];
$login_imeis[$imei] = 1;
}
apcstore($login_key, $login_imeis);
$login_imeis = apcfetch($login_key);
//operation_fail($login_imeis);
on_apc_miss();
} else {
on_apc_hit();
}
if (!isset($login_imeis[$imei])) {
operation_fail("你的登陆信息已失效，为了保护账号，请退出重新登陆");
}
}
public function testConcat()
{
$this->assertEquals("md5(username||password)", sqlstr('username')->concat('password')->md5());
}
/**
* @param type $name
* @return SQLString
*/
protected function column($name)
{
return sqlstr($name)->dotPrefix($this->rel_alias);
}
//must check this is a valid language
$languages = $conn->queryAll("SELECT l.*, (select count(distinct translation_id) FROM translations WHERE variable_id in (select variable_id from variables where resource_id = $resourceId) AND language_code = l.code) as complete FROM languages l");
$languageFound = in_array_column($languageCode,'code',$languages);
if (!$languageFound)
$languageCode = "";
else
{
switch ($_REQUEST[a])
{
case "update":
$variableId = (int)$_REQUEST[id];
$text = sqlstr(str_replace('\\','\\\\',$_REQUEST[content]));
$existing = $conn->queryAll("SELECT * FROM translations WHERE variable_id = $variableId AND user_id = $userId AND language_code = '$languageCode'");
if (sizeof($existing) == 0)
{
$conn->exec("INSERT INTO translations (user_id, variable_id, language_code, text) VALUES ($userId, $variableId, '$languageCode', '$text')");
}
else
{
$conn->exec("UPDATE translations SET text = '$text', last_update = CURRENT_TIMESTAMP WHERE user_id = $userId AND language_code = '$languageCode' AND variable_id = $variableId");
}
echo "1";
exit();
/**
* Adds a table an a optional alias to the FROM list
* @param string $table The name of the table
* @param string $alias The table alias, default to ''
* @return \Blend\Component\Database\SQL\Statement\Select
*/
public function from($table, $alias = '')
{
if (!empty($alias)) {
$this->from[] = sqlstr($table)->tableAlias($alias);
} else {
$this->from[] = $table;
}
$this->lastFromIndex = count($this->from) - 1;
return $this;
}
public function testSelectCountWithAliasTest()
{
$s = new SelectStatement();
$s->selectCount('numbers')->from('table1')->where(sqlstr('field1')->equalsTo(5));
$this->assertEquals('SELECT COUNT(*) AS numbers FROM table1 WHERE field1 = 5', $s . '');
}
function sql_getrecordcount()
{
global $conn;
global $order;
global $ordtype;
global $filter;
global $filterfield;
global $wholeonly;
$filterstr = sqlstr($filter);
if (!$wholeonly && isset($wholeonly) && $filterstr != '') {
$filterstr = "%" . $filterstr . "%";
}
$sql = "SELECT COUNT(*) FROM (SELECT t1.`codigoTarefa`, t1.`codigoTarefaStatus`, lp1.`descricao` AS `lp_codigoTarefaStatus`, t1.`dataInicio`, t1.`horaInicial`, t1.`dataLimite`, t1.`horaLimite`, t1.`titulo`, t1.`descricao`, t1.`codigoTarefaPrioridade`, lp8.`descricao` AS `lp_codigoTarefaPrioridade`, t1.`datahoracriacao` FROM `tarefa` AS t1 LEFT OUTER JOIN `tarefastatus` AS lp1 ON (t1.`codigoTarefaStatus` = lp1.`codigoTarefaStatus`) LEFT OUTER JOIN `tarefaprioridade` AS lp8 ON (t1.`codigoTarefaPrioridade` = lp8.`codigoTarefaPrioridade`)) subq";
if (isset($filterstr) && $filterstr != '' && isset($filterfield) && $filterfield != '') {
$sql .= " where " . sqlstr($filterfield) . " like '" . $filterstr . "'";
} elseif (isset($filterstr) && $filterstr != '') {
$sql .= " where (`codigoTarefa` like '" . $filterstr . "') or (`lp_codigoTarefaStatus` like '" . $filterstr . "') or (`dataInicio` like '" . $filterstr . "') or (`horaInicial` like '" . $filterstr . "') or (`dataLimite` like '" . $filterstr . "') or (`horaLimite` like '" . $filterstr . "') or (`titulo` like '" . $filterstr . "') or (`descricao` like '" . $filterstr . "') or (`lp_codigoTarefaPrioridade` like '" . $filterstr . "') or (`datahoracriacao` like '" . $filterstr . "')";
}
$res = mysql_query($sql, $conn) or die(mysql_error());
$row = mysql_fetch_assoc($res);
reset($row);
return current($row);
}
/**
* Loads columns for a given relation
* @param Relation $relation
*/
protected function loadColumnsForRelation(Relation $relation)
{
$sql = new SelectStatement();
$sql->from('information_schema.columns')->where(sqlstr('table_schema')->equalsTo(':table_schema'))->andWhere(sqlstr('table_name')->equalsTo(':table_name'));
$params = [':table_schema' => $relation->getSchemaName(), ':table_name' => $relation->getName()];
foreach ($this->database->executeQuery($sql, $params) as $record) {
$column = new Column($record);
$relation->addColumn($column);
}
}
