Compatibility of Logic Operators of SQL Statements "AND", "&&"

Keywords: MySQL PHP Database PDO SQLite

Recently, I plan to transfer my blog to the typecho platform and choose a theme that I personally prefer. Akina for Typecho Theme Template Thank you for the open source sharing of the theme, but there was a "500 Database Query Error" problem at the beginning of the use process.

Add the following code at the beginning of the index.php file for typecho.

  define('__TYPECHO_DEBUG__',true);   

Open the page again and check the source code of the page and find the following error prompts:

<pre><code><h1>SQLSTATE[HY000]: General error: 1 near &quot;&amp;&quot;: syntax error</h1>Typecho_Db_Query_Exception: SQLSTATE[HY000]: General error: 1 near &quot;&amp;&quot;: syntax error in /www/users/root/WEB/var/Typecho/Db/Adapter/Pdo.php:105
Stack trace:
#0 /www/users/root/WEB/var/Typecho/Db.php(354): Typecho_Db_Adapter_Pdo-&gt;query('SELECT typecho_...', Object(PDO), 1, 'SELECT')
#1 /www/users/root/WEB/var/Typecho/Db.php(384): Typecho_Db-&gt;query(Object(Typecho_Db_Query), 1)
#2 /www/users/root/WEB/usr/themes/Akina/index.php(39): Typecho_Db-&gt;fetchAll(Object(Typecho_Db_Query))
#3 /www/users/root/WEB/var/Widget/Archive.php(2022): require_once('/www/users/root...')
#4 /www/users/root/WEB/var/Typecho/Router.php(138): Widget_Archive-&gt;render()
#5 /www/users/root/WEB/index.php(26): Typecho_Router::dispatch()
#6 {main}</code></pre>

By looking at the error code, you can see that the error occurred in the function "Akina/index.php(39): Typecho_Db-> fetchAll (Object (Typecho_Db_Query)", and by printing the input parameter fetchAll(), you can see that it is a query statement:

["where"]=>
string(176) " WHERE  ("type" = #param:0 # && "status" = #param:1# && "created" < #param:2# ) AND (typecho_contents."cid" != #param:3# ) OR ("authorId" = #param:4# && "status" = #param:5# )"

Seeing this, I found that I used the SQLITE database. In the SQLLITE database, there is no support for'&'to do logic AND operators. By replacing'&' in the following code of the "Akina/index.php" file with'&'AND then reloading the page, the error disappears AND the page opens normally.

$select2 = $this->select()->where('type = ? && status = ? && created < ?', 'post','publish',time());
if($uid) $select2->orWhere('authorId = ? && status = ?',$uid,'private');

Finally, after experimenting with MYSQL, SQL Server and SQLite databases, only MYSQL databases can support'&''and'AND', so we try to use standard'AND' as logic and operator in writing SQL statements.

Posted by phpCCore Brad on Sun, 06 Oct 2019 09:15:08 -0700