diff options
| author | Habeas Codice <habeascodice@federated.social> | 2014-11-13 12:21:58 -0800 |
|---|---|---|
| committer | Habeas Codice <habeascodice@federated.social> | 2014-11-13 12:21:58 -0800 |
| commit | 1a5a5c7edb8697c93f8bababbafa80245378dd7e (patch) | |
| tree | b3d3b5ff30ecce1316ad0386d0e89fe18a867b4f /mod | |
| parent | 31376de0665091f2dba04755562ccd238d57a13c (diff) | |
| download | volse-hubzilla-1a5a5c7edb8697c93f8bababbafa80245378dd7e.tar.gz volse-hubzilla-1a5a5c7edb8697c93f8bababbafa80245378dd7e.tar.bz2 volse-hubzilla-1a5a5c7edb8697c93f8bababbafa80245378dd7e.zip | |
PostgreSQL support initial commit
There were 11 main types of changes:
- UPDATE's and DELETE's sometimes had LIMIT 1 at the end of them. This is not only non-compliant but
it would certainly not do what whoever wrote it thought it would. It is likely this mistake was just
copied from Friendica. All of these instances, the LIMIT 1 was simply removed.
- Bitwise operations (and even some non-zero int checks) erroneously rely on MySQL implicit
integer-boolean conversion in the WHERE clauses. This is non-compliant (and bad programming practice
to boot). Proper explicit boolean conversions were added. New queries should use proper conventions.
- MySQL has a different operator for bitwise XOR than postgres. Rather than add yet another dba_
func, I converted them to "& ~" ("AND NOT") when turning off, and "|" ("OR") when turning on. There
were no true toggles (XOR). New queries should refrain from using XOR when not necessary.
- There are several fields which the schema has marked as NOT NULL, but the inserts don't specify
them. The reason this works is because mysql totally ignores the constraint and adds an empty text
default automatically. Again, non-compliant, obviously. In these cases a default of empty text was
added.
- Several statements rely on a non-standard MySQL feature
(http://dev.mysql.com/doc/refman/5.5/en/group-by-handling.html). These queries can all be rewritten
to be standards compliant. Interestingly enough, the newly rewritten standards compliant queries run
a zillion times faster, even on MySQL.
- A couple of function/operator name translations were needed (RAND/RANDOM, GROUP_CONCAT/STRING_AGG,
UTC_NOW, REGEXP/~, ^/#) -- assist functions added in the dba_
- INTERVALs: postgres requires quotes around the value, mysql requires that there are not quotes
around the value -- assist functions added in the dba_
- NULL_DATE's -- Postgres does not allow the invalid date '0000-00-00 00:00:00' (there is no such
thing as year 0 or month 0 or day 0). We use '0001-01-01 00:00:00' for postgres. Conversions are
handled in Zot/item packets automagically by quoting all dates with dbescdate().
- char(##) specifications in the schema creates fields with blank spaces that aren't trimmed in the
code. MySQL apparently treats char(##) as varchar(##), again, non-compliant. Since postgres works
better with text fields anyway, this ball of bugs was simply side-stepped by using 'text' datatype
for all text fields in the postgres schema. varchar was used in a couple of places where it actually
seemed appropriate (size constraint), but without rigorously vetting that all of the PHP code
actually validates data, new bugs might come out from under the rug.
- postgres doesn't store nul bytes and a few other non-printables in text fields, even when quoted.
bytea fields were used when storing binary data (photo.data, attach.data). A new dbescbin() function
was added to handle this transparently.
- postgres does not support LIMIT #,# syntax. All databases support LIMIT # OFFSET # syntax.
Statements were updated to be standard.
These changes require corresponding changes in the coding standards. Please review those before
adding any code going forward.
Still on my TODO list:
- remove quotes from non-reserved identifiers and make reserved identifiers use dba func for quoting
- Rewrite search queries for better results (both MySQL and Postgres)
Diffstat (limited to 'mod')
48 files changed, 267 insertions, 234 deletions
diff --git a/mod/acl.php b/mod/acl.php index 01f5103f1..797a3633b 100644 --- a/mod/acl.php +++ b/mod/acl.php @@ -49,7 +49,7 @@ function acl_init(&$a){ if ($type=='' || $type=='c'){ $r = q("SELECT COUNT(abook_id) AS c FROM abook left join xchan on abook_xchan = xchan_hash - WHERE abook_channel = %d AND not ( abook_flags & %d ) and not (xchan_flags & %d ) $sql_extra2" , + WHERE abook_channel = %d AND not ( abook_flags & %d )>0 and not (xchan_flags & %d )>0 $sql_extra2" , intval(local_user()), intval(ABOOK_FLAG_BLOCKED|ABOOK_FLAG_PENDING|ABOOK_FLAG_ARCHIVED), intval(XCHAN_FLAGS_DELETED) @@ -59,7 +59,7 @@ function acl_init(&$a){ if(intval(get_config('system','taganyone')) || intval(get_pconfig(local_user(),'system','taganyone'))) { if(((! $r) || (! $r[0]['total'])) && $type == 'c') { $r = q("SELECT COUNT(xchan_hash) AS c FROM xchan - WHERE not (xchan_flags & %d ) $sql_extra2" , + WHERE not (xchan_flags & %d )>0 $sql_extra2" , intval(XCHAN_FLAGS_DELETED) ); $contact_count = (int)$r[0]['c']; @@ -75,8 +75,8 @@ function acl_init(&$a){ $r = q("SELECT count(xchan_hash) as c FROM abook left join xchan on abook_xchan = xchan_hash - WHERE abook_channel = %d and ( (abook_their_perms = null) or (abook_their_perms & %d )) - and not ( xchan_flags & %d ) + WHERE abook_channel = %d and ( (abook_their_perms = null) or (abook_their_perms & %d )>0) + and not ( xchan_flags & %d )>0 $sql_extra2 ", intval(local_user()), intval(PERMS_W_MAIL), @@ -92,7 +92,7 @@ function acl_init(&$a){ // autocomplete for Contacts $r = q("SELECT COUNT(abook_id) AS c FROM abook left join xchan on abook_xchan = xchan_hash - WHERE abook_channel = %d and not ( xchan_flags & %d ) $sql_extra2" , + WHERE abook_channel = %d and not ( xchan_flags & %d )>0 $sql_extra2" , intval(local_user()), intval(XCHAN_FLAGS_DELETED) ); @@ -110,17 +110,18 @@ function acl_init(&$a){ if ($type=='' || $type=='g'){ $r = q("SELECT `groups`.`id`, `groups`.`hash`, `groups`.`name`, - GROUP_CONCAT(DISTINCT `group_member`.`xchan` SEPARATOR ',') as uids + %s as uids FROM `groups`,`group_member` WHERE `groups`.`deleted` = 0 AND `groups`.`uid` = %d AND `group_member`.`gid`=`groups`.`id` $sql_extra GROUP BY `groups`.`id` ORDER BY `groups`.`name` - LIMIT %d,%d", + LIMIT %d OFFSET %d", + db_concat('group_member.xchan', ','), intval(local_user()), - intval($start), - intval($count) + intval($count), + intval($start) ); foreach($r as $g){ @@ -140,7 +141,7 @@ function acl_init(&$a){ if ($type=='' || $type=='c') { $r = q("SELECT abook_id as id, xchan_hash as hash, xchan_name as name, xchan_photo_s as micro, xchan_url as url, xchan_addr as nick, abook_their_perms, abook_flags FROM abook left join xchan on abook_xchan = xchan_hash - WHERE abook_channel = %d AND not ( abook_flags & %d ) and not (xchan_flags & %d ) $sql_extra2 order by xchan_name asc" , + WHERE abook_channel = %d AND not ( abook_flags & %d )>0 and not (xchan_flags & %d )>0 $sql_extra2 order by xchan_name asc" , intval(local_user()), intval(ABOOK_FLAG_BLOCKED|ABOOK_FLAG_PENDING|ABOOK_FLAG_ARCHIVED), intval(XCHAN_FLAGS_DELETED) @@ -149,7 +150,7 @@ function acl_init(&$a){ if((! $r) && $type == 'c') { $r = q("SELECT substr(xchan_hash,1,18) as id, xchan_hash as hash, xchan_name as name, xchan_photo_s as micro, xchan_url as url, xchan_addr as nick, 0 as abook_their_perms, 0 as abook_flags FROM xchan - WHERE not (xchan_flags & %d ) $sql_extra2 order by xchan_name asc" , + WHERE not (xchan_flags & %d )>0 $sql_extra2 order by xchan_name asc" , intval(XCHAN_FLAGS_DELETED) ); } @@ -159,8 +160,8 @@ function acl_init(&$a){ $r = q("SELECT xchan_hash as id, xchan_name as name, xchan_addr as nick, xchan_photo_s as micro, xchan_url as url FROM abook left join xchan on abook_xchan = xchan_hash - WHERE abook_channel = %d and ( (abook_their_perms = null) or (abook_their_perms & %d )) - and not (xchan_flags & %d) + WHERE abook_channel = %d and ( (abook_their_perms = null) or (abook_their_perms & %d )>0) + and not (xchan_flags & %d)>0 $sql_extra3 ORDER BY `xchan_name` ASC ", intval(local_user()), @@ -171,7 +172,7 @@ function acl_init(&$a){ elseif(($type == 'a') || ($type == 'p')) { $r = q("SELECT abook_id as id, xchan_name as name, xchan_hash as hash, xchan_addr as nick, xchan_photo_s as micro, xchan_network as network, xchan_url as url, xchan_addr as attag , abook_their_perms FROM abook left join xchan on abook_xchan = xchan_hash WHERE abook_channel = %d - and not (xchan_flags & %d) + and not (xchan_flags & %d)>0 $sql_extra3 ORDER BY xchan_name ASC ", intval(local_user()), diff --git a/mod/admin.php b/mod/admin.php index 06f44c89b..cbb2395b7 100644 --- a/mod/admin.php +++ b/mod/admin.php @@ -594,7 +594,7 @@ function admin_page_users_post(&$a){ if (x($_POST,'page_users_block')){ foreach($users as $uid){ - q("UPDATE account SET account_flags = (account_flags & %d) where account_id = %d limit 1", + q("UPDATE account SET account_flags = (account_flags & %d) where account_id = %d", intval(ACCOUNT_BLOCKED), intval( $uid ) ); @@ -650,7 +650,7 @@ function admin_page_users(&$a){ }; break; case "block":{ check_form_security_token_redirectOnErr('/admin/users', 'admin_users', 't'); - q("UPDATE account SET account_flags = ( account_flags ^ %d ) where account_id = %d", + q("UPDATE account SET account_flags = ( account_flags & ~%d ) where account_id = %d", intval(ACCOUNT_BLOCKED), intval( $uid ) ); @@ -664,13 +664,13 @@ function admin_page_users(&$a){ } /* get pending */ - $pending = q("SELECT account.*, register.hash from account left join register on account_id = register.uid where (account_flags & %d ) ", + $pending = q("SELECT account.*, register.hash from account left join register on account_id = register.uid where (account_flags & %d )>0 ", intval(ACCOUNT_PENDING) ); /* get users */ - $total = q("SELECT count(*) as total FROM account where 1"); + $total = q("SELECT count(*) as total FROM account"); if(count($total)) { $a->set_pager_total($total[0]['total']); $a->set_pager_itemspage(100); @@ -690,14 +690,15 @@ function admin_page_users(&$a){ if($_REQUEST['order'] === 'created') $order = " order by account_created desc "; - $users =q("SELECT `account_id` , `account_email`, `account_lastlog`, `account_created`, `account_expires`, " . "`account_service_class`, ( account_flags & %d ) > 0 as `blocked`, " . - "(SELECT GROUP_CONCAT( ch.channel_address SEPARATOR ' ') FROM channel as ch " . - "WHERE ch.channel_account_id = ac.account_id and not (ch.channel_pageflags & %d )) as `channels` " . - "FROM account as ac where true $serviceclass $order limit %d , %d ", + $users =q("SELECT `account_id` , `account_email`, `account_lastlog`, `account_created`, `account_expires`, " . "`account_service_class`, ( account_flags & %d )>0 as `blocked`, " . + "(SELECT %s FROM channel as ch " . + "WHERE ch.channel_account_id = ac.account_id and not (ch.channel_pageflags & %d )>0) as `channels` " . + "FROM account as ac where true $serviceclass $order limit %d offset %d ", intval(ACCOUNT_BLOCKED), + db_concat('ch.channel_address', ' '), intval(PAGE_REMOVED), - intval($a->pager['start']), - intval($a->pager['itemspage']) + intval($a->pager['itemspage']), + intval($a->pager['start']) ); // function _setup_users($e){ @@ -764,7 +765,7 @@ function admin_page_channels_post(&$a){ if (x($_POST,'page_channels_block')){ foreach($channels as $uid){ - q("UPDATE channel SET channel_pageflags = ( channel_pageflags ^ %d ) where channel_id = %d", + q("UPDATE channel SET channel_pageflags = ( channel_pageflags & ~%d ) where channel_id = %d", intval(PAGE_CENSORED), intval( $uid ) ); @@ -812,7 +813,7 @@ function admin_page_channels(&$a){ case "block":{ check_form_security_token_redirectOnErr('/admin/channels', 'admin_channels', 't'); - q("UPDATE channel SET channel_pageflags = ( channel_pageflags ^ %d ) where channel_id = %d", + q("UPDATE channel SET channel_pageflags = ( channel_pageflags & ~%d ) where channel_id = %d", intval(PAGE_CENSORED), intval( $uid ) ); @@ -828,7 +829,7 @@ function admin_page_channels(&$a){ /* get channels */ - $total = q("SELECT count(*) as total FROM channel where not (channel_pageflags & %d)", + $total = q("SELECT count(*) as total FROM channel where not (channel_pageflags & %d)>0", intval(PAGE_REMOVED) ); if($total) { @@ -838,10 +839,10 @@ function admin_page_channels(&$a){ $order = " order by channel_name asc "; - $channels = q("SELECT * from channel where not ( channel_pageflags & %d ) $order limit %d , %d ", + $channels = q("SELECT * from channel where not ( channel_pageflags & %d )>0 $order limit %d offset %d ", intval(PAGE_REMOVED), - intval($a->pager['start']), - intval($a->pager['itemspage']) + intval($a->pager['itemspage']), + intval($a->pager['start']) ); if($channels) { @@ -1295,7 +1296,7 @@ readable."); function admin_page_profs_post(&$a) { if($_REQUEST['id']) { - $r = q("update profdef set field_name = '%s', field_type = '%s', field_desc = '%s' field_help = '%s', field_inputs = '%s' where id = %d limit 1", + $r = q("update profdef set field_name = '%s', field_type = '%s', field_desc = '%s' field_help = '%s', field_inputs = '%s' where id = %d", dbesc($_REQUEST['field_name']), dbesc($_REQUEST['field_type']), dbesc($_REQUEST['field_desc']), @@ -1323,7 +1324,7 @@ function admin_page_profs_post(&$a) { function admin_page_profs(&$a) { if((argc() > 3) && argv(2) == 'drop' && intval(argv(3))) { - $r = q("delete from profdef where id = %d limit 1", + $r = q("delete from profdef where id = %d", intval(argv(3)) ); // remove from allowed fields diff --git a/mod/channel.php b/mod/channel.php index e4a7173c0..8d6b2a169 100644 --- a/mod/channel.php +++ b/mod/channel.php @@ -141,17 +141,17 @@ function channel_content(&$a, $update = 0, $load = false) { if(($update) && (! $load)) { if ($mid) { $r = q("SELECT parent AS item_id from item where mid = '%s' and uid = %d AND item_restrict = 0 - AND (item_flags & %d) AND (item_flags & %d) $sql_extra limit 1", + AND (item_flags & %d)>0 AND (item_flags & %d)>0 $sql_extra limit 1", dbesc($mid), intval($a->profile['profile_uid']), intval(ITEM_WALL), intval(ITEM_UNSEEN) ); } else { - $r = q("SELECT distinct parent AS `item_id` from item + $r = q("SELECT distinct parent AS `item_id`, created from item left join abook on item.author_xchan = abook.abook_xchan WHERE uid = %d AND item_restrict = 0 - AND (item_flags & %d) AND ( item_flags & %d ) + AND (item_flags & %d)>0 AND ( item_flags & %d )>0 AND ((abook.abook_flags & %d) = 0 or abook.abook_flags is null) $sql_extra ORDER BY created DESC", @@ -179,12 +179,12 @@ function channel_content(&$a, $update = 0, $load = false) { $itemspage = get_pconfig(local_user(),'system','itemspage'); $a->set_pager_itemspage(((intval($itemspage)) ? $itemspage : 20)); - $pager_sql = sprintf(" LIMIT %d, %d ",intval($a->pager['start']), intval($a->pager['itemspage'])); + $pager_sql = sprintf(" LIMIT %d OFFSET %d ", intval($a->pager['itemspage']), intval($a->pager['start'])); if($load || ($_COOKIE['jsAvailable'] != 1)) { if ($mid) { $r = q("SELECT parent AS item_id from item where mid = '%s' and uid = %d AND item_restrict = 0 - AND (item_flags & %d) $sql_extra limit 1", + AND (item_flags & %d)>0 $sql_extra limit 1", dbesc($mid), intval($a->profile['profile_uid']), intval(ITEM_WALL) @@ -194,10 +194,10 @@ function channel_content(&$a, $update = 0, $load = false) { } } else { - $r = q("SELECT distinct id AS item_id FROM item + $r = q("SELECT distinct id AS item_id, created FROM item left join abook on item.author_xchan = abook.abook_xchan WHERE uid = %d AND item_restrict = 0 - AND (item_flags & %d) and (item_flags & %d) + AND (item_flags & %d)>0 and (item_flags & %d)>0 AND ((abook.abook_flags & %d) = 0 or abook.abook_flags is null) $sql_extra $sql_extra2 ORDER BY created DESC $pager_sql ", @@ -283,8 +283,8 @@ function channel_content(&$a, $update = 0, $load = false) { if($is_owner) { - $r = q("UPDATE item SET item_flags = (item_flags ^ %d) - WHERE (item_flags & %d) AND (item_flags & %d) AND uid = %d ", + $r = q("UPDATE item SET item_flags = (item_flags & ~%d) + WHERE (item_flags & %d)>0 AND (item_flags & %d)>0 AND uid = %d ", intval(ITEM_UNSEEN), intval(ITEM_UNSEEN), intval(ITEM_WALL), diff --git a/mod/chatsvc.php b/mod/chatsvc.php index 43aa3d3c0..44225e6dd 100644 --- a/mod/chatsvc.php +++ b/mod/chatsvc.php @@ -73,7 +73,7 @@ function chatsvc_content(&$a) { intval($a->data['chat']['uid']) ); - $r = q("update chatpresence set cp_status = '%s', cp_last = '%s' where cp_room = %d and cp_xchan = '%s' and cp_client = '%s' limit 1", + $r = q("update chatpresence set cp_status = '%s', cp_last = '%s' where cp_room = %d and cp_xchan = '%s' and cp_client = '%s'", dbesc($status), dbesc(datetime_convert()), intval($room_id), @@ -141,7 +141,7 @@ function chatsvc_content(&$a) { } } - $r = q("update chatpresence set cp_last = '%s' where cp_room = %d and cp_xchan = '%s' and cp_client = '%s' limit 1", + $r = q("update chatpresence set cp_last = '%s' where cp_room = %d and cp_xchan = '%s' and cp_client = '%s'", dbesc(datetime_convert()), intval($a->data['chat']['room_id']), dbesc(get_observer_hash()), diff --git a/mod/connect.php b/mod/connect.php index f7748bcaf..5b1f258ad 100644 --- a/mod/connect.php +++ b/mod/connect.php @@ -38,7 +38,7 @@ function connect_post(&$a) { $text = escape_tags($_POST['text']); if($has_premium != $premium) { - $r = q("update channel set channel_pageflags = ( channel_pageflags ^ %d ) where channel_id = %d limit 1", + $r = q("update channel set channel_pageflags = ( channel_pageflags & ~%d ) where channel_id = %d", intval(PAGE_PREMIUM), intval(local_user()) ); diff --git a/mod/connections.php b/mod/connections.php index 0dfcfebf1..70e28913a 100644 --- a/mod/connections.php +++ b/mod/connections.php @@ -81,7 +81,7 @@ function connections_post(&$a) { } $r = q("UPDATE abook SET abook_profile = '%s', abook_my_perms = %d , abook_closeness = %d, abook_flags = %d - where abook_id = %d AND abook_channel = %d LIMIT 1", + where abook_id = %d AND abook_channel = %d", dbesc($profile_id), intval($abook_my_perms), intval($closeness), @@ -213,7 +213,7 @@ function connections_content(&$a) { nav_set_selected('intros'); break; case 'ifpending': - $r = q("SELECT COUNT(abook.abook_id) AS total FROM abook left join xchan on abook.abook_xchan = xchan.xchan_hash where abook_channel = %d and (abook_flags & %d) and not ((abook_flags & %d) or (xchan_flags & %d))", + $r = q("SELECT COUNT(abook.abook_id) AS total FROM abook left join xchan on abook.abook_xchan = xchan.xchan_hash where abook_channel = %d and (abook_flags & %d)>0 and not ((abook_flags & %d)>0 or (xchan_flags & %d)>0)", intval(local_user()), intval(ABOOK_FLAG_PENDING), intval(ABOOK_FLAG_SELF|ABOOK_FLAG_IGNORED), @@ -250,13 +250,13 @@ function connections_content(&$a) { } - $sql_extra = (($search_flags) ? " and ( abook_flags & " . $search_flags . " ) " : ""); + $sql_extra = (($search_flags) ? " and ( abook_flags & " . $search_flags . " )>0 " : ""); if(argv(1) === 'pending') - $sql_extra .= " and not ( abook_flags & " . ABOOK_FLAG_IGNORED . " ) "; + $sql_extra .= " and not ( abook_flags & " . ABOOK_FLAG_IGNORED . " )>0 "; } else { - $sql_extra = " and not ( abook_flags & " . ABOOK_FLAG_BLOCKED . " ) "; + $sql_extra = " and not ( abook_flags & " . ABOOK_FLAG_BLOCKED . " )>0 "; $unblocked = true; } @@ -342,7 +342,7 @@ function connections_content(&$a) { } $r = q("SELECT COUNT(abook.abook_id) AS total FROM abook left join xchan on abook.abook_xchan = xchan.xchan_hash - where abook_channel = %d and not (abook_flags & %d) and not (xchan_flags & %d ) $sql_extra $sql_extra2 ", + where abook_channel = %d and not (abook_flags & %d)>0 and not (xchan_flags & %d )>0 $sql_extra $sql_extra2 ", intval(local_user()), intval(ABOOK_FLAG_SELF), intval(XCHAN_FLAGS_DELETED|XCHAN_FLAGS_ORPHAN) @@ -353,12 +353,12 @@ function connections_content(&$a) { } $r = q("SELECT abook.*, xchan.* FROM abook left join xchan on abook.abook_xchan = xchan.xchan_hash - WHERE abook_channel = %d and not (abook_flags & %d) and not ( xchan_flags & %d) $sql_extra $sql_extra2 ORDER BY xchan_name LIMIT %d , %d ", + WHERE abook_channel = %d and not (abook_flags & %d)>0 and not ( xchan_flags & %d)>0 $sql_extra $sql_extra2 ORDER BY xchan_name LIMIT %d OFFSET %d ", intval(local_user()), intval(ABOOK_FLAG_SELF), intval(XCHAN_FLAGS_DELETED|XCHAN_FLAGS_ORPHAN), - intval($a->pager['start']), - intval($a->pager['itemspage']) + intval($a->pager['itemspage']), + intval($a->pager['start']) ); $contacts = array(); diff --git a/mod/connedit.php b/mod/connedit.php index b10d9f3b8..dd69b3d31 100644 --- a/mod/connedit.php +++ b/mod/connedit.php @@ -109,7 +109,7 @@ function connedit_post(&$a) { } $r = q("UPDATE abook SET abook_profile = '%s', abook_my_perms = %d , abook_closeness = %d, abook_flags = %d - where abook_id = %d AND abook_channel = %d LIMIT 1", + where abook_id = %d AND abook_channel = %d", dbesc($profile_id), intval($abook_my_perms), intval($closeness), @@ -292,7 +292,7 @@ function connedit_content(&$a) { $cmd = argv(2); $orig_record = q("SELECT abook.*, xchan.* FROM abook left join xchan on abook_xchan = xchan_hash - WHERE abook_id = %d AND abook_channel = %d AND NOT ( abook_flags & %d ) LIMIT 1", + WHERE abook_id = %d AND abook_channel = %d AND NOT ( abook_flags & %d )>0 LIMIT 1", intval($contact_id), intval(local_user()), intval(ABOOK_FLAG_SELF) diff --git a/mod/contactgroup.php b/mod/contactgroup.php index cac02a65d..4515c4c4c 100644 --- a/mod/contactgroup.php +++ b/mod/contactgroup.php @@ -9,7 +9,7 @@ function contactgroup_content(&$a) { } if((argc() > 2) && (intval(argv(1))) && (argv(2))) { - $r = q("SELECT abook_xchan from abook where abook_xchan = '%s' and abook_channel = %d and not ( abook_flags & %d ) limit 1", + $r = q("SELECT abook_xchan from abook where abook_xchan = '%s' and abook_channel = %d and not ( abook_flags & %d )>0 limit 1", dbesc(base64url_decode(argv(2))), intval(local_user()), intval(ABOOK_FLAG_SELF) diff --git a/mod/delegate.php b/mod/delegate.php index e582b9387..c78898b7f 100644 --- a/mod/delegate.php +++ b/mod/delegate.php @@ -42,7 +42,7 @@ function delegate_content(&$a) { if(x($_SESSION,'submanage') && intval($_SESSION['submanage'])) goaway($a->get_baseurl() . '/delegate'); - q("delete from manage where uid = %d and mid = %d limit 1", + q("delete from manage where uid = %d and mid = %d", intval($a->argv[2]), intval(local_user()) ); diff --git a/mod/dirsearch.php b/mod/dirsearch.php index d8f611e6a..485f99b38 100644 --- a/mod/dirsearch.php +++ b/mod/dirsearch.php @@ -151,16 +151,16 @@ function dirsearch_content(&$a) { } - $safesql = (($safe > 0) ? " and not ( xchan_flags & " . intval(XCHAN_FLAGS_CENSORED|XCHAN_FLAGS_SELFCENSORED) . " ) " : ''); + $safesql = (($safe > 0) ? " and not ( xchan_flags & " . intval(XCHAN_FLAGS_CENSORED|XCHAN_FLAGS_SELFCENSORED) . " )>0 " : ''); if($safe < 0) - $safesql = " and ( xchan_flags & " . intval(XCHAN_FLAGS_CENSORED|XCHAN_FLAGS_SELFCENSORED) . " ) "; + $safesql = " and ( xchan_flags & " . intval(XCHAN_FLAGS_CENSORED|XCHAN_FLAGS_SELFCENSORED) . " )>0 "; if($limit) $qlimit = " LIMIT $limit "; else { $qlimit = " LIMIT " . intval($startrec) . " , " . intval($perpage); if($return_total) { - $r = q("SELECT COUNT(xchan_hash) AS `total` FROM xchan left join xprof on xchan_hash = xprof_hash where $logic $sql_extra and xchan_network = 'zot' and not ( xchan_flags & %d) and not ( xchan_flags & %d ) and not ( xchan_flags & %d ) $safesql ", + $r = q("SELECT COUNT(xchan_hash) AS `total` FROM xchan left join xprof on xchan_hash = xprof_hash where $logic $sql_extra and xchan_network = 'zot' and not ( xchan_flags & %d)>0 and not ( xchan_flags & %d )>0 and not ( xchan_flags & %d )>0 $safesql ", intval(XCHAN_FLAGS_HIDDEN), intval(XCHAN_FLAGS_ORPHAN), intval(XCHAN_FLAGS_DELETED) @@ -205,7 +205,7 @@ function dirsearch_content(&$a) { json_return_and_die($spkt); } else { - $r = q("SELECT xchan.*, xprof.* from xchan left join xprof on xchan_hash = xprof_hash where ( $logic $sql_extra ) and xchan_network = 'zot' and not ( xchan_flags & %d ) and not ( xchan_flags & %d ) and not ( xchan_flags & %d ) $safesql $order $qlimit ", + $r = q("SELECT xchan.*, xprof.* from xchan left join xprof on xchan_hash = xprof_hash where ( $logic $sql_extra ) and xchan_network = 'zot' and not ( xchan_flags & %d )>0 and not ( xchan_flags & %d )>0 and not ( xchan_flags & %d )>0 $safesql $order $qlimit ", intval(XCHAN_FLAGS_HIDDEN), intval(XCHAN_FLAGS_ORPHAN), intval(XCHAN_FLAGS_DELETED) diff --git a/mod/display.php b/mod/display.php index be06c1e82..ece406543 100644 --- a/mod/display.php +++ b/mod/display.php @@ -149,7 +149,7 @@ function display_content(&$a, $update = 0, $load = false) { $updateable = false; - $pager_sql = sprintf(" LIMIT %d, %d ",intval($a->pager['start']), intval($a->pager['itemspage'])); + $pager_sql = sprintf(" LIMIT %d OFFSET %d ", intval($a->pager['itemspage']),intval($a->pager['start'])); if($load || ($_COOKIE['jsAvailable'] != 1)) { $r = null; @@ -189,7 +189,7 @@ function display_content(&$a, $update = 0, $load = false) { and owner_xchan in ( " . stream_perms_xchans(($observer_hash) ? (PERMS_NETWORK|PERMS_PUBLIC) : PERMS_PUBLIC) . " )) OR owner_xchan = '%s') $sql_extra ) - group by mid limit 1", + limit 1", dbesc($target_item['parent_mid']), dbesc($sys['xchan_hash']) ); @@ -228,8 +228,8 @@ function display_content(&$a, $update = 0, $load = false) { } if($updateable) { - $x = q("UPDATE item SET item_flags = ( item_flags ^ %d ) - WHERE (item_flags & %d) AND uid = %d and parent = %d ", + $x = q("UPDATE item SET item_flags = ( item_flags & ~%d ) + WHERE (item_flags & %d)>0 AND uid = %d and parent = %d ", intval(ITEM_UNSEEN), intval(ITEM_UNSEEN), intval(local_user()), diff --git a/mod/events.php b/mod/events.php index 513899308..009afbcb8 100755 --- a/mod/events.php +++ b/mod/events.php @@ -199,14 +199,14 @@ function events_content(&$a) { nav_set_selected('all_events'); if((argc() > 2) && (argv(1) === 'ignore') && intval(argv(2))) { - $r = q("update event set ignore = 1 where id = %d and uid = %d limit 1", + $r = q("update event set ignore = 1 where id = %d and uid = %d", intval(argv(2)), intval(local_user()) ); } if((argc() > 2) && (argv(1) === 'unignore') && intval(argv(2))) { - $r = q("update event set ignore = 0 where id = %d and uid = %d limit 1", + $r = q("update event set ignore = 0 where id = %d and uid = %d", intval(argv(2)), intval(local_user()) ); @@ -330,8 +330,8 @@ function events_content(&$a) { $r = q("SELECT event.*, item.plink, item.item_flags, item.author_xchan, item.owner_xchan from event left join item on event_hash = resource_id where resource_type = 'event' and event.uid = %d and event.ignore = %d - AND (( `adjust` = 0 AND ( `finish` >= '%s' or nofinish ) AND `start` <= '%s' ) - OR ( `adjust` = 1 AND ( `finish` >= '%s' or nofinish ) AND `start` <= '%s' )) ", + AND (( `adjust` = 0 AND ( `finish` >= '%s' or nofinish = 1 ) AND `start` <= '%s' ) + OR ( `adjust` = 1 AND ( `finish` >= '%s' or nofinish = 1 ) AND `start` <= '%s' )) ", intval(local_user()), intval($ignored), dbesc($start), diff --git a/mod/filer.php b/mod/filer.php index 3340fc999..9a409177c 100644 --- a/mod/filer.php +++ b/mod/filer.php @@ -27,7 +27,7 @@ function filer_content(&$a) { intval(local_user()) ); if($r) { - $x = q("update item set item_flags = ( item_flags | %d ) where id = %d and uid = %d limit 1", + $x = q("update item set item_flags = ( item_flags | %d ) where id = %d and uid = %d", intval(ITEM_RETAINED), intval($r[0]['parent']), intval(local_user()) diff --git a/mod/filerm.php b/mod/filerm.php index a37e80b31..900cfe60b 100644 --- a/mod/filerm.php +++ b/mod/filerm.php @@ -18,7 +18,7 @@ function filerm_content(&$a) { logger('filerm: tag ' . $term . ' item ' . $item_id); if($item_id && strlen($term)) { - $r = q("delete from term where uid = %d and type = %d and oid = %d and term = '%s' limit 1", + $r = q("delete from term where uid = %d and type = %d and oid = %d and term = '%s'", intval(local_user()), intval(($category) ? TERM_CATEGORY : TERM_FILE), intval($item_id), diff --git a/mod/fsuggest.php b/mod/fsuggest.php index 8b6f077d3..2f4f9606b 100644 --- a/mod/fsuggest.php +++ b/mod/fsuggest.php @@ -52,7 +52,7 @@ function fsuggest_post(&$a) { ); if(count($r)) { $fsuggest_id = $r[0]['id']; - q("UPDATE `fsuggest` SET `note` = '%s' WHERE `id` = %d AND `uid` = %d LIMIT 1", + q("UPDATE `fsuggest` SET `note` = '%s' WHERE `id` = %d AND `uid` = %d", dbesc($note), intval($fsuggest_id), intval(local_user()) diff --git a/mod/group.php b/mod/group.php index 9b90b1a1a..f67623a83 100644 --- a/mod/group.php +++ b/mod/group.php @@ -44,7 +44,7 @@ function group_post(&$a) { $public = intval($_POST['public']); if((strlen($groupname)) && (($groupname != $group['name']) || ($public != $group['visible']))) { - $r = q("UPDATE `groups` SET `name` = '%s', visible = %d WHERE `uid` = %d AND `id` = %d LIMIT 1", + $r = q("UPDATE `groups` SET `name` = '%s', visible = %d WHERE `uid` = %d AND `id` = %d", dbesc($groupname), intval($public), intval(local_user()), @@ -117,7 +117,7 @@ function group_content(&$a) { check_form_security_token_ForbiddenOnErr('group_member_change', 't'); - $r = q("SELECT abook_xchan from abook left join xchan on abook_xchan = xchan_hash where abook_xchan = '%s' and abook_channel = %d and not (xchan_flags & %d) and not (abook_flags & %d) and not (abook_flags & %d) limit 1", + $r = q("SELECT abook_xchan from abook left join xchan on abook_xchan = xchan_hash where abook_xchan = '%s' and abook_channel = %d and not (xchan_flags & %d)>0 and not (abook_flags & %d)>0 and not (abook_flags & %d)>0 limit 1", dbesc(base64url_decode(argv(2))), intval(local_user()), intval(XCHAN_FLAGS_DELETED), @@ -211,7 +211,7 @@ function group_content(&$a) { group_rmv_member(local_user(),$group['name'],$member['xchan_hash']); } - $r = q("SELECT abook.*, xchan.* FROM `abook` left join xchan on abook_xchan = xchan_hash WHERE `abook_channel` = %d AND not (abook_flags & %d) and not (xchan_flags & %d) and not (abook_flags & %d) order by xchan_name asc", + $r = q("SELECT abook.*, xchan.* FROM `abook` left join xchan on abook_xchan = xchan_hash WHERE `abook_channel` = %d AND not (abook_flags & %d)>0 and not (xchan_flags & %d)>0 and not (abook_flags & %d)>0 order by xchan_name asc", intval(local_user()), intval(ABOOK_FLAG_BLOCKED), intval(XCHAN_FLAGS_DELETED), diff --git a/mod/import.php b/mod/import.php index 44dfcc38d..6cb3767a6 100644 --- a/mod/import.php +++ b/mod/import.php @@ -215,7 +215,7 @@ function import_post(&$a) { // reset the original primary hubloc if it is being seized if($seize) - $r = q("update hubloc set hubloc_flags = (hubloc_flags ^ %d) where (hubloc_flags & %d) and hubloc_hash = '%s' and hubloc_url != '%s' ", + $r = q("update hubloc set hubloc_flags = (hubloc_flags & ~%d) where (hubloc_flags & %d)>0 and hubloc_hash = '%s' and hubloc_url != '%s' ", intval(HUBLOC_FLAGS_PRIMARY), intval(HUBLOC_FLAGS_PRIMARY), dbesc($channel['channel_hash']), @@ -228,7 +228,7 @@ function import_post(&$a) { // replace our existing xchan if we're seizing control - $r = q("delete from xchan where xchan_hash = '%s' limit 1", + $r = q("delete from xchan where xchan_hash = '%s'", dbesc($channel['channel_hash']) ); @@ -278,7 +278,7 @@ function import_post(&$a) { $photodate = $xchan['xchan_photo_date']; $r = q("update xchan set xchan_photo_l = '%s', xchan_photo_m = '%s', xchan_photo_s = '%s', xchan_photo_mimetype = '%s', xchan_photo_date = '%s' - where xchan_hash = '%s' limit 1", + where xchan_hash = '%s'", dbesc($photos[0]), dbesc($photos[1]), dbesc($photos[2]), diff --git a/mod/item.php b/mod/item.php index 3dea8809c..026240196 100644 --- a/mod/item.php +++ b/mod/item.php @@ -877,7 +877,7 @@ function item_post(&$a) { // They will show up as people comment on them. if($parent_item['item_restrict'] & ITEM_HIDDEN) { - $r = q("UPDATE `item` SET `item_restrict` = %d WHERE `id` = %d LIMIT 1", + $r = q("UPDATE `item` SET `item_restrict` = %d WHERE `id` = %d", intval($parent_item['item_restrict'] - ITEM_HIDDEN), intval($parent_item['id']) ); @@ -1291,7 +1291,7 @@ function fix_attached_photo_permissions($uid,$xchan_hash,$body, $private = (($str_contact_allow || $str_group_allow || $str_contact_deny || $str_group_deny) ? true : false); $r = q("UPDATE item SET allow_cid = '%s', allow_gid = '%s', deny_cid = '%s', deny_gid = '%s', item_private = %d - WHERE id = %d AND uid = %d limit 1", + WHERE id = %d AND uid = %d", dbesc($str_contact_allow), dbesc($str_group_allow), dbesc($str_contact_deny), @@ -1341,7 +1341,7 @@ function item_check_service_class($channel_id,$iswebpage) { if ($iswebpage) { $r = q("select count(i.id) as total from item i right join channel c on (i.author_xchan=c.channel_hash and i.uid=c.channel_id ) - and i.parent=i.id and (i.item_restrict & %d) and not (i.item_restrict & %d) and i.uid= %d ", + and i.parent=i.id and (i.item_restrict & %d)>0 and not (i.item_restrict & %d)>0 and i.uid= %d ", intval(ITEM_WEBPAGE), intval(ITEM_DELETED), intval($channel_id) diff --git a/mod/like.php b/mod/like.php index f4fd33787..b56611197 100755 --- a/mod/like.php +++ b/mod/like.php @@ -276,7 +276,7 @@ function like_content(&$a) { // Already liked/disliked it, delete it - $r = q("UPDATE item SET item_restrict = ( item_restrict ^ %d ), changed = '%s' WHERE id = %d LIMIT 1", + $r = q("UPDATE item SET item_restrict = ( item_restrict & ~%d ), changed = '%s' WHERE id = %d", intval(ITEM_DELETED), dbesc(datetime_convert()), intval($like_item['id']) @@ -332,7 +332,7 @@ function like_content(&$a) { // if this was a linked photo and was hidden, unhide it. if($item['item_restrict'] & ITEM_HIDDEN) { - $r = q("update item set item_restrict = (item_restrict ^ %d) where id = %d limit 1", + $r = q("update item set item_restrict = (item_restrict ^ %d) where id = %d", intval(ITEM_HIDDEN), intval($item['id']) ); diff --git a/mod/locs.php b/mod/locs.php index 95aa7a579..0bd7f0f84 100644 --- a/mod/locs.php +++ b/mod/locs.php @@ -47,12 +47,12 @@ function locs_post(&$a) { notice( t('Location not found.') . EOL); return; } - $r = q("update hubloc set hubloc_flags = (hubloc_flags ^ %d) where (hubloc_flags & %d) and hubloc_hash = '%s' ", + $r = q("update hubloc set hubloc_flags = (hubloc_flags & ~%d) where (hubloc_flags & %d)>0 and hubloc_hash = '%s' ", intval(HUBLOC_FLAGS_PRIMARY), intval(HUBLOC_FLAGS_PRIMARY), dbesc($channel['channel_hash']) ); - $r = q("update hubloc set hubloc_flags = (hubloc_flags & %d) where hubloc_id = %d and hubloc_hash = '%s' limit 1", + $r = q("update hubloc set hubloc_flags = (hubloc_flags & %d) where hubloc_id = %d and hubloc_hash = '%s'", intval(HUBLOC_FLAGS_PRIMARY), intval($hubloc_id), dbesc($channel['channel_hash']) @@ -78,7 +78,7 @@ function locs_post(&$a) { notice( t('Primary location cannot be removed.') . EOL); return; } - $r = q("update hubloc set hubloc_flags = (hubloc_flags & %d) where hubloc_id = %d and hubloc_hash = '%s' limit 1", + $r = q("update hubloc set hubloc_flags = (hubloc_flags & %d) where hubloc_id = %d and hubloc_hash = '%s'", intval(HUBLOC_FLAGS_DELETED), intval($hubloc_id), dbesc($channel['channel_hash']) diff --git a/mod/lostpass.php b/mod/lostpass.php index dd7c7a7d5..3269128f1 100644 --- a/mod/lostpass.php +++ b/mod/lostpass.php @@ -21,7 +21,7 @@ function lostpass_post(&$a) { $hash = random_string(); - $r = q("UPDATE account SET account_reset = '%s' WHERE account_id = %d LIMIT 1", + $r = q("UPDATE account SET account_reset = '%s' WHERE account_id = %d", dbesc($hash), intval($aid) ); @@ -73,7 +73,7 @@ function lostpass_content(&$a) { $salt = random_string(32); $password_encoded = hash('whirlpool', $salt . $new_password); - $r = q("UPDATE account SET account_salt = '%s', account_password = '%s', account_reset = '' where account_id = %d limit 1", + $r = q("UPDATE account SET account_salt = '%s', account_password = '%s', account_reset = '' where account_id = %d", dbesc($salt), dbesc($password_encoded), intval($aid) diff --git a/mod/mail.php b/mod/mail.php index f4897149a..dacb181c5 100644 --- a/mod/mail.php +++ b/mod/mail.php @@ -141,7 +141,7 @@ function mail_content(&$a) { if(! intval(argv(2))) return; $cmd = argv(1); - $r = q("update mail set mail_flags = mail_flags | %d where id = %d and channel_id = %d limit 1", + $r = q("update mail set mail_flags = mail_flags | %d where id = %d and channel_id = %d", intval(MAIL_RECALLED), intval(argv(2)), intval(local_user()) diff --git a/mod/manage.php b/mod/manage.php index 1920967e6..00c6db7f0 100644 --- a/mod/manage.php +++ b/mod/manage.php @@ -17,7 +17,7 @@ function manage_content(&$a) { intval(get_account_id()) ); if($r) { - q("update account set account_default_channel = %d where account_id = %d limit 1", + q("update account set account_default_channel = %d where account_id = %d", intval($change_channel), intval(get_account_id()) ); @@ -36,7 +36,7 @@ function manage_content(&$a) { $channels = null; if(local_user()) { - $r = q("select channel.*, xchan.* from channel left join xchan on channel.channel_hash = xchan.xchan_hash where channel.channel_account_id = %d and not ( channel_pageflags & %d ) order by channel_name ", + $r = q("select channel.*, xchan.* from channel left join xchan on channel.channel_hash = xchan.xchan_hash where channel.channel_account_id = %d and not ( channel_pageflags & %d )>0 order by channel_name ", intval(get_account_id()), intval(PAGE_REMOVED) ); @@ -55,7 +55,7 @@ function manage_content(&$a) { $c = q("SELECT id, item_restrict, item_flags FROM item - WHERE (item_restrict = %d) and ( item_flags & %d ) and uid = %d", + WHERE (item_restrict = %d) and ( item_flags & %d )>0 and uid = %d", intval(ITEM_VISIBLE), intval(ITEM_UNSEEN), intval($channels[$x]['channel_id']) @@ -71,7 +71,7 @@ function manage_content(&$a) { } - $intr = q("SELECT COUNT(abook.abook_id) AS total FROM abook left join xchan on abook.abook_xchan = xchan.xchan_hash where abook_channel = %d and (abook_flags & %d) and not ((abook_flags & %d) or (xchan_flags & %d))", + $intr = q("SELECT COUNT(abook.abook_id) AS total FROM abook left join xchan on abook.abook_xchan = xchan.xchan_hash where abook_channel = %d and (abook_flags & %d)>0 and not ((abook_flags & %d)>0 or (xchan_flags & %d)>0)", intval($channels[$x]['channel_id']), intval(ABOOK_FLAG_PENDING), intval(ABOOK_FLAG_SELF|ABOOK_FLAG_IGNORED), @@ -82,7 +82,7 @@ function manage_content(&$a) { $channels[$x]['intros'] = intval($intr[0]['total']); - $mails = q("SELECT count(id) as total from mail WHERE channel_id = %d AND not (mail_flags & %d) and from_xchan != '%s' ", + $mails = q("SELECT count(id) as total from mail WHERE channel_id = %d AND not (mail_flags & %d)>0 and from_xchan != '%s' ", intval($channels[$x]['channel_id']), intval(MAIL_SEEN), dbesc($channels[$x]['channel_hash']) @@ -127,7 +127,7 @@ function manage_content(&$a) { } } - $r = q("select count(channel_id) as total from channel where channel_account_id = %d and not ( channel_pageflags & %d )", + $r = q("select count(channel_id) as total from channel where channel_account_id = %d and not ( channel_pageflags & %d )>0", intval(get_account_id()), intval(PAGE_REMOVED) ); diff --git a/mod/network.php b/mod/network.php index 0bad366d4..da020c389 100644 --- a/mod/network.php +++ b/mod/network.php @@ -150,12 +150,12 @@ function network_content(&$a, $update = 0, $load = false) { $sql_options = (($star) - ? " and (item_flags & " . intval(ITEM_STARRED) . ")" + ? " and (item_flags & " . intval(ITEM_STARRED) . ")>0" : ''); $sql_nets = ''; - $sql_extra = " AND `item`.`parent` IN ( SELECT `parent` FROM `item` WHERE (item_flags & " . intval(ITEM_THREAD_TOP) . ") $sql_options ) "; + $sql_extra = " AND `item`.`parent` IN ( SELECT `parent` FROM `item` WHERE (item_flags & " . intval(ITEM_THREAD_TOP) . ")>0 $sql_options ) "; if($group) { $contact_str = ''; @@ -184,7 +184,7 @@ function network_content(&$a, $update = 0, $load = false) { elseif($cid) { - $r = q("SELECT abook.*, xchan.* from abook left join xchan on abook_xchan = xchan_hash where abook_id = %d and abook_channel = %d and not ( abook_flags & " . intval(ABOOK_FLAG_BLOCKED) . ") limit 1", + $r = q("SELECT abook.*, xchan.* from abook left join xchan on abook_xchan = xchan_hash where abook_id = %d and abook_channel = %d and not ( abook_flags & " . intval(ABOOK_FLAG_BLOCKED) . ")>0 limit 1", intval($cid), intval(local_user()) ); @@ -264,7 +264,7 @@ function network_content(&$a, $update = 0, $load = false) { } if($conv) { - $sql_extra .= sprintf(" AND parent IN (SELECT distinct(parent) from item where ( author_xchan like '%s' or ( item_flags & %d ))) ", + $sql_extra .= sprintf(" AND parent IN (SELECT distinct(parent) from item where ( author_xchan like '%s' or ( item_flags & %d )>0)) ", dbesc(protect_sprintf($channel['channel_hash'])), intval(ITEM_MENTIONSME) ); @@ -279,7 +279,7 @@ function network_content(&$a, $update = 0, $load = false) { else { $itemspage = get_pconfig(local_user(),'system','itemspage'); $a->set_pager_itemspage(((intval($itemspage)) ? $itemspage : 20)); - $pager_sql = sprintf(" LIMIT %d, %d ",intval($a->pager['start']), intval($a->pager['itemspage'])); + $pager_sql = sprintf(" LIMIT %d OFFSET %d ", intval($a->pager['itemspage']), intval($a->pager['start'])); } @@ -314,7 +314,7 @@ function network_content(&$a, $update = 0, $load = false) { $uids = " and item.uid = " . local_user() . " "; } - $simple_update = (($update) ? " and ( item.item_flags & " . intval(ITEM_UNSEEN) . " ) " : ''); + $simple_update = (($update) ? " and ( item.item_flags & " . intval(ITEM_UNSEEN) . " )>0 " : ''); // This fixes a very subtle bug so I'd better explain it. You wake up in the morning or return after a day // or three and look at your matrix page - after opening up your browser. The first page loads just as it @@ -336,7 +336,7 @@ function network_content(&$a, $update = 0, $load = false) { if($nouveau && $load) { // "New Item View" - show all items unthreaded in reverse created date order - $items = q("SELECT `item`.*, `item`.`id` AS `item_id` FROM `item` + $items = q("SELECT `item`.*, `item`.`id` AS `item_id`, received FROM `item` WHERE true $uids AND item_restrict = 0 $simple_update $sql_extra $sql_nets @@ -364,13 +364,13 @@ function network_content(&$a, $update = 0, $load = false) { // Fetch a page full of parent items for this page - $r = q("SELECT distinct item.id AS item_id FROM item + $r = q("SELECT distinct item.id AS item_id, $ordering FROM item left join abook on item.author_xchan = abook.abook_xchan WHERE true $uids AND item.item_restrict = 0 AND item.parent = item.id and ((abook.abook_flags & %d) = 0 or abook.abook_flags is null) $sql_extra3 $sql_extra $sql_nets - ORDER BY item.$ordering DESC $pager_sql ", + ORDER BY $ordering DESC $pager_sql ", intval(ABOOK_FLAG_BLOCKED) ); @@ -417,8 +417,8 @@ function network_content(&$a, $update = 0, $load = false) { } if(($update_unseen) && (! $firehose)) - $r = q("UPDATE `item` SET item_flags = ( item_flags ^ %d) - WHERE (item_flags & %d) AND `uid` = %d $update_unseen ", + $r = q("UPDATE `item` SET item_flags = ( item_flags & ~%d) + WHERE (item_flags & %d)>0 AND `uid` = %d $update_unseen ", intval(ITEM_UNSEEN), intval(ITEM_UNSEEN), intval(local_user()) diff --git a/mod/notifications.php b/mod/notifications.php index 09f89e88a..5507ee7e2 100644 --- a/mod/notifications.php +++ b/mod/notifications.php @@ -33,7 +33,7 @@ function notifications_post(&$a) { $fid = $r[0]['fid']; if($_POST['submit'] == t('Discard')) { - $r = q("DELETE FROM `intro` WHERE `id` = %d LIMIT 1", + $r = q("DELETE FROM `intro` WHERE `id` = %d", intval($intro_id) ); if(! $fid) { @@ -41,7 +41,7 @@ function notifications_post(&$a) { // The check for blocked and pending is in case the friendship was already approved // and we just want to get rid of the now pointless notification - $r = q("DELETE FROM `contact` WHERE `id` = %d AND `uid` = %d AND `self` = 0 AND `blocked` = 1 AND `pending` = 1 LIMIT 1", + $r = q("DELETE FROM `contact` WHERE `id` = %d AND `uid` = %d AND `self` = 0 AND `blocked` = 1 AND `pending` = 1", intval($contact_id), intval(local_user()) ); @@ -49,7 +49,7 @@ function notifications_post(&$a) { goaway($a->get_baseurl(true) . '/notifications/intros'); } if($_POST['submit'] == t('Ignore')) { - $r = q("UPDATE `intro` SET `ignore` = 1 WHERE `id` = %d LIMIT 1", + $r = q("UPDATE `intro` SET `ignore` = 1 WHERE `id` = %d", intval($intro_id)); goaway($a->get_baseurl(true) . '/notifications/intros'); } diff --git a/mod/openid.php b/mod/openid.php index ce7fe22ba..9752db440 100644 --- a/mod/openid.php +++ b/mod/openid.php @@ -159,7 +159,7 @@ function openid_content(&$a) { $photos = import_profile_photo($pphoto,$url); if($photos) { $z = q("update xchan set xchan_photo_date = '%s', xchan_photo_l = '%s', xchan_photo_m = '%s', - xchan_photo_s = '%s', xchan_photo_mimetype = '%s' where xchan_hash = '%s' limit 1", + xchan_photo_s = '%s', xchan_photo_mimetype = '%s' where xchan_hash = '%s'", dbesc(datetime_convert()), dbesc($photos[0]), dbesc($photos[1]), @@ -11,7 +11,7 @@ function p_init(&$a) { $mid = str_replace('.xml','',argv(1)); - $r = q("select * from item where mid = '%s' and (item_flags & %d) and item_private = 0 limit 1", + $r = q("select * from item where mid = '%s' and (item_flags & %d)>0 and item_private = 0 limit 1", dbesc($mid), intval(ITEM_WALL) ); diff --git a/mod/photo.php b/mod/photo.php index 0329fe0a8..8cb82e8ff 100644 --- a/mod/photo.php +++ b/mod/photo.php @@ -66,7 +66,7 @@ function photo_init(&$a) { intval($uid) ); if(count($r)) { - $data = $r[0]['data']; + $data = dbunescbin($r[0]['data']); $mimetype = $r[0]['type']; } if(! isset($data)) { @@ -140,7 +140,7 @@ function photo_init(&$a) { ); if($r && $allowed) { - $data = $r[0]['data']; + $data = dbunescbin($r[0]['data']); $mimetype = $r[0]['type']; } else { diff --git a/mod/photos.php b/mod/photos.php index d1f2b4993..297878b29 100644 --- a/mod/photos.php +++ b/mod/photos.php @@ -226,7 +226,7 @@ function photos_post(&$a) { intval($page_owner_uid) ); if(count($r)) { - $ph = photo_factory($r[0]['data'], $r[0]['type']); + $ph = photo_factory(dbunescbin($r[0]['data']), $r[0]['type']); if($ph->is_valid()) { $rotate_deg = ( (intval($_POST['rotate']) == 1) ? 270 : 90 ); $ph->rotate($rotate_deg); @@ -234,8 +234,8 @@ function photos_post(&$a) { $width = $ph->getWidth(); $height = $ph->getHeight(); - $x = q("update photo set data = '%s', height = %d, width = %d where `resource_id` = '%s' and uid = %d and scale = 0 limit 1", - dbesc($ph->imageString()), + $x = q("update photo set data = '%s', height = %d, width = %d where `resource_id` = '%s' and uid = %d and scale = 0", + dbescbin($ph->imageString()), intval($height), intval($width), dbesc($resource_id), @@ -247,8 +247,8 @@ function photos_post(&$a) { $width = $ph->getWidth(); $height = $ph->getHeight(); - $x = q("update photo set data = '%s', height = %d, width = %d where `resource_id` = '%s' and uid = %d and scale = 1 limit 1", - dbesc($ph->imageString()), + $x = q("update photo set data = '%s', height = %d, width = %d where `resource_id` = '%s' and uid = %d and scale = 1", + dbescbin($ph->imageString()), intval($height), intval($width), dbesc($resource_id), @@ -261,8 +261,8 @@ function photos_post(&$a) { $width = $ph->getWidth(); $height = $ph->getHeight(); - $x = q("update photo set data = '%s', height = %d, width = %d where `resource_id` = '%s' and uid = %d and scale = 2 limit 1", - dbesc($ph->imageString()), + $x = q("update photo set data = '%s', height = %d, width = %d where `resource_id` = '%s' and uid = %d and scale = 2", + dbescbin($ph->imageString()), intval($height), intval($width), dbesc($resource_id), @@ -322,7 +322,7 @@ function photos_post(&$a) { // make sure the linked item has the same permissions as the photo regardless of any other changes $x = q("update item set allow_cid = '%s', allow_gid = '%s', deny_cid = '%s', deny_gid = '%s', item_private = %d - where id = %d limit 1", + where id = %d", dbesc($str_contact_allow), dbesc($str_group_allow), dbesc($str_contact_deny), @@ -621,14 +621,20 @@ function photos_content(&$a) { else $order = 'DESC'; - $r = q("SELECT `resource_id`, `id`, `filename`, type, max(`scale`) AS `scale`, `description` FROM `photo` WHERE `uid` = %d AND `album` = '%s' - AND `scale` <= 4 and (photo_flags = %d or photo_flags = %d ) $sql_extra GROUP BY `resource_id` ORDER BY `created` $order LIMIT %d , %d", + + /*"SELECT $prefix `resource_id`, `id`, `filename`, type, max(`scale`) AS `scale`, `description` FROM `photo` WHERE `uid` = %d AND `album` = '%s' + AND `scale` <= 4 and (photo_flags = %d or photo_flags = %d ) $sql_extra GROUP BY resource_id ORDER BY `created` $order LIMIT %d OFFSET %d"*/ + + $r = q("SELECT p.resource_id, p.id, p.filename, p.type, p.scale, p.description, p.created FROM photo p INNER JOIN + (SELECT resource_id, max(scale) scale FROM photo WHERE uid = %d AND album = '%s' AND scale <= 4 AND (photo_flags = %d or photo_flags = %d ) $sql_extra GROUP BY resource_id) ph + ON (p.resource_id = ph.resource_id AND p.scale = ph.scale) + ORDER BY created $order LIMIT %d OFFSET %d", intval($owner_uid), dbesc($album), intvaL(PHOTO_NORMAL), intval(PHOTO_PROFILE), - intval($a->pager['start']), - intval($a->pager['itemspage']) + intval($a->pager['itemspage']), + intval($a->pager['start']) ); //edit album name @@ -899,7 +905,7 @@ function photos_content(&$a) { } if((local_user()) && (local_user() == $link_item['uid'])) { - q("UPDATE `item` SET item_flags = (item_flags ^ %d) WHERE parent = %d and uid = %d and (item_flags & %d)", + q("UPDATE `item` SET item_flags = (item_flags & ~%d) WHERE parent = %d and uid = %d and (item_flags & %d)>0", intval(ITEM_UNSEEN), intval($link_item['parent']), intval(local_user()), @@ -1156,18 +1162,19 @@ function photos_content(&$a) { $a->set_pager_total(count($r)); $a->set_pager_itemspage(60); } - - $r = q("SELECT `resource_id`, `id`, `filename`, type, `album`, max(`scale`) AS `scale` FROM `photo` - WHERE `uid` = %d AND `album` != '%s' AND `album` != '%s' - and ( photo_flags = %d or photo_flags = %d ) - $sql_extra GROUP BY `resource_id` ORDER BY `created` DESC LIMIT %d , %d", + + $r = q("SELECT p.resource_id, p.id, p.filename, p.type, p.album, p.scale, p.created FROM photo p INNER JOIN + (SELECT resource_id, max(scale) scale FROM photo + WHERE uid=%d AND album != '%s' AND album != '%s' + AND (photo_flags = %d or photo_flags = %d ) group by resource_id) ph + ON (p.resource_id = ph.resource_id and p.scale = ph.scale) ORDER by p.created DESC LIMIT %d OFFSET %d", intval($a->data['channel']['channel_id']), dbesc('Contact Photos'), dbesc( t('Contact Photos')), intval(PHOTO_NORMAL), intval(PHOTO_PROFILE), - intval($a->pager['start']), - intval($a->pager['itemspage']) + intval($a->pager['itemspage']), + intval($a->pager['start']) ); diff --git a/mod/ping.php b/mod/ping.php index 49475de66..c5db6ba66 100644 --- a/mod/ping.php +++ b/mod/ping.php @@ -89,7 +89,7 @@ function ping_init(&$a) { $basic_presence = false; if($r) { $basic_presence = true; - q("update chatpresence set cp_last = '%s' where cp_id = %d limit 1", + q("update chatpresence set cp_last = '%s' where cp_id = %d", dbesc(datetime_convert()), intval($r[0]['cp_id']) ); @@ -110,7 +110,9 @@ function ping_init(&$a) { * and shouldn't count as online anymore. We allow an expection for bots. */ - q("delete from chatpresence where cp_last < UTC_TIMESTAMP() - INTERVAL 3 MINUTE and cp_client != 'auto' "); + q("delete from chatpresence where cp_last < %s - INTERVAL %s and cp_client != 'auto' ", + db_utcnow(), db_quoteinterval('3 MINUTE') + ); if((! local_user()) || ($result['invalid'])) { echo json_encode($result); @@ -130,14 +132,14 @@ function ping_init(&$a) { if(x($_REQUEST, 'markRead') && local_user()) { switch($_REQUEST['markRead']) { case 'network': - $r = q("update item set item_flags = ( item_flags ^ %d ) where (item_flags & %d) and uid = %d", + $r = q("update item set item_flags = ( item_flags & ~%d ) where (item_flags & %d)>0 and uid = %d", intval(ITEM_UNSEEN), intval(ITEM_UNSEEN), intval(local_user()) ); break; case 'home': - $r = q("update item set item_flags = ( item_flags ^ %d ) where (item_flags & %d) and (item_flags & %d) and uid = %d", + $r = q("update item set item_flags = ( item_flags & ~%d ) where (item_flags & %d)>0 and (item_flags & %d) and uid = %d", intval(ITEM_UNSEEN), intval(ITEM_UNSEEN), intval(ITEM_WALL), @@ -145,7 +147,7 @@ function ping_init(&$a) { ); break; case 'messages': - $r = q("update mail set mail_flags = ( mail_flags ^ %d ) where channel_id = %d and not (mail_flags & %d)", + $r = q("update mail set mail_flags = ( mail_flags | %d ) where channel_id = %d and not (mail_flags & %d)>0", intval(MAIL_SEEN), intval(local_user()), intval(MAIL_SEEN) @@ -179,17 +181,17 @@ function ping_init(&$a) { ); if($t && intval($t[0]['total']) > 49) { $z = q("select * from notify where uid = %d - and seen = 0 order by date desc limit 0, 50", + and seen = 0 order by date desc limit 50", intval(local_user()) ); } else { $z1 = q("select * from notify where uid = %d - and seen = 0 order by date desc limit 0, 50", + and seen = 0 order by date desc limit 50", intval(local_user()) ); $z2 = q("select * from notify where uid = %d - and seen = 1 order by date desc limit 0, %d", + and seen = 1 order by date desc limit %d", intval(local_user()), intval(50 - intval($t[0]['total'])) ); @@ -217,8 +219,8 @@ function ping_init(&$a) { if(argc() > 1 && argv(1) === 'messages') { $channel = $a->get_channel(); $t = q("select mail.*, xchan.* from mail left join xchan on xchan_hash = from_xchan - where channel_id = %d and not ( mail_flags & %d ) and not (mail_flags & %d ) - and from_xchan != '%s' order by created desc limit 0,50", + where channel_id = %d and not ( mail_flags & %d )>0 and not (mail_flags & %d )>0 + and from_xchan != '%s' order by created desc limit 50", intval(local_user()), intval(MAIL_SEEN), intval(MAIL_DELETED), @@ -247,7 +249,7 @@ function ping_init(&$a) { $result = array(); $r = q("SELECT * FROM item - WHERE item_restrict = %d and ( item_flags & %d ) and uid = %d", + WHERE item_restrict = %d and ( item_flags & %d )>0 and uid = %d", intval(ITEM_VISIBLE), intval(ITEM_UNSEEN), intval(local_user()) @@ -269,7 +271,7 @@ function ping_init(&$a) { if(argc() > 1 && (argv(1) === 'intros')) { $result = array(); - $r = q("SELECT * FROM abook left join xchan on abook.abook_xchan = xchan.xchan_hash where abook_channel = %d and (abook_flags & %d) and not ((abook_flags & %d) or (xchan_flags & %d))", + $r = q("SELECT * FROM abook left join xchan on abook.abook_xchan = xchan.xchan_hash where abook_channel = %d and (abook_flags & %d)>0 and not ((abook_flags & %d)>0 or (xchan_flags & %d)>0)", intval(local_user()), intval(ABOOK_FLAG_PENDING), intval(ABOOK_FLAG_SELF|ABOOK_FLAG_IGNORED), @@ -350,7 +352,7 @@ function ping_init(&$a) { $t1 = dba_timer(); $r = q("SELECT id, item_restrict, item_flags FROM item - WHERE (item_restrict = %d) and ( item_flags & %d ) and uid = %d", + WHERE (item_restrict = %d) and ( item_flags & %d )>0 and uid = %d", intval(ITEM_VISIBLE), intval(ITEM_UNSEEN), intval(local_user()) @@ -370,7 +372,7 @@ function ping_init(&$a) { $t2 = dba_timer(); - $intr = q("SELECT COUNT(abook.abook_id) AS total FROM abook left join xchan on abook.abook_xchan = xchan.xchan_hash where abook_channel = %d and (abook_flags & %d) and not ((abook_flags & %d) or (xchan_flags & %d))", + $intr = q("SELECT COUNT(abook.abook_id) AS total FROM abook left join xchan on abook.abook_xchan = xchan.xchan_hash where abook_channel = %d and (abook_flags & %d)>0 and not ((abook_flags & %d)>0 or (xchan_flags & %d)>0)", intval(local_user()), intval(ABOOK_FLAG_PENDING), intval(ABOOK_FLAG_SELF|ABOOK_FLAG_IGNORED), @@ -386,7 +388,7 @@ function ping_init(&$a) { $channel = get_app()->get_channel(); $mails = q("SELECT count(id) as total from mail - WHERE channel_id = %d AND not (mail_flags & %d) and from_xchan != '%s' ", + WHERE channel_id = %d AND not (mail_flags & %d)>0 and from_xchan != '%s' ", intval(local_user()), intval(MAIL_SEEN), dbesc($channel['channel_hash']) @@ -395,7 +397,7 @@ function ping_init(&$a) { $result['mail'] = intval($mails[0]['total']); if ($a->config['system']['register_policy'] == REGISTER_APPROVE && is_site_admin()) { - $regs = q("SELECT count(account_id) as total from account where (account_flags & %d)", + $regs = q("SELECT count(account_id) as total from account where (account_flags & %d)>0", intval(ACCOUNT_PENDING) ); if($regs) diff --git a/mod/poco.php b/mod/poco.php index c1696e4cd..fc01fc565 100644 --- a/mod/poco.php +++ b/mod/poco.php @@ -15,7 +15,7 @@ function poco_init(&$a) { $user = notags(trim(argv(1))); } if(! x($user)) { - $c = q("select * from pconfig where cat = 'system' and k = 'suggestme' and v = 1"); + $c = q("select * from pconfig where cat = 'system' and k = 'suggestme' and v = '1'"); if(! $c) { logger('mod_poco: system mode. No candidates.', LOGGER_DEBUG); http_status_exit(404); @@ -60,7 +60,7 @@ function poco_init(&$a) { } if($justme) - $sql_extra = " and ( abook_flags & " . ABOOK_FLAG_SELF . " ) "; + $sql_extra = " and ( abook_flags & " . ABOOK_FLAG_SELF . " )>0 "; else $sql_extra = " and abook_flags = 0 "; @@ -69,14 +69,14 @@ function poco_init(&$a) { if($system_mode) { $r = q("SELECT count(*) as `total` from abook where ( abook_flags & " . ABOOK_FLAG_SELF . - " ) and abook_channel in (select uid from pconfig where cat = 'system' and k = 'suggestme' and v = 1) "); + " )>0 and abook_channel in (select uid from pconfig where cat = 'system' and k = 'suggestme' and v = 1) "); } else { $r = q("SELECT count(*) as `total` from abook where abook_channel = %d $sql_extra ", intval($channel_id) ); - $c = q("select * from menu_item where ( mitem_flags & " . intval(MENU_ITEM_CHATROOM) . " ) and allow_cid = '' and allow_gid = '' and deny_cid = '' and deny_gid = '' and mitem_channel_id = %d", + $c = q("select * from menu_item where ( mitem_flags & " . intval(MENU_ITEM_CHATROOM) . " )>0 and allow_cid = '' and allow_gid = '' and deny_cid = '' and deny_gid = '' and mitem_channel_id = %d", intval($channel_id) ); } @@ -93,17 +93,17 @@ function poco_init(&$a) { if($system_mode) { $r = q("SELECT abook.*, xchan.* from abook left join xchan on abook_xchan = xchan_hash where ( abook_flags & " . ABOOK_FLAG_SELF . - " ) and abook_channel in (select uid from pconfig where cat = 'system' and k = 'suggestme' and v = 1) limit %d, %d ", - intval($startIndex), - intval($itemsPerPage) + " )>0 and abook_channel in (select uid from pconfig where cat = 'system' and k = 'suggestme' and v = 1) limit %d offset %d ", + intval($itemsPerPage), + intval($startIndex) ); } else { $r = q("SELECT abook.*, xchan.* from abook left join xchan on abook_xchan = xchan_hash where abook_channel = %d - $sql_extra LIMIT %d, %d", + $sql_extra LIMIT %d OFFSET %d", intval($channel_id), - intval($startIndex), - intval($itemsPerPage) + intval($itemsPerPage), + intval($startIndex) ); } diff --git a/mod/post.php b/mod/post.php index c21af83e4..47e460f1e 100644 --- a/mod/post.php +++ b/mod/post.php @@ -98,7 +98,7 @@ function post_init(&$a) { // Any channel will do, providing it's currently active. We just need to have an // identity to attach to the packet we send back. So find one. - $c = q("select * from channel where not ( channel_pageflags & %d ) limit 1", + $c = q("select * from channel where not ( channel_pageflags & %d )>0 limit 1", intval(PAGE_REMOVED) ); @@ -612,7 +612,7 @@ function post_post(&$a) { else $ret['pickup'][] = array('notify' => json_decode($rr['outq_notify'],true),'message' => $x); - $x = q("delete from outq where outq_hash = '%s' limit 1", + $x = q("delete from outq where outq_hash = '%s'", dbesc($rr['outq_hash']) ); } @@ -659,7 +659,7 @@ function post_post(&$a) { // Update our DB to show when we last communicated successfully with this hub // This will allow us to prune dead hubs from using up resources - $r = q("update hubloc set hubloc_connected = '%s' where hubloc_id = %d limit 1", + $r = q("update hubloc set hubloc_connected = '%s' where hubloc_id = %d", dbesc(datetime_convert()), intval($hub['hubloc_id']) ); @@ -667,17 +667,17 @@ function post_post(&$a) { // a dead hub came back to life - reset any tombstones we might have if($hub['hubloc_status'] & HUBLOC_OFFLINE) { - q("update hubloc set hubloc_status = (hubloc_status ^ %d) where hubloc_id = %d limit 1", + q("update hubloc set hubloc_status = (hubloc_status & ~%d) where hubloc_id = %d", intval(HUBLOC_OFFLINE), intval($hub['hubloc_id']) ); if($r[0]['hubloc_flags'] & HUBLOC_FLAGS_ORPHANCHECK) { - q("update hubloc set hubloc_flags = (hubloc_flags ^ %d) where hubloc_id = %d limit 1", + q("update hubloc set hubloc_flags = (hubloc_flags & ~%d) where hubloc_id = %d", intval(HUBLOC_FLAGS_ORPHANCHECK), intval($hub['hubloc_id']) ); } - q("update xchan set xchan_flags = (xchan_flags ^ %d) where (xchan_flags & %d) and xchan_hash = '%s' limit 1", + q("update xchan set xchan_flags = (xchan_flags & ~%d) where (xchan_flags & %d)>0 and xchan_hash = '%s'", intval(XCHAN_FLAGS_ORPHAN), intval(XCHAN_FLAGS_ORPHAN), dbesc($hub['hubloc_hash']) @@ -732,7 +732,9 @@ function post_post(&$a) { $sender_hash = make_xchan_hash($arr['guid'],$arr['guid_sig']); // garbage collect any old unused notifications - q("delete from verify where type = 'auth' and created < UTC_TIMESTAMP() - INTERVAL 10 MINUTE"); + q("delete from verify where type = 'auth' and created < %s - INTERVAL %s", + db_utcnow(), db_quoteinterval('10 MINUTE') + ); $y = q("select xchan_pubkey from xchan where xchan_hash = '%s' limit 1", dbesc($sender_hash) @@ -781,7 +783,7 @@ function post_post(&$a) { $ret['message'] .= 'verification key not found' . EOL; json_return_and_die($ret); } - $r = q("delete from verify where id = %d limit 1", + $r = q("delete from verify where id = %d", intval($z[0]['id']) ); diff --git a/mod/profile_photo.php b/mod/profile_photo.php index aad9c9d16..24439d404 100644 --- a/mod/profile_photo.php +++ b/mod/profile_photo.php @@ -173,7 +173,7 @@ function profile_photo_post(&$a) { dbesc($base_image['resource_id']), intval(local_user()) ); - $r = q("UPDATE photo SET photo_flags = ( photo_flags ^ %d ) WHERE ( photo_flags & %d ) + $r = q("UPDATE photo SET photo_flags = ( photo_flags & ~%d ) WHERE ( photo_flags & %d )>0 AND resource_id != '%s' AND `uid` = %d", intval(PHOTO_PROFILE), intval(PHOTO_PROFILE), @@ -182,7 +182,7 @@ function profile_photo_post(&$a) { ); } else { - $r = q("update profile set photo = '%s', thumb = '%s' where id = %d and uid = %d limit 1", + $r = q("update profile set photo = '%s', thumb = '%s' where id = %d and uid = %d", dbesc($a->get_baseurl() . '/photo/' . $base_image['resource_id'] . '-4'), dbesc($a->get_baseurl() . '/photo/' . $base_image['resource_id'] . '-5'), intval($_REQUEST['profile']), @@ -196,7 +196,7 @@ function profile_photo_post(&$a) { $channel = $a->get_channel(); $r = q("UPDATE xchan set xchan_photo_mimetype = '%s', xchan_photo_date = '%s' - where xchan_hash = '%s' limit 1", + where xchan_hash = '%s'", dbesc($im->getType()), dbesc(datetime_convert()), dbesc($channel['xchan_hash']) @@ -302,7 +302,7 @@ function profile_photo_content(&$a) { // unset any existing profile photos $r = q("UPDATE photo SET profile = 0 WHERE profile = 1 AND uid = %d", intval(local_user())); - $r = q("UPDATE photo SET photo_flags = (photo_flags ^ %d ) WHERE (photo_flags & %d ) AND uid = %d", + $r = q("UPDATE photo SET photo_flags = (photo_flags & ~%d ) WHERE (photo_flags & %d )>0 AND uid = %d", intval(PHOTO_PROFILE), intval(PHOTO_PROFILE), intval(local_user())); @@ -320,7 +320,7 @@ function profile_photo_content(&$a) { ); $r = q("UPDATE xchan set xchan_photo_date = '%s' - where xchan_hash = '%s' limit 1", + where xchan_hash = '%s'", dbesc(datetime_convert()), dbesc($channel['xchan_hash']) ); diff --git a/mod/profiles.php b/mod/profiles.php index 14f24c5cf..2c1ebd394 100644 --- a/mod/profiles.php +++ b/mod/profiles.php @@ -30,7 +30,7 @@ function profiles_init(&$a) { dbesc($profile_guid), intval(local_user()) ); - $r = q("DELETE FROM `profile` WHERE `id` = %d AND `uid` = %d LIMIT 1", + $r = q("DELETE FROM `profile` WHERE `id` = %d AND `uid` = %d", intval(argv(2)), intval(local_user()) ); @@ -338,7 +338,7 @@ function profiles_post(&$a) { dbesc($zz['field_name']) ); if($w) { - q("update profext set v = '%s' where id = %d limit 1", + q("update profext set v = '%s' where id = %d", dbesc(escape_tags(trim($_POST[$zz['field_name']]))), intval($w[0]['id']) ); @@ -453,7 +453,7 @@ function profiles_post(&$a) { `work` = '%s', `education` = '%s', `hide_friends` = %d - WHERE `id` = %d AND `uid` = %d LIMIT 1", + WHERE `id` = %d AND `uid` = %d", dbesc($profile_name), dbesc($name), dbesc($pdesc), @@ -506,7 +506,7 @@ function profiles_post(&$a) { $channel = $a->get_channel(); if($namechanged && $is_default) { - $r = q("UPDATE xchan SET xchan_name = '%s', xchan_name_date = '%s' WHERE xchan_hash = '%s' limit 1", + $r = q("UPDATE xchan SET xchan_name = '%s', xchan_name_date = '%s' WHERE xchan_hash = '%s'", dbesc($name), dbesc(datetime_convert()), dbesc($channel['xchan_hash']) diff --git a/mod/profperm.php b/mod/profperm.php index 197062936..55dc8cc77 100644 --- a/mod/profperm.php +++ b/mod/profperm.php @@ -75,13 +75,13 @@ function profperm_content(&$a) { if($change) { if(in_array($change,$ingroup)) { - q("UPDATE abook SET abook_profile = '' WHERE abook_id = %d AND abook_channel = %d LIMIT 1", + q("UPDATE abook SET abook_profile = '' WHERE abook_id = %d AND abook_channel = %d", intval($change), intval(local_user()) ); } else { - q("UPDATE abook SET abook_profile = '%s' WHERE abook_id = %d AND abook_channel = %d LIMIT 1", + q("UPDATE abook SET abook_profile = '%s' WHERE abook_id = %d AND abook_channel = %d", dbesc($profile['profile_guid']), intval($change), intval(local_user()) diff --git a/mod/receive.php b/mod/receive.php index 4071b169b..b7d27d40f 100644 --- a/mod/receive.php +++ b/mod/receive.php @@ -31,7 +31,7 @@ function receive_post(&$a) { // Diaspora sites *may* provide a truncated guid. - $r = q("SELECT * FROM channel left join xchan on channel_hash = xchan_hash WHERE channel_guid like '%s' AND NOT (channel_pageflags & %d ) LIMIT 1", + $r = q("SELECT * FROM channel left join xchan on channel_hash = xchan_hash WHERE channel_guid like '%s' AND NOT (channel_pageflags & %d )>0 LIMIT 1", dbesc($guid . '%'), intval(PAGE_REMOVED) ); diff --git a/mod/register.php b/mod/register.php index 0cf1364b5..70bdcf350 100644 --- a/mod/register.php +++ b/mod/register.php @@ -37,7 +37,9 @@ function register_post(&$a) { $max_dailies = intval(get_config('system','max_daily_registrations')); if($max_dailies) { - $r = q("select count(account_id) as total from account where account_created > UTC_TIMESTAMP() - INTERVAL 1 day"); + $r = q("select count(account_id) as total from account where account_created > %s - INTERVAL %s", + db_utcnow(), db_quoteinterval('1 day') + ); if($r && $r[0]['total'] >= $max_dailies) { notice( t('Maximum daily site registrations exceeded. Please try again tomorrow.') . EOL); return; @@ -100,7 +102,7 @@ function register_post(&$a) { $invite_code = ((x($_POST,'invite_code')) ? notags(trim($_POST['invite_code'])) : ''); if($using_invites && $invite_code) { - q("delete * from register where hash = '%s' limit 1", dbesc($invite_code)); + q("delete * from register where hash = '%s'", dbesc($invite_code)); set_pconfig($result['account']['account_id'],'system','invites_remaining',$num_invites); } @@ -164,7 +166,9 @@ function register_content(&$a) { $max_dailies = intval(get_config('system','max_daily_registrations')); if($max_dailies) { - $r = q("select count(account_id) as total from account where account_created > UTC_TIMESTAMP() - INTERVAL 1 day"); + $r = q("select count(account_id) as total from account where account_created > %s - INTERVAL %s", + db_utcnow(), db_quoteinterval('1 day') + ); if($r && $r[0]['total'] >= $max_dailies) { logger('max daily registrations exceeded.'); notice( t('This site has exceeded the number of allowed daily account registrations. Please try again tomorrow.') . EOL); diff --git a/mod/search.php b/mod/search.php index 15ac71376..612ceb4bc 100644 --- a/mod/search.php +++ b/mod/search.php @@ -68,7 +68,8 @@ function search_content(&$a,$update = 0, $load = false) { ); } else { - $sql_extra = sprintf(" AND `item`.`body` REGEXP '%s' ", dbesc(protect_sprintf(preg_quote($search)))); + $regstr = db_getfunc('REGEXP'); + $sql_extra = sprintf(" AND `item`.`body` $regstr '%s' ", dbesc(protect_sprintf(preg_quote($search)))); } // Here is the way permissions work in the search module... @@ -123,7 +124,7 @@ function search_content(&$a,$update = 0, $load = false) { if(($update) && ($load)) { $itemspage = get_pconfig(local_user(),'system','itemspage'); $a->set_pager_itemspage(((intval($itemspage)) ? $itemspage : 20)); - $pager_sql = sprintf(" LIMIT %d, %d ",intval($a->pager['start']), intval($a->pager['itemspage'])); + $pager_sql = sprintf(" LIMIT %d OFFSET %d ", intval($a->pager['itemspage']), intval($a->pager['start'])); // in case somebody turned off public access to sys channel content with permissions @@ -132,29 +133,36 @@ function search_content(&$a,$update = 0, $load = false) { if($load) { $r = null; - + + if(ACTIVE_DBTYPE == DBTYPE_POSTGRES) { + $prefix = 'distinct on (created, mid)'; + $suffix = 'ORDER BY created DESC, mid'; + } else { + $prefix = 'distinct'; + $suffix = 'group by mid ORDER BY created DESC'; + } if(local_user()) { - $r = q("SELECT distinct mid, item.id as item_id, item.* from item + $r = q("SELECT $prefix mid, item.id as item_id, item.* from item WHERE item_restrict = 0 AND ((( `item`.`allow_cid` = '' AND `item`.`allow_gid` = '' AND `item`.`deny_cid` = '' AND `item`.`deny_gid` = '' AND item_private = 0 ) OR ( `item`.`uid` = %d )) OR item.owner_xchan = '%s' ) $sql_extra - group by mid ORDER BY created DESC $pager_sql ", + $suffix $pager_sql ", intval(local_user()), dbesc($sys['xchan_hash']) ); } if($r === null) { - $r = q("SELECT distinct mid, item.id as item_id, item.* from item - WHERE item_restrict = 0 - AND (((( `item`.`allow_cid` = '' AND `item`.`allow_gid` = '' AND `item`.`deny_cid` = '' - AND `item`.`deny_gid` = '' AND item_private = 0 ) - and owner_xchan in ( " . stream_perms_xchans(($observer) ? (PERMS_NETWORK|PERMS_PUBLIC) : PERMS_PUBLIC) . " )) - $pub_sql ) OR owner_xchan = '%s') - $sql_extra - group by mid ORDER BY created DESC $pager_sql", + $r = q("SELECT $prefix mid, item.id as item_id, item.* from item + WHERE item_restrict = 0 + AND (((( `item`.`allow_cid` = '' AND `item`.`allow_gid` = '' AND `item`.`deny_cid` = '' + AND `item`.`deny_gid` = '' AND item_private = 0 ) + and owner_xchan in ( " . stream_perms_xchans(($observer) ? (PERMS_NETWORK|PERMS_PUBLIC) : PERMS_PUBLIC) . " )) + $pub_sql ) OR owner_xchan = '%s') + $sql_extra + $suffix $pager_sql", dbesc($sys['xchan_hash']) - ); + ); } } else { diff --git a/mod/settings.php b/mod/settings.php index 58257368e..08fad5471 100644 --- a/mod/settings.php +++ b/mod/settings.php @@ -168,7 +168,7 @@ function settings_post(&$a) { } } - $r = q("UPDATE channel SET channel_theme = '%s' WHERE channel_id = %d LIMIT 1", + $r = q("UPDATE channel SET channel_theme = '%s' WHERE channel_id = %d", dbesc($theme), intval(local_user()) ); @@ -205,7 +205,7 @@ function settings_post(&$a) { $salt = random_string(32); $password_encoded = hash('whirlpool', $salt . $newpass); $r = q("update account set account_salt = '%s', account_password = '%s', account_password_changed = '%s' - where account_id = %d limit 1", + where account_id = %d", dbesc($salt), dbesc($password_encoded), dbesc(datetime_convert()), @@ -235,7 +235,7 @@ function settings_post(&$a) { $email = $a->user['email']; } if(! $errs) { - $r = q("update account set account_email = '%s' where account_id = %d limit 1", + $r = q("update account set account_email = '%s' where account_id = %d", dbesc($email), intval($account['account_id']) ); @@ -267,7 +267,7 @@ function settings_post(&$a) { $hide_presence = (((x($_POST,'hide_presence')) && (intval($_POST['hide_presence']) == 1)) ? 1: 0); $publish = (((x($_POST,'profile_in_directory')) && (intval($_POST['profile_in_directory']) == 1)) ? 1: 0); $def_group = ((x($_POST,'group-selection')) ? notags(trim($_POST['group-selection'])) : ''); - $r = q("update channel set channel_default_group = '%s' where channel_id = %d limit 1", + $r = q("update channel set channel_default_group = '%s' where channel_id = %d", dbesc($def_group), intval(local_user()) ); @@ -283,7 +283,7 @@ function settings_post(&$a) { $str_group_deny = perms2str($_POST['group_deny']); $str_contact_deny = perms2str($_POST['contact_deny']); $r = q("update channel set channel_allow_cid = '%s', channel_allow_gid = '%s', channel_deny_cid = '%s', channel_deny_gid = '%s' - where channel_id = %d limit 1", + where channel_id = %d", dbesc($str_contact_allow), dbesc($str_group_allow), dbesc($str_contact_deny), @@ -313,7 +313,7 @@ function settings_post(&$a) { ); } if($r) { - q("update channel set channel_default_group = '%s', channel_allow_gid = '%s', channel_allow_cid = '', channel_deny_gid = '', channel_deny_cid = '' where channel_id = %d limit 1", + q("update channel set channel_default_group = '%s', channel_allow_gid = '%s', channel_allow_cid = '', channel_deny_gid = '', channel_deny_cid = '' where channel_id = %d", dbesc($r[0]['hash']), dbesc('<' . $r[0]['hash'] . '>'), intval(local_user()) @@ -327,12 +327,12 @@ function settings_post(&$a) { // no default collection else { q("update channel set channel_default_group = '', channel_allow_gid = '', channel_allow_cid = '', channel_deny_gid = '', - channel_deny_cid = '' where channel_id = %d limit 1", + channel_deny_cid = '' where channel_id = %d", intval(local_user()) ); } - $r = q("update abook set abook_my_perms = %d where abook_channel = %d and (abook_flags & %d) limit 1", + $r = q("update abook set abook_my_perms = %d where abook_channel = %d and (abook_flags & %d)>0", intval(($role_permissions['perms_auto']) ? intval($role_permissions['perms_accept']) : 0), intval(local_user()), intval(ABOOK_FLAG_SELF) @@ -433,7 +433,7 @@ function settings_post(&$a) { set_pconfig(local_user(),'system','blocktags',$blocktags); set_pconfig(local_user(),'system','channel_menu',$channel_menu); - $r = q("update channel set channel_name = '%s', channel_pageflags = %d, channel_timezone = '%s', channel_location = '%s', channel_notifyflags = %d, channel_max_anon_mail = %d, channel_max_friend_req = %d, channel_expire_days = %d $set_perms where channel_id = %d limit 1", + $r = q("update channel set channel_name = '%s', channel_pageflags = %d, channel_timezone = '%s', channel_location = '%s', channel_notifyflags = %d, channel_max_anon_mail = %d, channel_max_friend_req = %d, channel_expire_days = %d $set_perms where channel_id = %d", dbesc($username), intval($pageflags), dbesc($timezone), @@ -448,14 +448,14 @@ function settings_post(&$a) { info( t('Settings updated.') . EOL); if(! is_null($publish)) { - $r = q("UPDATE profile SET publish = %d WHERE is_default = 1 AND uid = %d LIMIT 1", + $r = q("UPDATE profile SET publish = %d WHERE is_default = 1 AND uid = %d", intval($publish), intval(local_user()) ); } if($name_change) { - $r = q("update xchan set xchan_name = '%s', xchan_name_date = '%s' where xchan_hash = '%s' limit 1", + $r = q("update xchan set xchan_name = '%s', xchan_name_date = '%s' where xchan_hash = '%s'", dbesc($username), dbesc(datetime_convert()), dbesc($channel['channel_hash']) diff --git a/mod/setup.php b/mod/setup.php index a8f3a1f47..044def15a 100755 --- a/mod/setup.php +++ b/mod/setup.php @@ -50,16 +50,18 @@ function setup_post(&$a) { $dbuser = trim($_POST['dbuser']); $dbpass = trim($_POST['dbpass']); $dbdata = trim($_POST['dbdata']); + $dbtype = intval(trim($_POST['dbtype'])); $phpath = trim($_POST['phpath']); $adminmail = trim($_POST['adminmail']); $siteurl = trim($_POST['siteurl']); require_once('include/dba/dba_driver.php'); unset($db); - $db = dba_factory($dbhost, $dbport, $dbuser, $dbpass, $dbdata, true); + $db = dba_factory($dbhost, $dbport, $dbuser, $dbpass, $dbdata, $dbtype, true); if(! $db->connected) { echo "Database Connect failed: " . $db->error; killme(); + $a->data['db_conn_failed']=true; } /*if(get_db_errno()) { unset($db); @@ -80,9 +82,9 @@ function setup_post(&$a) { return; } }*/ - if(get_db_errno()) { - $a->data['db_conn_failed']=true; - } + //if(get_db_errno()) { + + //} return; break; @@ -93,6 +95,7 @@ function setup_post(&$a) { $dbuser = notags(trim($_POST['dbuser'])); $dbpass = notags(trim($_POST['dbpass'])); $dbdata = notags(trim($_POST['dbdata'])); + $dbtype = intval(notags(trim($_POST['dbtype']))); $phpath = notags(trim($_POST['phpath'])); $timezone = notags(trim($_POST['timezone'])); $adminmail = notags(trim($_POST['adminmail'])); @@ -109,7 +112,7 @@ function setup_post(&$a) { } // connect to db - $db = dba_factory($dbhost, $dbport, $dbuser, $dbpass, $dbdata, true); + $db = dba_factory($dbhost, $dbport, $dbuser, $dbpass, $dbdata, $dbtype, true); if(! $db->connected) { echo 'CRITICAL: DB not connected.'; @@ -123,6 +126,7 @@ function setup_post(&$a) { '$dbuser' => $dbuser, '$dbpass' => $dbpass, '$dbdata' => $dbdata, + '$dbtype' => $dbtype, '$timezone' => $timezone, '$siteurl' => $siteurl, '$site_id' => random_string(), @@ -187,7 +191,7 @@ function setup_content(&$a) { } if(x($a->data,'db_failed')) { - $txt = t('You may need to import the file "install/database.sql" manually using phpmyadmin or mysql.') . EOL; + $txt = t('You may need to import the file "install/schema_xxx.sql" manually using a database client.') . EOL; $txt .= t('Please see the file "install/INSTALL.txt".') . EOL ."<hr>" ; $txt .= "<pre>".$a->data['db_failed'] . "</pre>". EOL ; $db_return_text .= $txt; @@ -273,6 +277,7 @@ function setup_content(&$a) { $dbport = intval(notags(trim($_POST['dbport']))); $dbpass = notags(trim($_POST['dbpass'])); $dbdata = notags(trim($_POST['dbdata'])); + $dbtype = intval(notags(trim($_POST['dbtype']))); $phpath = notags(trim($_POST['phpath'])); $adminmail = notags(trim($_POST['adminmail'])); $siteurl = notags(trim($_POST['siteurl'])); @@ -293,6 +298,7 @@ function setup_content(&$a) { '$dbuser' => array('dbuser', t('Database Login Name'), $dbuser, ''), '$dbpass' => array('dbpass', t('Database Login Password'), $dbpass, ''), '$dbdata' => array('dbdata', t('Database Name'), $dbdata, ''), + '$dbtype' => array('dbtype', t('Database Type'), $dbtype, '', array( 0=>'MySQL', 1=>'PostgreSQL' )), '$adminmail' => array('adminmail', t('Site administrator email address'), $adminmail, t('Your account email address must match this in order to use the web admin panel.')), '$siteurl' => array('siteurl', t('Website URL'), z_root(), t('Please use SSL (https) URL if available.')), @@ -316,6 +322,7 @@ function setup_content(&$a) { $dbuser = notags(trim($_POST['dbuser'])); $dbpass = notags(trim($_POST['dbpass'])); $dbdata = notags(trim($_POST['dbdata'])); + $dbtype = intval(notags(trim($_POST['dbtype']))); $phpath = notags(trim($_POST['phpath'])); $adminmail = notags(trim($_POST['adminmail'])); @@ -335,6 +342,7 @@ function setup_content(&$a) { '$dbpass' => $dbpass, '$dbdata' => $dbdata, '$phpath' => $phpath, + '$dbtype' => $dbtype, '$adminmail' => array('adminmail', t('Site administrator email address'), $adminmail, t('Your account email address must match this in order to use the web admin panel.')), @@ -440,7 +448,7 @@ function check_funcs(&$checks) { check_add($ck_funcs, t('libCurl PHP module'), true, true, ""); check_add($ck_funcs, t('GD graphics PHP module'), true, true, ""); check_add($ck_funcs, t('OpenSSL PHP module'), true, true, ""); - check_add($ck_funcs, t('mysqli PHP module'), true, true, ""); + check_add($ck_funcs, t('mysqli or postgres PHP module'), true, true, ""); check_add($ck_funcs, t('mb_string PHP module'), true, true, ""); check_add($ck_funcs, t('mcrypt PHP module'), true, true, ""); @@ -471,9 +479,9 @@ function check_funcs(&$checks) { $ck_funcs[2]['status']= false; $ck_funcs[2]['help']= t('Error: openssl PHP module required but not installed.'); } - if(! function_exists('mysqli_connect')){ + if(! function_exists('mysqli_connect') && !function_exists('pg_connect')){ $ck_funcs[3]['status']= false; - $ck_funcs[3]['help']= t('Error: mysqli PHP module required but not installed.'); + $ck_funcs[3]['help']= t('Error: mysqli or postgres PHP module required but neither are installed.'); } if(! function_exists('mb_strlen')){ $ck_funcs[4]['status']= false; @@ -579,7 +587,7 @@ function check_htaccess(&$checks) { if ((! $test['success']) || ($test['body'] != "ok")) { $status = false; - $help = t('Url rewrite in .htaccess is not working. Check your server configuration.'); + $help = t('Url rewrite in .htaccess is not working. Check your server configuration.'.'Test: '.var_export($test,true)); } check_add($checks, t('Url rewrite is working'), $status, true, $help); } else { @@ -607,8 +615,8 @@ function load_database_rem($v, $i){ function load_database($db) { - - $str = file_get_contents('install/database.sql'); + file_put_contents('debug-foo.log', 'Loading schema: '.$db->get_install_script()); + $str = file_get_contents($db->get_install_script()); $arr = explode(';',$str); $errors = false; foreach($arr as $a) { diff --git a/mod/siteinfo.php b/mod/siteinfo.php index a58f17c53..12598cc12 100644 --- a/mod/siteinfo.php +++ b/mod/siteinfo.php @@ -8,7 +8,7 @@ function siteinfo_init(&$a) { $sql_extra = ''; - $r = q("select * from channel left join account on account_id = channel_account_id where ( account_roles & 4096 ) and account_default_channel = channel_id"); + $r = q("select * from channel left join account on account_id = channel_account_id where ( account_roles & 4096 )>0 and account_default_channel = channel_id"); if($r) { diff --git a/mod/sources.php b/mod/sources.php index f4b36508f..73d78bbbf 100644 --- a/mod/sources.php +++ b/mod/sources.php @@ -47,7 +47,7 @@ function sources_post(&$a) { goaway(z_root() . '/sources'); } else { - $r = q("update source set src_xchan = '%s', src_patt = '%s' where src_channel_id = %d and src_id = %d limit 1", + $r = q("update source set src_xchan = '%s', src_patt = '%s' where src_channel_id = %d and src_id = %d", dbesc($xchan), dbesc($words), intval(local_user()), @@ -150,7 +150,7 @@ function sources_content(&$a) { notice( t('Source not found.') . EOL); return ''; } - $r = q("delete from source where src_id = %d and src_channel_id = %d limit 1", + $r = q("delete from source where src_id = %d and src_channel_id = %d", intval(argv(1)), intval(local_user()) ); diff --git a/mod/starred.php b/mod/starred.php index ca7621b0f..05b45bea3 100644 --- a/mod/starred.php +++ b/mod/starred.php @@ -21,7 +21,7 @@ function starred_init(&$a) { $item_flags = ( $r[0]['item_flags'] ^ ITEM_STARRED ); - $r = q("UPDATE item SET item_flags = %d WHERE uid = %d and id = %d LIMIT 1", + $r = q("UPDATE item SET item_flags = %d WHERE uid = %d and id = %d", intval($item_flags), intval(local_user()), intval($message_id) diff --git a/mod/subthread.php b/mod/subthread.php index 5ef0615b1..f0f54f4a6 100755 --- a/mod/subthread.php +++ b/mod/subthread.php @@ -228,7 +228,7 @@ EOT; $post_id = $post['item_id']; if(! $item['visible']) { - $r = q("UPDATE `item` SET `visible` = 1 WHERE `id` = %d AND `uid` = %d LIMIT 1", + $r = q("UPDATE `item` SET `visible` = 1 WHERE `id` = %d AND `uid` = %d", intval($item['id']), intval($owner_uid) ); diff --git a/mod/thing.php b/mod/thing.php index c12976f22..f53a6ab7b 100644 --- a/mod/thing.php +++ b/mod/thing.php @@ -85,7 +85,7 @@ function thing_init(&$a) { else $local_photo = $orig_record['imgurl']; - $r = q("update term set term = '%s', url = '%s', imgurl = '%s' where term_hash = '%s' and uid = %d limit 1", + $r = q("update term set term = '%s', url = '%s', imgurl = '%s' where term_hash = '%s' and uid = %d", dbesc($name), dbesc(($url) ? $url : z_root() . '/thing/' . $term_hash), dbesc($local_photo), @@ -301,12 +301,12 @@ function thing_content(&$a) { } - $x = q("delete from obj where obj_obj = '%s' and obj_type = %d and obj_channel = %d limit 1", + $x = q("delete from obj where obj_obj = '%s' and obj_type = %d and obj_channel = %d", dbesc($thing_hash), intval(TERM_OBJ_THING), intval(local_user()) ); - $x = q("delete from term where term_hash = '%s' and uid = %d limit 1", + $x = q("delete from term where term_hash = '%s' and uid = %d", dbesc($thing_hash), intval(local_user()) ); diff --git a/mod/viewconnections.php b/mod/viewconnections.php index 40ce28efe..40d26c823 100644 --- a/mod/viewconnections.php +++ b/mod/viewconnections.php @@ -37,7 +37,7 @@ function viewconnections_content(&$a) { $xchan_flags = $xchan_flags | XCHAN_FLAGS_HIDDEN; } - $r = q("SELECT count(*) as total FROM abook left join xchan on abook_xchan = xchan_hash where abook_channel = %d and not (abook_flags & %d ) and not ( xchan_flags & %d ) ", + $r = q("SELECT count(*) as total FROM abook left join xchan on abook_xchan = xchan_hash where abook_channel = %d and not (abook_flags & %d )>0 and not ( xchan_flags & %d )>0 ", intval($a->profile['uid']), intval($abook_flags), intval($xchan_flags) @@ -46,12 +46,12 @@ function viewconnections_content(&$a) { $a->set_pager_total($r[0]['total']); } - $r = q("SELECT * FROM abook left join xchan on abook_xchan = xchan_hash where abook_channel = %d and not ( abook_flags & %d ) and not ( xchan_flags & %d ) order by xchan_name LIMIT %d , %d ", + $r = q("SELECT * FROM abook left join xchan on abook_xchan = xchan_hash where abook_channel = %d and not ( abook_flags & %d )>0 and not ( xchan_flags & %d )>0 order by xchan_name LIMIT %d OFFSET %d ", intval($a->profile['uid']), intval($abook_flags), intval($xchan_flags), - intval($a->pager['start']), - intval($a->pager['itemspage']) + intval($a->pager['itemspage']), + intval($a->pager['start']) ); if(! $r) { diff --git a/mod/zfinger.php b/mod/zfinger.php index 666f141ec..7966caea8 100644 --- a/mod/zfinger.php +++ b/mod/zfinger.php @@ -74,12 +74,12 @@ function zfinger_init(&$a) { */ $r = q("select channel.*, xchan.* from channel left join xchan on channel_hash = xchan_hash - where ( channel_pageflags & %d ) order by channel_id limit 1", + where ( channel_pageflags & %d )>0 order by channel_id limit 1", intval(PAGE_SYSTEM) ); if(! $r) { $r = q("select channel.*, xchan.* from channel left join xchan on channel_hash = xchan_hash - where not ( channel_pageflags & %d ) order by channel_id limit 1", + where not ( channel_pageflags & %d )>0 order by channel_id limit 1", intval(PAGE_REMOVED) ); } diff --git a/mod/zotfeed.php b/mod/zotfeed.php index c730e4162..9ad93c1d4 100644 --- a/mod/zotfeed.php +++ b/mod/zotfeed.php @@ -21,7 +21,7 @@ function zotfeed_init(&$a) { $channel_address = ((argc() > 1) ? argv(1) : ''); if($channel_address) { - $r = q("select channel_id, channel_name from channel where channel_address = '%s' and not (channel_pageflags & %d) limit 1", + $r = q("select channel_id, channel_name from channel where channel_address = '%s' and not (channel_pageflags & %d)>0 limit 1", dbesc(argv(1)), intval(PAGE_REMOVED) ); |