From 9bb847bb07ec017eb8e2ebb2764b7e34acf5e619 Mon Sep 17 00:00:00 2001 From: zotlabs Date: Wed, 12 Oct 2016 18:15:12 -0700 Subject: remove the rest of the backticks from sql queries; replace with TQUOT const which is driver dependent --- include/dba/dba_driver.php | 9 +++- include/dba/dba_postgres.php | 1 + include/import.php | 112 ++++++++++++++++++++--------------------- include/items.php | 14 +++--- include/photo/photo_driver.php | 100 ++++++++++++++++++------------------ include/zot.php | 5 +- 6 files changed, 125 insertions(+), 116 deletions(-) (limited to 'include') diff --git a/include/dba/dba_driver.php b/include/dba/dba_driver.php index 852dc16af..36353354c 100755 --- a/include/dba/dba_driver.php +++ b/include/dba/dba_driver.php @@ -72,6 +72,7 @@ class DBA { define('NULL_DATE', self::$dba->get_null_date()); define('ACTIVE_DBTYPE', self::$dbtype); + define('TQUOT', self::$dba->get_table_quote()); return self::$dba; } @@ -88,6 +89,7 @@ abstract class dba_driver { const INSTALL_SCRIPT='install/schema_mysql.sql'; const NULL_DATE = '0001-01-01 00:00:00'; const UTC_NOW = 'UTC_TIMESTAMP()'; + const TQUOT = "`"; protected $db; protected $pdo = array(); @@ -157,6 +159,11 @@ abstract class dba_driver { return static::INSTALL_SCRIPT; } + function get_table_quote() { + return static::TQUOT; + } + + function utcnow() { return static::UTC_NOW; } @@ -313,7 +320,7 @@ function db_concat($fld, $sep) { * queries return true if the command was successful or false if it wasn't. * * Example: - * $r = q("SELECT * FROM `%s` WHERE `uid` = %d", + * $r = q("SELECT * FROM %s WHERE `uid` = %d", * 'user', 1); * * @param string $sql The SQL query to execute diff --git a/include/dba/dba_postgres.php b/include/dba/dba_postgres.php index 03b29d703..ae3e5a76f 100644 --- a/include/dba/dba_postgres.php +++ b/include/dba/dba_postgres.php @@ -7,6 +7,7 @@ class dba_postgres extends dba_driver { const INSTALL_SCRIPT='install/schema_postgres.sql'; const NULL_DATE = '0001-01-01 00:00:00'; const UTC_NOW = "now() at time zone 'UTC'"; + const TQUOT = '"'; function connect($server,$port,$user,$pass,$db) { if(!$port) $port = 5432; diff --git a/include/import.php b/include/import.php index 479e45cc2..ef3f8bec5 100644 --- a/include/import.php +++ b/include/import.php @@ -84,9 +84,9 @@ function import_channel($channel, $account_id, $seize) { if($clean) { dbesc_array($clean); - $r = dbq("INSERT INTO channel (`" - . implode("`, `", array_keys($clean)) - . "`) VALUES ('" + $r = dbq("INSERT INTO channel (" . TQUOT + . implode(TQUOT . ", " . TQUOT, array_keys($clean)) + . TQUOT . ") VALUES ('" . implode("', '", array_values($clean)) . "')" ); @@ -132,9 +132,9 @@ function import_config($channel,$configs) { unset($config['id']); $config['uid'] = $channel['channel_id']; dbesc_array($config); - $r = dbq("INSERT INTO pconfig (`" - . implode("`, `", array_keys($config)) - . "`) VALUES ('" + $r = dbq("INSERT INTO pconfig (" . TQUOT + . implode(TQUOT . ", " . TQUOT, array_keys($config)) + . TQUOT . ") VALUES ('" . implode("', '", array_values($config)) . "')" ); } @@ -163,9 +163,9 @@ function import_profiles($channel,$profiles) { $profile['thumb'] = z_root() . '/photo/profile/m/' . $channel['channel_id']; dbesc_array($profile); - $r = dbq("INSERT INTO profile (`" - . implode("`, `", array_keys($profile)) - . "`) VALUES ('" + $r = dbq("INSERT INTO profile (" . TQUOT + . implode(TQUOT . ", " . TQUOT, array_keys($profile)) + . TQUOT ") VALUES ('" . implode("', '", array_values($profile)) . "')" ); @@ -205,9 +205,9 @@ function import_hublocs($channel,$hublocs,$seize) { unset($hubloc['hubloc_id']); dbesc_array($hubloc); - $r = dbq("INSERT INTO hubloc (`" - . implode("`, `", array_keys($hubloc)) - . "`) VALUES ('" + $r = dbq("INSERT INTO hubloc (" . TQUOT + . implode(TQUOT . ", " . TQUOT, array_keys($hubloc)) + . TQUOT . ") VALUES ('" . implode("', '", array_values($hubloc)) . "')" ); @@ -244,9 +244,9 @@ function import_objs($channel,$objs) { dbesc_array($obj); - $r = dbq("INSERT INTO obj (`" - . implode("`, `", array_keys($obj)) - . "`) VALUES ('" + $r = dbq("INSERT INTO obj (" . TQUOT + . implode(TQUOT . ", " . TQUOT, array_keys($obj)) + . TQUOT . ") VALUES ('" . implode("', '", array_values($obj)) . "')" ); @@ -304,7 +304,7 @@ function sync_objs($channel,$objs) { if($exists) { unset($obj['obj_obj']); foreach($obj as $k => $v) { - $r = q("UPDATE obj SET `%s` = '%s' WHERE obj_obj = '%s' AND obj_channel = %d", + $r = q("UPDATE obj SET " . TQUOT . "%s" . TQUOT . " = '%s' WHERE obj_obj = '%s' AND obj_channel = %d", dbesc($k), dbesc($v), dbesc($hash), @@ -316,9 +316,9 @@ function sync_objs($channel,$objs) { dbesc_array($obj); - $r = dbq("INSERT INTO obj (`" - . implode("`, `", array_keys($obj)) - . "`) VALUES ('" + $r = dbq("INSERT INTO obj (" . TQUOT + . implode(TQUOT . ", " . TQUOT, array_keys($obj)) + . TQUOT . ") VALUES ('" . implode("', '", array_values($obj)) . "')" ); @@ -352,9 +352,9 @@ function import_apps($channel,$apps) { $hash = $app['app_id']; dbesc_array($app); - $r = dbq("INSERT INTO app (`" - . implode("`, `", array_keys($app)) - . "`) VALUES ('" + $r = dbq("INSERT INTO app (" . TQUOT + . implode(TQUOT . ", " . TQUOT, array_keys($app)) + . TQUOT . ") VALUES ('" . implode("', '", array_values($app)) . "')" ); @@ -451,7 +451,7 @@ function sync_apps($channel,$apps) { if($exists) { unset($app['app_id']); foreach($app as $k => $v) { - $r = q("UPDATE app SET `%s` = '%s' WHERE app_id = '%s' AND app_channel = %d", + $r = q("UPDATE app SET " . TQUOT . "%s" . TQUOT . " = '%s' WHERE app_id = '%s' AND app_channel = %d", dbesc($k), dbesc($v), dbesc($hash), @@ -461,9 +461,9 @@ function sync_apps($channel,$apps) { } else { dbesc_array($app); - $r = dbq("INSERT INTO app (`" - . implode("`, `", array_keys($app)) - . "`) VALUES ('" + $r = dbq("INSERT INTO app (" . TQUOT + . implode(TQUOT . ", " . TQUOT, array_keys($app)) + . TQUOT . ") VALUES ('" . implode("', '", array_values($app)) . "')" ); @@ -503,9 +503,9 @@ function import_chatrooms($channel,$chatrooms) { $chatroom['cr_uid'] = $channel['channel_id']; dbesc_array($chatroom); - $r = dbq("INSERT INTO chatroom (`" - . implode("`, `", array_keys($chatroom)) - . "`) VALUES ('" + $r = dbq("INSERT INTO chatroom (" . TQUOT + . implode(TQUOT . ", " . TQUOT, array_keys($chatroom)) + . TQUOT . ") VALUES ('" . implode("', '", array_values($chatroom)) . "')" ); @@ -559,7 +559,7 @@ function sync_chatrooms($channel,$chatrooms) { if($exists) { foreach($chatroom as $k => $v) { - $r = q("UPDATE chatroom SET `%s` = '%s' WHERE cr_name = '%s' AND cr_uid = %d", + $r = q("UPDATE chatroom SET " . TQUOT . "%s" . TQUOT . " = '%s' WHERE cr_name = '%s' AND cr_uid = %d", dbesc($k), dbesc($v), dbesc($name), @@ -569,9 +569,9 @@ function sync_chatrooms($channel,$chatrooms) { } else { dbesc_array($chatroom); - $r = dbq("INSERT INTO chatroom (`" - . implode("`, `", array_keys($chatroom)) - . "`) VALUES ('" + $r = dbq("INSERT INTO chatroom (" . TQUOT + . implode(TQUOT . ", " . TQUOT, array_keys($chatroom)) + . TQUOT . ") VALUES ('" . implode("', '", array_values($chatroom)) . "')" ); @@ -685,9 +685,9 @@ function import_events($channel,$events) { convert_oldfields($event,'ignore','dismissed'); dbesc_array($event); - $r = dbq("INSERT INTO event (`" - . implode("`, `", array_keys($event)) - . "`) VALUES ('" + $r = dbq("INSERT INTO event (" . TQUOT + . implode(TQUOT . ", " . TQUOT, array_keys($event)) + . TQUOT . ") VALUES ('" . implode("', '", array_values($event)) . "')" ); @@ -736,7 +736,7 @@ function sync_events($channel,$events) { if($exists) { foreach($event as $k => $v) { - $r = q("UPDATE event SET `%s` = '%s' WHERE event_hash = '%s' AND uid = %d", + $r = q("UPDATE event SET " . TQUOT . "%s" . TQUOT . " = '%s' WHERE event_hash = '%s' AND uid = %d", dbesc($k), dbesc($v), dbesc($event['event_hash']), @@ -746,9 +746,9 @@ function sync_events($channel,$events) { } else { dbesc_array($event); - $r = dbq("INSERT INTO event (`" - . implode("`, `", array_keys($event)) - . "`) VALUES ('" + $r = dbq("INSERT INTO event (" . TQUOT + . implode(TQUOT . ", " . TQUOT, array_keys($event)) + . TQUOT . ") VALUES ('" . implode("', '", array_values($event)) . "')" ); @@ -928,9 +928,9 @@ function import_likes($channel,$likes) { continue; dbesc_array($like); - $r = dbq("INSERT INTO likes (`" - . implode("`, `", array_keys($like)) - . "`) VALUES ('" + $r = dbq("INSERT INTO likes (" . TQUOT + . implode(TQUOT . ", " . TQUOT, array_keys($like)) + . TQUOT . ") VALUES ('" . implode("', '", array_values($like)) . "')" ); } @@ -961,9 +961,9 @@ function import_conv($channel,$convs) { continue; dbesc_array($conv); - $r = dbq("INSERT INTO conv (`" - . implode("`, `", array_keys($conv)) - . "`) VALUES ('" + $r = dbq("INSERT INTO conv (" . TQUOT + . implode(TQUOT . ", " . TQUOT, array_keys($conv)) + . TQUOT . ") VALUES ('" . implode("', '", array_values($conv)) . "')" ); } @@ -1118,15 +1118,15 @@ function sync_files($channel,$files) { foreach($att as $k => $v) { if($str) $str .= ","; - $str .= " `" . $k . "` = '" . $v . "' "; + $str .= " " . TQUOT . $k . TQUOT . " = '" . $v . "' "; } - $r = dbq("update `attach` set " . $str . " where id = " . intval($attach_id) ); + $r = dbq("update attach set " . $str . " where id = " . intval($attach_id) ); } else { logger('sync_files attach does not exists: ' . print_r($att,true), LOGGER_DEBUG); - $r = dbq("INSERT INTO attach (`" - . implode("`, `", array_keys($att)) - . "`) VALUES ('" + $r = dbq("INSERT INTO attach (" . TQUOT + . implode(TQUOT . ", " . TQUOT, array_keys($att)) + . TQUOT . ") VALUES ('" . implode("', '", array_values($att)) . "')" ); } @@ -1236,14 +1236,14 @@ function sync_files($channel,$files) { foreach($p as $k => $v) { if($str) $str .= ","; - $str .= " `" . $k . "` = '" . $v . "' "; + $str .= " " . TQUOT . $k . TQUOT . " = '" . $v . "' "; } - $r = dbq("update `photo` set " . $str . " where id = " . intval($exists[0]['id']) ); + $r = dbq("update photo set " . $str . " where id = " . intval($exists[0]['id']) ); } else { - $r = dbq("INSERT INTO photo (`" - . implode("`, `", array_keys($p)) - . "`) VALUES ('" + $r = dbq("INSERT INTO photo (" . TQUOT + . implode(TQUOT . ", " . TQUOT, array_keys($p)) + . TQUOT . ") VALUES ('" . implode("', '", array_values($p)) . "')" ); } diff --git a/include/items.php b/include/items.php index 333795827..b432748ee 100755 --- a/include/items.php +++ b/include/items.php @@ -1855,9 +1855,9 @@ function item_store($arr, $allow_exec = false, $deliver = true) { dbesc_array($arr); - $r = dbq("INSERT INTO `item` (`" - . implode("`, `", array_keys($arr)) - . "`) VALUES ('" + $r = dbq("INSERT INTO " . TQUOT . 'item' . TQUOT . " (" . TQUOT + . implode(TQUOT . ', ' . TQUOT, array_keys($arr)) + . TQUOT . ") VALUES ('" . implode("', '", array_values($arr)) . "')" ); @@ -2174,7 +2174,7 @@ function item_store_update($arr,$allow_exec = false, $deliver = true) { foreach($arr as $k => $v) { if($str) $str .= ","; - $str .= " `" . $k . "` = '" . $v . "' "; + $str .= " " . TQUOT . $k . TQUOT . " = '" . $v . "' "; } $r = dbq("update item set " . $str . " where id = " . $orig_post_id ); @@ -3092,9 +3092,9 @@ function mail_store($arr) { logger('mail_store: ' . print_r($arr,true), LOGGER_DATA); - $r = dbq("INSERT INTO mail (`" - . implode("`, `", array_keys($arr)) - . "`) VALUES ('" + $r = dbq("INSERT INTO mail (" . TQUOT + . implode(TQUOT . ', ' . TQUOT, array_keys($arr)) + . TQUOT . ") VALUES ('" . implode("', '", array_values($arr)) . "')" ); diff --git a/include/photo/photo_driver.php b/include/photo/photo_driver.php index 9b6d38cc1..87e4cbd0c 100644 --- a/include/photo/photo_driver.php +++ b/include/photo/photo_driver.php @@ -340,31 +340,31 @@ abstract class photo_driver { intval($p['imgscale']) ); if($x) { - $r = q("UPDATE `photo` set - `aid` = %d, - `uid` = %d, - `xchan` = '%s', - `resource_id` = '%s', - `created` = '%s', - `edited` = '%s', - `filename` = '%s', - `mimetype` = '%s', - `album` = '%s', - `height` = %d, - `width` = %d, - `content` = '%s', - `os_storage` = %d, - `filesize` = %d, - `imgscale` = %d, - `photo_usage` = %d, - `title` = '%s', - `description` = '%s', - `os_path` = '%s', - `display_path` = '%s', - `allow_cid` = '%s', - `allow_gid` = '%s', - `deny_cid` = '%s', - `deny_gid` = '%s' + $r = q("UPDATE photo set + aid = %d, + uid = %d, + xchan = '%s', + resource_id = '%s', + created = '%s', + edited = '%s', + filename = '%s', + mimetype = '%s', + album = '%s', + height = %d, + width = %d, + content = '%s', + os_storage = %d, + filesize = %d, + imgscale = %d, + photo_usage = %d, + title = '%s', + description = '%s', + os_path = '%s', + display_path = '%s', + allow_cid = '%s', + allow_gid = '%s', + deny_cid = '%s', + deny_gid = '%s' where id = %d", intval($p['aid']), @@ -395,8 +395,8 @@ abstract class photo_driver { ); } else { - $r = q("INSERT INTO `photo` - ( `aid`, `uid`, `xchan`, `resource_id`, `created`, `edited`, `filename`, mimetype, `album`, `height`, `width`, `content`, `os_storage`, `filesize`, `imgscale`, `photo_usage`, `title`, `description`, `os_path`, `display_path`, `allow_cid`, `allow_gid`, `deny_cid`, `deny_gid` ) + $r = q("INSERT INTO photo + ( aid, uid, xchan, resource_id, created, edited, filename, mimetype, album, height, width, content, os_storage, filesize, imgscale, photo_usage, title, description, os_path, display_path, allow_cid, allow_gid, deny_cid, deny_gid ) VALUES ( %d, %d, '%s', '%s', '%s', '%s', '%s', '%s', '%s', %d, %d, '%s', %d, %d, %d, %d, '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s' )", intval($p['aid']), intval($p['uid']), @@ -432,33 +432,33 @@ abstract class photo_driver { public function store($aid, $uid, $xchan, $rid, $filename, $album, $scale, $usage = PHOTO_NORMAL, $allow_cid = '', $allow_gid = '', $deny_cid = '', $deny_gid = '') { - $x = q("select id from photo where `resource_id` = '%s' and uid = %d and `xchan` = '%s' and `imgscale` = %d limit 1", + $x = q("select id from photo where resource_id = '%s' and uid = %d and xchan = '%s' and imgscale = %d limit 1", dbesc($rid), intval($uid), dbesc($xchan), intval($scale) ); if(count($x)) { - $r = q("UPDATE `photo` - set `aid` = %d, - `uid` = %d, - `xchan` = '%s', - `resource_id` = '%s', - `created` = '%s', - `edited` = '%s', - `filename` = '%s', - `mimetype` = '%s', - `album` = '%s', - `height` = %d, - `width` = %d, - `content` = '%s', - `filesize` = %d, - `imgscale` = %d, - `photo_usage` = %d, - `allow_cid` = '%s', - `allow_gid` = '%s', - `deny_cid` = '%s', - `deny_gid` = '%s' + $r = q("UPDATE photo + set aid = %d, + uid = %d, + xchan = '%s', + resource_id = '%s', + created = '%s', + edited = '%s', + filename = '%s', + mimetype = '%s', + album = '%s', + height = %d, + width = %d, + content = '%s', + filesize = %d, + imgscale = %d, + photo_usage = %d, + allow_cid = '%s', + allow_gid = '%s', + deny_cid = '%s', + deny_gid = '%s' where id = %d", intval($aid), @@ -484,8 +484,8 @@ abstract class photo_driver { ); } else { - $r = q("INSERT INTO `photo` - ( `aid`, `uid`, `xchan`, `resource_id`, `created`, `edited`, `filename`, mimetype, `album`, `height`, `width`, `content`, `filesize`, `imgscale`, `photo_usage`, `allow_cid`, `allow_gid`, `deny_cid`, `deny_gid` ) + $r = q("INSERT INTO photo + ( aid, uid, xchan, resource_id, created, edited, filename, mimetype, album, height, width, content, filesize, imgscale, photo_usage, allow_cid, allow_gid, deny_cid, deny_gid ) VALUES ( %d, %d, '%s', '%s', '%s', '%s', '%s', '%s', '%s', %d, %d, '%s', %d, %d, %d, '%s', '%s', '%s', '%s' )", intval($aid), intval($uid), diff --git a/include/zot.php b/include/zot.php index a214a1b0c..2e02b96e7 100644 --- a/include/zot.php +++ b/include/zot.php @@ -3521,8 +3521,9 @@ function process_channel_sync_delivery($sender, $arr, $deliveries) { if(count($clean)) { foreach($clean as $k => $v) { - $r = dbq("UPDATE profile set `" . dbesc($k) . "` = '" . dbesc($v) - . "' where profile_guid = '" . dbesc($profile['profile_guid']) . "' and uid = " . intval($channel['channel_id'])); + $r = dbq("UPDATE profile set " . TQUOT . dbesc($k) . TQUOT . " = '" . dbesc($v) + . "' where profile_guid = '" . dbesc($profile['profile_guid']) + . "' and uid = " . intval($channel['channel_id'])); } } } -- cgit v1.2.3