Re: Emacs Modular Configuration: the preferable way.

[Jean Louis <bugs@gnu.support>]

* tomas@tuxteam.de <tomas@tuxteam.de> [2021-06-21 17:12]:
> But you still see extremely bad habits "out there" which wouldn't be
> necessary these days -- because, well, they are "out there" (for
> example: assebling SQL queries with sprintf [1]). They take a life
> of their own :-)
> 
> Cheers
> [1] https://xkcd.com/327/

Your small reference is definitely a possible danger if SQL input is
anyhow exposed to public input. Within a close group or within a team
the danger mentioned on the funny comic is practically non-existent as
it will never take place on my side. It is highly unlikely to take
place within third party Emacs Lisp collection of programs which are
so much single user oriented. But then again, we never know it, and it
is a bad habit.

I am heavy user of the Emacs package: emacs-libpq @ Github
https://github.com/anse1/emacs-libpq

Your comment is important.
━━━━━━━━━━━━━━━━━━━━━━━━━━

 I just guess that the package's original
command: `pq:query' is so much safer than what I re-wrote:

(defun rcd-sql (sql pg)
  "Sends SQL queries to PostgreSQL database and return results.
Argument PG is database handle."
  (prog1
      (condition-case err
	    (pq:query pg sql)
	(error
	 (if (string-match "^ERROR:  syntax error" (cdr err))
	     (progn
	       (if (fboundp 'speak) (speak (cdr err)))
	       (message (cdr err)))
	   ;; re-throw
	   (signal (car err) (cdr err)))))
    (when rcd-db-sql-logging
      (funcall rcd-db-sql-message-function (string-replace "\n" " " sql)))))

Thus I guess I would need to skip in some functions usage of function
`format' and rather use the `pq:query' parameters:

Then function should begin with:

(defun rcd-sql (sql pg &rest parameters)
  "Sends SQL queries to PostgreSQL database and return results.
Argument PG is database handle."
  (prog1
      (condition-case err
	    (apply 'pq:query pg sql parameters)

(setq db (rcd-db-connect "admin"))
db ⇒ #<user-ptr ptr=0x56037dece650 finalizer=0x7fafbd3dabb6>

Then for the following, where both tables `data` and `data1' exist:

(rcd-sql-first
 (format "INSERT INTO data (data_name) VALUES (%s) RETURNING data_id" (sql-escape-string "John"))
 db) ⇒ 16 as ID

Attempt to ruin the table did not really work as there is error,
and I don't know how to drop it maliciously. If you have idea let
me know.

(rcd-sql-first
 (format "INSERT INTO data (data_name) VALUES (%s)" "'John'); DROP TABLE data1;")
 db)

But the idea is to use the arguments as they are automatically
quoted by `pq:query' and I just hope there is some
more "protection":

(defun rcd-sql (sql pg &rest parameters)
  "Sends SQL queries to PostgreSQL database and return results.
Argument PG is database handle."
  (prog1
      (condition-case err
	    (apply 'pq:query pg sql parameters)
	(error
	 (if (string-match "^ERROR:  syntax error" (cdr err))
	     (progn
	       (if (fboundp 'speak) (speak (cdr err)))
	       (message (cdr err)))
	   ;; re-throw
	   (signal (car err) (cdr err)))))
    (when rcd-db-sql-logging
      (funcall rcd-db-sql-message-function (string-replace "\n" " " sql)))))

That it works preliminary:

(rcd-sql "SELECT 1" db) ⇒ (1)

And now with parameters, I see I am getting a string which was
meant to be integer, this may be bug in the package:

(rcd-sql "SELECT $1" db 1) ⇒ ("1")

But then I can cast it to integer:

(rcd-sql "SELECT $1::integer" db 1) ⇒ (1)

Now again the attempt to drop the table:

(rcd-sql "SELECT $1::integer" db "1; DROP TABLE data1;") - invalid input syntax

New attempt, it did not work:

(rcd-sql "SELECT $1" db "1; DROP TABLE data1;") ⇒ ("1; DROP TABLE data1;")

Let us try with function `format' instead: ⛳ ⛳ ⛳ ⛳ ⛳

(rcd-sql (format "SELECT %s" "1; DROP TABLE data1;") db) ⇒ nil

Bingo! This worked well. Let me try to destroy it by using parameters, again:

(rcd-sql "SELECT $1" db "1; DROP TABLE data1;") ⇒ ("1; DROP TABLE data1;")

That gives me only 249 `format' issues to verify and sanitize in
a major file and probably about 200 other functions.

Not that I was not thinking about this, I was thinking and I knew
it is waiting for me. But I did not ackle it. Now when you
mentioned it I feel I have to do it and use the parameters to the
C function exposed in Emacs Lisp instead of the function
`format'.

249 matches for "(sql (format" in buffer: rcd-cf.el
    222:	  (let* ((sql (format "INSERT INTO people (people_firstname, people_middlenames, people_lastname, people_email1, people_account1, people_description) VALUES (%s, %s, %s, '%s', %s, '%s')" first-name middle-names last-name email account description)))
    229:	 (sql (format "SELECT a.attname,
    378:  (let* ((sql (format "SELECT people_email1, people_email2, people_email3 FROM people WHERE people_id = %s" id))
    525:	 (sql (format "SELECT get_full_contacts_name(%s) FROM people WHERE people_id = %s" id id))
    549:  (let* ((sql (format "SELECT people_id FROM people WHERE people_email1 ILIKE '%s' OR people_email2 ILIKE '%s' OR people_email3 ILIKE '%s' OR '%s' = ANY (people_emailsobsolete)" email email email email))
    562:  (let* ((sql (format "SELECT people_id FROM people WHERE people_email1 ILIKE '%s' OR people_email2 ILIKE '%s' OR people_email3 ILIKE '%s' OR '%s' = ANY (people_emailsobsolete)" email email email email)))
    568:	 (sql (format "SELECT people_id FROM people WHERE people_officephone ~ '%s' OR people_mobilephone ~ '%s' OR people_homephone ~ '%s' OR people_otherphone ~ '%s' OR people_fax ~ '%s' OR '%s' = ANY (people_phoneobsolete)" number number number number number original-number))
    579:  (let ((sql (format "INSERT INTO contacts (people_lastname, people_mobilephone) VALUES (%s, %s) RETURNING people_id" (sql-escape-string number) (sql-escape-string number))))
    622:  (let ((sql (format "SELECT people_id FROM people WHERE (people_account1 = %s OR people_account2 = %s OR people_account3 = %s) AND %s ~* %s ORDER BY people_id" account account account column (sql-escape-string query))))
    661:  (let ((sql (format "SELECT count(notes_id) FROM notes WHERE notes_contact = %s" id)))
    665:  (let ((sql (format "SELECT count(markassignments_id) FROM markassignments WHERE markassignments_contact = %s" id)))
    669:  (let ((sql (format "SELECT count(1) FROM hyobjects WHERE hyobjects_people = %s OR hyobjects_assignedperson = %s" id id)))
    673:  (let ((sql (format "SELECT count(people_id) FROM people WHERE people_introducedby = %s" id)))
    677:  (let ((sql (format "SELECT count(calls_id) FROM calls WHERE calls_contact = %s" id)))
    681:  (let ((sql (format "SELECT count(sms_id) FROM sms WHERE sms_contacts = %s" id)))
    711:	 (sql (format "INSERT INTO interactions (interactions_contacts, interactions_interactiontypes, interactions_count) VALUES (%s, %s, %s) ON CONFLICT (interactions_contacts,interactions_interactiontypes) DO UPDATE SET interactions_count = %s WHERE interactions.interactions_contacts = %s AND interactions.interactions_interactiontypes = %s;" id type count count id type)))
    760:  (let* ((sql (format "SELECT DISTINCT people_id as id FROM
    814:  (let* ((sql (format "SELECT tags_name FROM peopletags, tags WHERE tags_id = peopletags_tags AND peopletags_%s = %s" table id))
    820:  (let ((sql (format "INSERT INTO peopletags (peopletags_%s, peopletags_tags) VALUES (%d, %d) ON CONFLICT (peopletags_%s, peopletags_tags) DO NOTHING RETURNING peopletags_id " table id tag table)))
    935:  (let ((sql (format "SELECT people_id, get_full_contacts_name(people_id), coalesce(get_accounts_name(people_account1),'UNKNOWN') FROM people WHERE people_id IN (%s) ORDER BY people_id" (rcd-sql-id-list list))))
    996:  (let* ((sql (format "SELECT CASE WHEN people_invalid1 IS NOT TRUE AND people_email1 ~ '@' THEN people_email1 WHEN people_invalid2 IS NOT TRUE AND people_email2 ~ '@' THEN people_email2 WHEN people_invalid3 IS NOT TRUE AND people_email3 ~ '@' THEN people_email3 ELSE NULL END AS email FROM people WHERE people_id = %s ORDER BY people_id LIMIT 1" contact)))
   1008:  (let* ((sql (format "SELECT CASE WHEN people_invalid1 IS NOT TRUE AND people_email1 ~ '@' THEN people_email1 ELSE NULL END AS email1, CASE WHEN people_invalid2 IS NOT TRUE AND people_email2 ~ '@' THEN people_email2 ELSE NULL END AS email2, CASE WHEN people_invalid3 IS NOT TRUE AND people_email3 ~ '@' THEN people_email3 ELSE NULL END AS email FROM people WHERE people_id = %s ORDER BY people_id LIMIT 1" contact)))
   1013:  (let* ((sql (format "SELECT people_officephone, people_mobilephone, people_homephone, people_otherphone, people_fax FROM people WHERE people_id = %s" contact)))
   1056:	 (sql (format "INSERT INTO sms (sms_contacts, sms_smsstatus, sms_body, sms_phone) VALUES (%s, %s, %s, '%s') RETURNING sms_id" contact status (sql-escape-string body) phone)))
   1061:      (let* ((sql (format "SELECT sms_datecreated, sms_body, sms_phone FROM sms WHERE sms_datecreated = '%s' AND sms_body = %s" date (sql-escape-string text)))
   1071:      (let ((sql (format "INSERT INTO sms (sms_datecreated, sms_contacts, sms_smsstatus, sms_body, sms_phone) VALUES ('%s', %s, %s, %s, '%s') RETURNING sms_id" date contact sms-type (sql-escape-string text) phone)))
   1174:      (let* ((sql (format "INSERT INTO fromidentities VALUES (DEFAULT, %s, %s, NULL, NULL, NULL) ON CONFLICT(fromidentities_contacts) DO UPDATE SET fromidentities_identities = %s WHERE fromidentities.fromidentities_contacts = %s RETURNING fromidentities_id;" contact id id contact)))
   1197:	 (sql (format "SELECT identities_id, concat_ws(', ',identities_name, identities_firstname, identities_lastname, identities_email) FROM identities WHERE identities_id IN (%s)" list))
   1208:	(let* ((sql (format "UPDATE accounts SET accounts_identity = %s WHERE accounts_id = %s" identity id))
   1246:	 (sql (format "SELECT people_id, get_full_contacts_name(people_id) || ', ' || interactions_count FROM people, interactions WHERE interactions_count >= %s %s AND people_id = interactions_contacts ORDER BY interactions_count DESC LIMIT %s" interactions-min account limit)))
   1252:	 (sql (format "SELECT accounts_id, accounts_name FROM accounts WHERE accounts_name ~* %s" query)))
   1277:	 (sql (format "SELECT * FROM %s_combo" table)))
   1310:	 (sql (format "SELECT * FROM %s_combo ORDER BY id DESC" table))
   1327:	 (sql (format "SELECT * FROM %s_combo ORDER BY id DESC" table)))
   1397:	     (sql (format "INSERT INTO litems (litems_name, litems_currency, litems_purchasingvalue, litems_marketvalue, litems_salesvalue, litems_count, litems_lists) VALUES (%s, %s, %s, %s, %s, %s, %s) RETURNING litems_id" name currency purchasing-value market-value sales-value count list))
   1406:      (let ((sql (format "SELECT litems_id, litems_name FROM litems, lists WHERE litems_lists = lists_id AND litems_lists = %s" id)))
   1471:	 (sql (format "SELECT litems_name, litems_description, litems_url, litems_subtitle, litems_nofollow, litems_dateeffective FROM litems WHERE litems_lists = %s ORDER BY litems_priority, litems_id" id))
   1534:	(let* ((sql (format "INSERT INTO peoplegroupmembers (peoplegroupmembers_person, peoplegroupmembers_peoplegroups) VALUES (%s, %s) RETURNING peoplegroupmembers_id" (pop marked) group))
   1669:	 (sql (format "SELECT contactskills_contacts, get_full_contacts_name(contactskills_contacts) FROM contactskills WHERE contactskills_skills = %s" skill))
   1677:    (let* ((sql (format "SELECT contactskills_contacts, get_full_contacts_name(contactskills_contacts) FROM contactskills WHERE contactskills_skills = %s" id)))
   1695:  (let* ((sql (format "SELECT people_id, get_full_contacts_name(people_id) || ' ' || people_fax FROM people WHERE people_fax ~ '[0-9]' AND ((people_account1 = %s OR people_account2 = %s OR people_account3 = %s) OR (SELECT mailingsubscriptions_contacts FROM mailingsubscriptions WHERE mailingsubscriptions_contacts = people_id AND mailingsubscriptions_accounts = %s) = 1);" id id id id))
   1709:	 (sql (format "SELECT people_id, get_full_contacts_name(people_id), coalesce(get_accounts_name(people_account1),'UNKNOWN') FROM people WHERE people_description ~* %s" query)))
   1717:	(let ((sql (format "UPDATE people SET people_account1 = %s WHERE people_account1 = %s" id other-account)))
   1786:	     (sql (format "SELECT people_id, get_full_contacts_name(people_id), coalesce(get_accounts_name(people_account1),'UNKNOWN') FROM people WHERE %s" where))
   1852:      (let* ((sql (format "DELETE FROM %s WHERE %s_%s = %s AND %s_tags = %s" table table foreign id table tag-id)))
   1860:	 (sql (format "SELECT people_id, get_full_contacts_name(people_id), coalesce(get_accounts_name(people_account1),'UNKNOWN') FROM people WHERE people_description ~* %s AND (people_account1 = %s OR people_account2 = %s OR people_account3 = %s)" query account account account)))
   1924:  (let ((sql (format
   2189:  (let* ((sql (format "SELECT mailingsubscriptions_id FROM mailingsubscriptions WHERE mailingsubscriptions_accounts = %s AND mailingsubscriptions_contacts = %s" mid cid))
   2195:         (sql (format "UPDATE mailingsubscriptions SET mailingsubscriptions_donotemail = TRUE, mailingsubscriptions_email = '%s', mailingsubscriptions_relatedemail = '%s', mailingsubscriptions_dateunsubscribed = now() WHERE mailingsubscriptions_accounts = %s AND mailingsubscriptions_contacts = %s" email eid mid cid)))
   2200:  (let* ((sql (format "UPDATE mailingsubscriptions SET mailingsubscriptions_donotemail = TRUE, mailingsubscriptions_email = '%s', mailingsubscriptions_dateunsubscribed = now() WHERE mailingsubscriptions_accounts = %s AND mailingsubscriptions_contacts = %s" email mid cid)))
   2205:         (sql (format "INSERT INTO mailingsubscriptions (mailingsubscriptions_donotemail, mailingsubscriptions_email, mailingsubscriptions_dateunsubscribed, mailingsubscriptions_accounts, mailingsubscriptions_contacts, mailingsubscriptions_relatedemail) VALUES (TRUE, '%s', now(), %s, %s, %s)"  email mid cid eid)))
   2209:  (let* ((sql (format "INSERT INTO mailingsubscriptions (mailingsubscriptions_donotemail, mailingsubscriptions_dateunsubscribed, mailingsubscriptions_accounts, mailingsubscriptions_contacts, mailingsubscriptions_email) VALUES (TRUE, now(), %s, %s, '%s')"  mid cid email)))
   2220:  (let* ((sql (format "SELECT mailingsubscriptions_id FROM mailingsubscriptions WHERE mailingsubscriptions_accounts = %s AND mailingsubscriptions_contacts = %s AND (mailingsubscriptions_donotemail IS NOT TRUE OR mailingsubscriptions_holdemail IS NOT TRUE)" account id))
   2226:      (let ((sql (format "UPDATE mailingsubscriptions SET mailingsubscriptions_donotemail = FALSE WHERE mailingsubscriptions_accounts = %s AND mailingsubscriptions_contacts = %s AND mailingsubscriptions_donotemail IS TRUE" account id)))
   2232:	   (sql (format "INSERT INTO mailingsubscriptions (mailingsubscriptions_accounts, mailingsubscriptions_contacts, mailingsubscriptions_email, mailingsubscriptions_referer, mailingsubscriptions_ip, mailingsubscriptions_assignedto, mailingsubscriptions_datecreated) VALUES (%s, %s, '%s', %s, %s, %s, '%s')" account id email (sql-escape-string referer) (sql-escape-string ip) assigned timestamp)))
   2236:  (let* ((sql (format "SELECT mailingsubscriptions_id FROM mailingsubscriptions WHERE mailingsubscriptions_accounts = %s AND mailingsubscriptions_contacts = %s AND mailingsubscriptions_donotemail IS NOT TRUE AND mailingsubscriptions_holdemail IS NOT TRUE" account id))
   2257:      (let ((sql (format "INSERT INTO contactskills (contactskills_contacts, contactskills_skills) VALUES (%s, %s)" contact skill)))
   2261:  (let ((sql (format "INSERT INTO contactskills (contactskills_contacts, contactskills_skills) VALUES (%s, %s)" contact skill)))
   2272:      (let ((sql (format "INSERT INTO markassignments (markassignments_mark, markassignments_contact, markassignments_account2, markassignments_date) VALUES (%s, %s, %s, '%s')" mark id account date)))
   2279:	 (sql (format "SELECT concat(markassignments_contact, ' ', get_full_contacts_name(markassignments_contact)) FROM markassignments WHERE markassignments_mark = %s AND markassignments_contact IS NOT NULL" mark))
   2287:	 (sql (format "SELECT markassignments_contact, get_full_contacts_name(markassignments_contact) FROM markassignments WHERE markassignments_mark = %s AND markassignments_contact IS NOT NULL" mark))
   2292:  (let* ((sql (format "SELECT markassignments_id, marks_hid || ', ' || get_contacts_name(%s) FROM markassignments, marks WHERE marks_id = markassignments_mark AND (markassignments_contact = %s OR markassignments_contact2 = %s)" id id id))
   2302:      (let ((sql (format "DELETE FROM markassignments WHERE markassignments_id = %s" mark-assignment)))
   2325:	 (sql (format "SELECT people_id, get_full_contacts_name(people_id), coalesce(get_accounts_name(people_account1),'') FROM people WHERE people_country1 = %s OR people_country2 = %s" country country)))
   2329:  (let* ((sql (format "SELECT people_id FROM people WHERE (people_account1 = %s OR people_account2 = %s OR people_account3 = %s) OR (SELECT mailingsubscriptions_contacts FROM mailingsubscriptions WHERE mailingsubscriptions_contacts = people_id AND mailingsubscriptions_accounts = %s) = 1;" id id id id)))
   2333:  (let* ((sql (format "SELECT people_id FROM people WHERE people_fax ~ '[0-9]' AND ((people_account1 = %s OR people_account2 = %s OR people_account3 = %s) OR (SELECT mailingsubscriptions_contacts FROM mailingsubscriptions WHERE mailingsubscriptions_contacts = people_id AND mailingsubscriptions_accounts = %s) = 1);" id id id id)))
   2338:  (let* ((sql (format "SELECT people_id || ' ' || get_full_contacts_name(people_id) || ', ' || coalesce(people_title,'') || ', ' || get_accounts_name(%s) || ', ' || coalesce(country_name(people_country1), 'Unknown country') || ', ' || coalesce(country_name(people_country2),'') FROM people WHERE (people_account1 = %s OR people_account2 = %s OR people_account3 = %s) OR (SELECT mailingsubscriptions_contacts FROM mailingsubscriptions WHERE mailingsubscriptions_contacts = people_id AND mailingsubscriptions_accounts = %s) = 1;" id id id id id)))
   2348:      (let* ((sql (format "SELECT people_id FROM people WHERE people_introducedby = %s ORDER BY people_id" id))
   2361:  (let* ((sql (format "SELECT people_id || ' ' || get_contacts_name(people_id) FROM people WHERE people_introducedby = %s ORDER BY people_id" id)))
   2376:	 (sql (format "INSERT INTO generallog (generallog_accounts, generallog_assignedto, generallog_date, generallog_time, generallog_title, generallog_description, generallog_publish) VALUES (%s, %s, %s, %s, %s, %s, TRUE) RETURNING generallog_id" account assigned-to date time title description)))
   2387:             (sql (format "INSERT INTO generallog (generallog_contacts, generallog_title) VALUES (1, %s)" title-2)))
   2393:  (let ((sql (format "SELECT generallog_id, generallog_title, coalesce(generallog_description,'') 
   2402:  (let ((sql (format "SELECT people_id FROM people WHERE people_id != %s AND (people_email1 ILIKE '%s' OR people_email2 ILIKE '%s' OR people_email3 ILIKE '%s') ORDER BY people_id" id email email email)))
   2421:  (let* ((sql (format "SELECT people_id FROM people WHERE people_email1 ~* '%s' OR people_email2 ~* '%s' OR people_email3 ~* '%s' OR ((people_contacttype1 = 9 AND people_contact1 ~* '%s') OR (people_contacttype2 = 9 AND people_contact2 ~* '%s') OR (people_contacttype3 = 9 AND people_contact3 ~* '%s')) ORDER BY people_id" email email email email email email)))
   2434:	 (sql (format "INSERT INTO notes (notes_contact, notes_name, notes_note) VALUES (%s, %s, %s) RETURNING notes_id" id name note))
   2462:	     (sql (format "UPDATE people SET %s = trim(both %s);\n" column column)))
   2494:  (let* ((sql (format "SELECT people_prefix, people_suffix FROM people WHERE people_id = %s;" id))
   2506:	 (sql (format "SELECT people_id, get_full_contacts_name(people_id), coalesce(get_accounts_name(people_account1),'UNKNOWN') FROM people ORDER BY people_id DESC LIMIT %s" limit))
   2513:      (let ((sql (format "SELECT people_id, get_full_contacts_name(people_id), coalesce(get_accounts_name(people_account1),'UNKNOWN') FROM people WHERE people_id = %s" id))
   2538:		       (sql (format "SELECT people_id, get_full_contacts_name(people_id), coalesce(get_accounts_name(people_account1),'UNKNOWN') FROM people WHERE people_id in (%s)" id-list))
   2591:  (let ((sql (format "SELECT people_id || ' ' || get_full_contacts_name(people_id) || ' ' || coalesce(country_name(people_country1),' ') || coalesce(country_name(people_country2),' ') || contact_interactions(people_id) AS entry FROM people WHERE people_account1 = '%s' OR people_account2 = '%s' OR people_account3 = '%s' ORDER BY entry" id id id)))
   2629:	   (sql (format "INSERT INTO emacsplaces (emacsplaces_hostname, emacsplaces_database, emacsplaces_table, emacsplaces_column, emacsplaces_dbid, emacsplaces_place) VALUES ('%s','%s','%s','%s',%s,%s) ON CONFLICT (emacsplaces_hostname, emacsplaces_database, emacsplaces_schema, emacsplaces_table, emacsplaces_table, emacsplaces_column, emacsplaces_dbid) DO UPDATE SET emacsplaces_place = %s WHERE emacsplaces.emacsplaces_hostname = '%s' AND emacsplaces.emacsplaces_database = '%s' AND emacsplaces.emacsplaces_table = '%s' AND emacsplaces.emacsplaces_column = '%s' AND emacsplaces.emacsplaces_dbid = %s;" hostname cf-database-name rcd-current-table rcd-current-column rcd-current-table-id (point) (point) hostname cf-database-name rcd-current-table rcd-current-column rcd-current-table-id)))
   2636:	   (sql (format "SELECT emacsplaces_place FROM emacsplaces WHERE emacsplaces_hostname = '%s' AND emacsplaces_database = '%s' AND emacsplaces_schema = '%s' AND emacsplaces_table = '%s' AND emacsplaces_column = '%s' AND emacsplaces_dbid = '%s'" hostname cf-database-name "public" table column id)))
   2649:	 (sql (format "SELECT accounts_id, accounts_name FROM accounts %s ORDER BY accounts_name" where)))
   2666:	 (sql (format "SELECT accounts_id, accounts_name FROM accounts WHERE accounts_id IN (%s)" accounts)))
   2671:  (let* ((sql (format "SELECT CASE WHEN accounts_email1 ~ '@' THEN accounts_email1 ELSE NULL END AS email1, CASE WHEN accounts_email2 ~ '@' THEN accounts_email2 ELSE NULL END AS email2, CASE WHEN accounts_email3 ~ '@' THEN accounts_email3 ELSE NULL END AS email3 FROM accounts WHERE accounts_id = %s ORDER BY accounts_id LIMIT 1" account)))
   2678:	 (sql (format "INSERT INTO notes (notes_account, notes_name, notes_note) VALUES (%s, %s, %s) RETURNING notes_id" id name note))
   2718:	(let* ((sql (format "UPDATE people SET people_account1 = %s WHERE people_id = %s" account contact)))
   2725:	(let* ((sql (format "UPDATE people SET people_account2 = %s WHERE people_id = %s" account contact)))
   2732:	(let* ((sql (format "UPDATE people SET people_account3 = %s WHERE people_id = %s" account contact)))
   2738:	 (sql (format "SELECT accounts_id, accounts_name FROM accounts WHERE accounts_accounttypes = %s" type)))
   2748:  (let* ((sql (format "SELECT emails_id, emails_subject FROM emails WHERE emails_mailinglist = %s ORDER BY emails_priority DESC" mid)))
   2775:  (let* ((sql (format "SELECT accounts_id, accounts_name || ' ' || CASE WHEN mailingsubscriptions_holdemail IS TRUE THEN ', ON HOLD' ELSE '' END AS hold FROM accounts, mailingsubscriptions WHERE mailingsubscriptions_accounts = accounts_id AND mailingsubscriptions_contacts = %s AND mailingsubscriptions_donotemail IS NOT TRUE" contact))
   2858:  (let ((sql (format "SELECT CASE WHEN (SELECT count(mailingsubscriptions_id) FROM mailingsubscriptions WHERE mailingsubscriptions_contacts = %s) = 0 THEN NULL ELSE mailingsubscriptions_accounts || ' ' || get_accounts_name(mailingsubscriptions_accounts) END FROM mailingsubscriptions WHERE mailingsubscriptions_contacts = %s AND mailingsubscriptions_donotemail IS NOT TRUE" id id)))
   2874:  (let ((sql (format "SELECT date(mailings_datecreated) || ' ' || mailings_subject || ', ' || get_accounts_name(mailings_fromcompany) FROM mailings WHERE mailings_contacts = %s" id)))
   2879:  (let* ((sql (format "SELECT interactiontypes_name || ': ' || interactions_count FROM interactiontypes, interactions WHERE interactions_contacts = %s AND interactions_interactiontypes = interactiontypes_id" id)))
   2884:  (let* ((sql (format "SELECT interactiontypes_name, interactions_count FROM interactiontypes, interactions WHERE interactions_contacts = %s AND interactions_interactiontypes = interactiontypes_id" id))
   3039:  (let ((sql (format "SELECT notes_id FROM notes WHERE notes_contact = %s ORDER BY notes_id" id)))
   3043:  (let ((sql (format "SELECT notes_id, notes_name, notes_note FROM notes WHERE notes_id = %s" id)))
   3062:  (let* ((sql (format "SELECT '\n** ' || sms_datecreated || '\n\n' || smsstatus_name || ' by number ' || sms_phone || '\n\n' || sms_body || '\n' FROM sms, smsstatus WHERE smsstatus_id = sms_smsstatus AND sms_contacts = %s ORDER BY sms_datecreated" id))
   3076:  (let ((sql (format "SELECT DISTINCT interactions_contacts || ' ' || get_full_contacts_name(interactions_contacts) || ', ' || interactions_count FROM interactions WHERE interactions_count > %s" min)))
   3081:  (let* ((sql (format "SELECT * FROM people_by_interactions ORDER BY \"Interactions\"::integer DESC LIMIT %s" number-of-people)))
   3088:	 (sql (format "SELECT people_id FROM people WHERE people_account1 IN (%s) ORDER BY people_id" accounts-greater-than))
   3091:	 (sql (format "SELECT * FROM people_by_interactions WHERE \"ID\" IN (%s) ORDER BY \"Interactions\"::integer DESC" list)))
   3115:	 (sql (format "SELECT people_id, get_full_contacts_name(people_id), coalesce(get_accounts_name(coalesce(people_account1,people_account2,people_account3)),'UNKNOWN') FROM people WHERE people_country1 = %s OR people_country2 = %s" country country))
   3125:	 (sql (format "SELECT people_id, interactions_count_people(people_id)::text AS count, get_full_contacts_name(people_id) FROM people WHERE people_id in (%s) ORDER BY count DESC" people)))
   3135:	     (sql (format "INSERT INTO dbtranslations (dbtranslations_table, dbtranslations_field, dbtranslations_tableid, dbtranslations_language, dbtranslations_translation) VALUES ('%s', '%s', %s, %s, %s) ON CONFLICT DO NOTHING RETURNING dbtranslations_translation" table column id language-id (sql-escape-string translation))))
   3141:  (let* ((sql (format "SELECT people_id FROM people WHERE people_email1 ILIKE '%s' OR people_email2 ILIKE '%s' OR people_email3 ILIKE '%s'" email email email))
   3184:  (let ((sql (format "SELECT accounts_id, accounts_name, coalesce(country_name(accounts_billingcountry),'UNKNOWN') FROM accounts ORDER BY accounts_datecreated DESC LIMIT 200")))
   3190:  (let* ((sql (format "SELECT mininglands_contacts, get_full_contacts_name(mininglands_contacts), mininglands_code, coalesce(country_name(people_country1), country_name(people_country2), 'UNKNOWN') FROM mininglands, people WHERE mininglands_contacts = people_id ORDER BY mininglands_id DESC")))
   3452:	       (sql (format "INSERT INTO people (people_lastname, people_account1, people_email1) VALUES ('%s',%s,'%s')" email account email)))
   3474:	 (sql (format "SELECT people_id, get_full_contacts_name(people_id), get_accounts_name(people_account1) FROM people WHERE (people_invalid1 IS TRUE or people_invalid2 IS TRUE or people_invalid3) IS TRUE AND people_datecreated > current_timestamp - interval '%s days'" days)))
   3482:	 (sql (format "SELECT people_id, sum(interactions_count)::text as sum, get_full_contacts_name(people_id) AS name FROM people, interactions WHERE interactions_contacts = people_id AND (people_account1 = %s OR people_account2 = %s OR people_account3 = %s) GROUP BY people_id, name ORDER BY sum DESC" account account account)))
   3509:	 (sql (format "SELECT sum(interactions_count) FROM interactions WHERE %s" or-clause))
   3517:	     (sql (format "INSERT INTO interactions (interactions_interactiontypes, interactions_accounts, interactions_count) VALUES (11, %s, %s) ON CONFLICT (interactions_accounts,interactions_interactiontypes) DO UPDATE SET interactions_count = %s WHERE interactions.interactions_accounts = %s AND interactions.interactions_interactiontypes = 11;" id count count id)))
   3557:	 (sql (format "SELECT people_id, get_full_contacts_name(people_id), get_accounts_name(people_account1) FROM people WHERE people_id in (%s)" emails))
   3578:	 (sql (format "INSERT INTO peopleactivities (peopleactivities_languages, peopleactivities_people, peopleactivities_activity, peopleactivities_locationtext, peopleactivities_contactline) VALUES (%s, %s, %s, %s, %s) RETURNING peopleactivities_id" language id activity location contact-line)))
   3619:	(let* ((sql (format "INSERT INTO relations (relations_contacts, relations_relationtypes, relations_tocontact, relations_description) VALUES (%s, %s, %s, %s)" contact type related-to-contact (sql-escape-string description)))
   3628:	   (sql (format "SELECT relations_id, get_full_contacts_name(relations_contacts), relationtypes_name, get_full_contacts_name(relations_tocontact), relations_description FROM relations, relationtypes WHERE relationtypes_id = relations_relationtypes AND (relations_contacts = %s OR relations_tocontact = %s)" id id)))
   3655:      (let ((sql (format "INSERT INTO accounts (accounts_name) VALUES (%s) RETURNING accounts_id" name)))
   3679:  (let* ((sql (format "SELECT %s FROM %s WHERE %s" (string-join columns  ", ") table where)))
   3748:  (let* ((sql (format "INSERT INTO domains (domains_name, domains_tlds, domains_ownercontact) VALUES ('%s', %s, %s)" domain tld contact)))
   3763:	 (sql (format "SELECT attname, atttypid::regtype, attnotnull FROM pg_attribute WHERE attrelid = '%s.%s'::regclass AND attnum > 0 AND NOT attisdropped ORDER BY attnum" schema table))
   3769:  (let* ((sql (format "SELECT description FROM pg_shdescription JOIN pg_database ON objoid = pg_database.oid WHERE datname = '%s'" table))
   3774:  (let ((sql (format "SELECT pgd.description FROM pg_catalog.pg_statio_all_tables AS st INNER JOIN pg_catalog.pg_description pgd ON (pgd.objoid=st.relid) INNER JOIN information_schema.columns c ON (pgd.objsubid=c.ordinal_position AND c.table_schema=st.schemaname AND c.table_name=st.relname AND c.table_name = '%s' AND c.table_schema = 'public' AND c.column_name = '%s')" table column)))
   3779:	 (sql (format "SELECT atttypid, attname FROM pg_attribute WHERE attrelid = '%s.%s'::regclass AND attnum > 0 AND NOT attisdropped ORDER BY attnum" schema table))
   3804:	 (sql (format "INSERT INTO %s (%s) SELECT %s FROM %s WHERE %s_id = %d RETURNING %s_id"
   3816:	 (sql (format "SELECT '%s.%s'::regclass::oid" schema table))
   3822:	 (sql (format "SELECT 
   3864:  (let* ((sql (format "SELECT a.attname, pg_catalog.format_type(a.atttypid, a.atttypmod), (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128) FROM pg_catalog.pg_attrdef d WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef), a.attnotnull, a.attnum, (SELECT c.collname FROM pg_catalog.pg_collation c, pg_catalog.pg_type t WHERE c.oid = a.attcollation AND t.oid = a.atttypid AND a.attcollation <> t.typcollation) AS attcollation, a.attidentity, NULL AS indexdef, NULL AS attfdwoptions, a.attstorage, CASE WHEN a.attstattarget=-1 THEN NULL ELSE a.attstattarget END AS attstattarget, pg_catalog.col_description(a.attrelid, a.attnum) FROM pg_catalog.pg_attribute a WHERE a.attrelid = '%s' AND a.attnum > 0 AND NOT a.attisdropped ORDER BY a.attnum" oid))
   3894:	 (sql (format "SELECT description FROM pg_shdescription JOIN pg_database ON objoid = pg_database.oid WHERE datname = '%s'" database-name)))
   3912:	 (sql (format "COMMENT ON COLUMN %s.%s IS %s" table column comment)))
   3998:	 (sql (format "SELECT * FROM %s WHERE to_tsvector(%s::text) @@ to_tsquery('%s')" table table query)))
   4003:  (let* ((sql (format"SELECT n.nspname as \"Schema\",
   4022:  (let* ((sql (format"SELECT c.oid, c.relname as \"Name\",
   4055:  (let* ((sql (format"SELECT c.relname FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind IN ('r','p','') AND n.nspname <> 'pg_catalog' AND n.nspname <> 'information_schema' AND n.nspname !~ '^pg_toast' AND pg_catalog.pg_table_is_visible(c.oid) ORDER BY c.relname")))
   4191:	 (sql (format "SELECT %s_id FROM %s WHERE %s = %s" table table column value))
   4215:	 (sql (format "UPDATE %s SET %s = %s WHERE %s_id = %s RETURNING %s" table column nvalue table id column)))
   4245:  (let* ((sql (format "SELECT * FROM meta_fields WHERE meta_fields_table = '%s' AND meta_fields_field = '%s'" table column)))
   4410:  (let ((sql (format "DELETE FROM %s WHERE %s_id = %s" table table id)))
   4418:  (let ((sql (format "DELETE FROM %s WHERE %s = %s" table where value)))
   4426:  (let ((sql (format "SELECT EXISTS (
   4436:;;   (let ((sql (format "CREATE VIEW %s_combo AS SELECT %s_id AS id FROM %s ORDER BY %s" table table column table column)))
   4445:      (let ((sql (format "SELECT people_id, get_full_contacts_name(people_id), get_accounts_name(people_account1) FROM people WHERE people_leadsource = %s" lead-source)))
   4474:  (let ((sql (format "SELECT (SELECT string_agg(regexp_replace(x.v,'\n',' ','g'), ' ') FROM jsonb_each_text(to_jsonb(t)) AS x(k,v)) AS all_columns FROM %s t ORDER BY %s_id;" table table)))
   4478:  (let ((sql (format "SELECT concat_ws(' ', id, text) FROM %s_combo ORDER BY id" table)))
   4482:  (let* ((sql (format "SELECT concat(%s_list.*) FROM %s_list ORDER BY %s_id" table table table))
   4487:  (let* ((sql (format "SELECT concat(%s.*) FROM %s ORDER BY %s_id" table table table))
   4516:	 (sql (format "UPDATE %s SET %s = regexp_replace(%s, %s, %s, 'g') WHERE %s ~ %s" table column column pattern replacement column pattern)))
   4524:	 (sql (format "UPDATE %s SET %s = regexp_replace(%s, %s, %s, 'g') WHERE %s ~ %s" table column column pattern replacement column pattern)))
   4539:  (let* ((sql (format "SELECT %s FROM %s WHERE %s_id = %s" (string-join columns  ", ") table table id)))
   4589:  (let* ((sql (format "UPDATE %s SET %s = NULL WHERE %s_id = %s" table column table id)))
   4604:	 (sql (format "DELETE FROM %s a USING %s b WHERE a.%s_id > b.%s_id AND a.%s = b.%s %s" table table table table column column and-where)))
   4641:  (let* ((sql (format "SELECT * FROM %s" view))
   4714:	 (sql (format "UPDATE people SET people_tokens = to_tsvector(concat_ws(' ', people_firstname, people_middlenames, people_lastname, people_email1, people_email2, people_email3, get_accounts_name(people_account1), get_accounts_name(people_account2), get_accounts_name(people_account3), people_city1, CASE WHEN people_country1 IS NOT NULL THEN country_name(people_country1) ELSE '' END, coalesce((SELECT string_agg(tags_name,' ') FROM tags, peopletags WHERE peopletags_tags = tags_id AND peopletags_people = people_id),''), CASE WHEN people_country2 IS NOT NULL THEN country_name(people_country2) ELSE '' END, people_description, (select string_agg(sms_body,' ') from sms where sms_contacts = people_id))) %s" where)))
   4725:	   (sql (format "SELECT documents_id, documents_name || ' ' || ts_rank_cd(to_tsvector(documents_name || ' ' || documents_description || ' ' || documents_document),%s,32 /* rank/(rank+1) */) AS rank FROM documents, to_tsquery(%s) query WHERE query @@ to_tsvector(documents_name || ' ' || documents_description || ' ' || documents_document) ORDER BY rank DESC LIMIT 30;" query query)) ;; TODO this cannot order by rank
   4736:  (let ((sql (format "SELECT unnest(%s) FROM %s WHERE %s_id = %s" column table table id)))
   4842:	 (sql (format "INSERT INTO markassignments (markassignments_mark, markassignments_account, markassignments_contact, markassignments_date) VALUES (%s, %s, %s, '%s') RETURNING markassignments_id" mark account contact date)))
   4894:    (let* ((sql (format "SELECT people_id, get_full_contacts_name(people_id) FROM people WHERE people_introducedby = %s" id))
   4902:    (let* ((sql (format "SELECT mailings_id, mailings_subject, date(mailings_datecreated), get_accounts_name(mailings_fromcompany) FROM mailings WHERE mailings_contacts = %s ORDER by mailings_datecreated" id)))
   4922:	 (sql (format "SELECT people_id, get_full_contacts_name(people_id), get_accounts_name(people_account1) FROM people WHERE people_officephone ~ '%s' OR people_mobilephone ~ '%s' OR people_homephone ~ '%s' OR people_otherphone ~ '%s' OR people_fax ~ '%s'" number number number number number))
   4954:  (let* ((sql (format "SELECT accounts_id, accounts_name FROM accounts WHERE accounts_id = %s" id)))
   5002:    (let* ((sql (format "SELECT notes_id, notes_name, notes_note FROM notes WHERE notes_contact = %s" id))
   5010:    (let* ((sql (format "SELECT notes_id, notes_name FROM notes WHERE notes_account = %s" id))
   5022:	   (sql (format "INSERT INTO notes (notes_name, notes_contact, notes_note) VALUES (%s, %s, %s) RETURNING notes_id" name id note))
   5034:	   (sql (format "INSERT INTO notes (notes_name, notes_account, notes_note) VALUES (%s, %s, %s) RETURNING notes_id" name id note))
   5042:    (let* ((sql (format "SELECT sms_id, sms_datecreated::date, smsstatus_name, sms_body FROM sms, smsstatus WHERE sms_contacts = %s AND smsstatus_id = sms_smsstatus" id))
   5071:	(let* ((sql (format "INSERT INTO markassignments (markassignments_mark, markassignments_contact, markassignments_date) VALUES (%s, %s, '%s') RETURNING markassignments_id" mark id  date))
   5078:  (let ((sql (format "SELECT addressbookentries_people FROM addressbookentries WHERE addressbookentries_people = %s AND addressbookentries_addressbooks = %s" person-id addressbook)))
   5084:    (let ((sql (format "INSERT INTO addressbookentries (addressbookentries_addressbooks, addressbookentries_people) VALUES (%s, %s) RETURNING addressbookentries_id" addressbook person-id)))
   5092:      (let ((sql (format "DELETE FROM addressbookentries WHERE addressbookentries_addressbooks = %s AND addressbookentries_people = %s" addressbook person-id)))
   5135:	 (sql (format "SELECT people_id, get_full_contacts_name(people_id) FROM people WHERE (people_mobilephone ~ '\\+%s' OR people_homephone ~ '\\+%s' OR people_homephone ~ '\\+%s' OR people_fax ~ '\\+%s') AND people_country1 IS NULL" prefix prefix prefix prefix)))
   5142:	 (sql (format "SELECT people_id, get_full_contacts_name(people_id), coalesce(get_accounts_name(people_account1),get_accounts_name(people_account2)) FROM people WHERE (people_mobilephone ~ '\\+%s' OR people_homephone ~ '\\+%s' OR people_homephone ~ '\\+%s' OR people_fax ~ '\\+%s') OR people_country1 = %s" prefix prefix prefix prefix country)))
   5158:	 (sql (format "SELECT emails_id, emails_subject, get_accounts_name(emails_mailinglist) FROM emails WHERE emails_subject ~* %s" query)))
   5165:	 (sql (format "SELECT emails_id, emails_subject, coalesce(get_accounts_name(emails_mailinglist),'UNKNOWN') FROM emails WHERE emails_body ~* %s" query)))
   5176:  (let* ((sql (format "SELECT domains_name || tlds_tld FROM domains, tlds WHERE domains_ownercontact = %s AND domains_tlds = tlds_id" id)))
   5187:    (let ((sql (format "SELECT accounts_id, accounts_name FROM accounts WHERE accounts_member1 = %s OR accounts_member2 = %s OR accounts_member3 = %s" id id id)))
   5199:		   (sql (format "SELECT people_id, get_full_contacts_name(people_id) FROM people WHERE people_id IN (%s)" list))
   5262:    (let ((sql (format "SELECT personaltransactions_id, personaltransactions_date,
   5385:  (let ((sql (format "SELECT id, text FROM %s_combo ORDER BY id" table)))
   5426:	 (sql (format "COMMENT ON TABLE %s IS %s" table comment)))
   5628:	 (sql (format "SELECT hyobjects_id FROM hyobjects WHERE %s = %s" column value)))
   5746:	 (sql (format "SELECT people_id, get_full_contacts_name(people_id), coalesce(get_accounts_name(people_account1),get_accounts_name(people_account2),get_accounts_name(people_account3)) FROM people where people_tokens @@ to_tsquery('%s')" query)))
   5753:	     (sql (format "UPDATE people SET people_tokens = to_tsvector(concat_ws(' ', people_firstname, people_middlenames, people_lastname, people_email1, people_email2, people_email3, get_accounts_name(people_account1), get_accounts_name(people_account2), get_accounts_name(people_account3), people_city1, CASE WHEN people_country1 IS NOT NULL THEN country_name(people_country1) ELSE '' END, CASE WHEN people_country2 IS NOT NULL THEN country_name(people_country2) ELSE '' END, people_description, (select string_agg(sms_body,' ') from sms where sms_contacts = people_id))) %s" where)))
   5898:	    (sql (format "UPDATE %s SET %s = NULL WHERE %s_id = %s" table column table new-id)))
   6164:	 (sql (format "SELECT count(1)::text FROM people WHERE people_country1 = %s OR people_country2 = %s" country country)))
   6197:    (let ((sql (format "SELECT pages_id, pages_title, areas_name FROM pages, areas WHERE areas_id = pages_area AND pages_pagetype = %s" id)))
   6319:         (sql (format "SELECT pages_id FROM pages WHERE pages_title !~~ 'EMPTY PAGE' AND pages_area = %s %s %s ORDER BY pages_id %s %s" area cat-sql excluded order limit))
   6530:  (let* ((sql (format "SELECT pages_id, pages_title, 'page', pages_priority AS priority FROM pages WHERE pages_area = %s AND pages_categories IS NULL AND pages_notinmenu IS NOT TRUE UNION (SELECT categories_id, categories_name, 'category', categories_priority AS priority FROM categories WHERE categories_parent IS NULL and categories_area = %s AND categories_notinmenu IS NOT TRUE UNION SELECT pages_id, pages_title, 'page', pages_priority AS priority FROM pages WHERE pages_area = %s AND pages_categories IS NOT NULL AND pages_notinmenu IS NOT TRUE) ORDER BY priority" area area area))
   6571:  (let ((sql (format "DELETE FROM pages WHERE pages_id = %d" id)))
   6630:	   (sql (format "SELECT pages_id || ' ' ||  pages_title || ', ' || areas_name FROM pages, areas WHERE pages_area = areas_id AND (pages_title ~* '%s' OR pages_description ~* '%s')" query query))
   6767:         (sql (format "SELECT tlds_id FROM tlds WHERE tlds_tld = '%s'" tld))
   6775:           (sql (format "UPDATE pages SET pages_filename = '%s' WHERE pages_id = %s" slug page-id)))
   6789:	   (sql (format "SELECT pages_id, pages_title, areas_name FROM pages, areas WHERE areas_id = pages_area %s ORDER BY areas_name" where)))
   6795:    (let ((sql (format "SELECT pages_id, pages_title, coalesce(pages_filename,''), areas_name FROM pages, areas WHERE areas_id = pages_area AND pages_area = %s" id)))
   6810:  (let ((sql (format "SELECT pages_title FROM pages WHERE pages_id = %s" id)))
   6831:	(let ((sql (format "UPDATE pages SET pages_ogimage = '%s' WHERE pages_id = %s AND pages_ogimage !~ '/'" (public-html-rest image-1536) id)))
   6857:  (let ((sql (format "SELECT pages_id FROM pages WHERE pages_area = %s AND pages_ogimage !~ '//'" area)))
   6920:	 (sql (format "SELECT pages_id, pages_title, 'page', pages_priority AS priority, pages_notinmenu FROM pages WHERE pages_area = %s AND pages_categories IS NULL UNION ALL SELECT categories_id, categories_name, 'category', categories_priority, categories_notinmenu AS priority FROM categories WHERE categories_parent IS NULL and categories_area = %s ORDER BY priority DESC" area area))
   6932:	 (sql (format "SELECT pages_id, pages_title, 'page', pages_priority AS priority, pages_notinmenu FROM pages WHERE pages_area = %s AND pages_categories = %s UNION ALL SELECT categories_id, categories_name, 'category', categories_priority, categories_notinmenu AS priority FROM categories WHERE categories_parent = %s AND categories_area = %s ORDER BY priority DESC" area category category area))
   6951:	(let* ((sql (format "INSERT INTO categories (categories_area, categories_parent, categories_slug, categories_name, categories_menuname) VALUES (%s, %s, '%s', %s, %s) RETURNING categories_id" area parent slug (sql-escape-string name) (sql-escape-string menu)))
   6963:	 (sql (format "SELECT pages_id, pages_title || ', ' || pages_priority FROM pages WHERE pages_area = %s AND pages_categories %s" area category))
   7065:    (let ((sql (format "SELECT categories_id, categories_name FROM categories WHERE categories_area = %s" id)))
   7071:	 (sql (format "SELECT variables_id, variables_name FROM variables WHERE variables_area = %s" area)))
   7092:  (let ((sql (format "SELECT targets_id FROM targets WHERE targets_area = %d AND targets_active IS TRUE" area)))
   7186:  (let ((sql (format "SELECT pages_ogimage FROM pages WHERE pages_area = %d ORDER BY pages_id" area)))
   7208:      (let* ((sql (format "SELECT pages_priority FROM pages WHERE pages_categories = %s ORDER BY pages_priority DESC LIMIT 1" id)))
   7238:	     (sql (format "INSERT INTO pages (pages_area, pages_filename, pages_title, pages_description, pages_keywords, pages_priority, pages_categories, pages_content, pages_templates) VALUES (%s, '%s', %s, %s, '%s', %s, %s, %s, %s) RETURNING pages_id" area filename (sql-escape-string title) (sql-escape-string description) keywords new-priority category (sql-escape-string page) template))
   7309:  (let* ((sql (format "SELECT pages_id FROM pages WHERE pages_area = %s AND pages_content ~* %s" area (sql-escape-string query)))
   7351:	 (sql (format "SELECT categories_id, parent_category_name(categories_id) || '::' || categories_name FROM categories %s ORDER BY categories_parent, categories_priority" where)))
   7356:  (let ((sql (format "SELECT pages_id FROM pages WHERE pages_filename = '%s' AND pages_categories = %s" slug id)))
   7455:	 (sql (format "INSERT INTO pages (pages_area, pages_categories, pages_title, pages_description, pages_content, pages_keywords, pages_priority, pages_ogimage, pages_mediaurl, pages_mediatypes, pages_mediasize, pages_mainpage, pages_filename, pages_menuname, pages_active, pages_notinmenu, pages_content2, pages_templates) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) RETURNING pages_id" area category (sql-escape-string title) (sql-escape-string description) (sql-escape-string body) (sql-escape-string keywords) new-priority (sql-escape-string ogimage) (sql-escape-string media) media-type media-size main (sql-escape-string slug) (sql-escape-string menu) active hidden (sql-escape-string body2) template))
   7462:	 (sql (format "SELECT pages_id FROM pages WHERE pages_content ~* %s" region))
   7557:	 (sql (format "INSERT INTO categories (categories_area, categories_parent, categories_slug, categories_name) VALUES (%s, %s, %s, %s) RETURNING categories_id;" area parent slug name))
   7576:         (sql (format "SELECT pages_id FROM pages WHERE pages_area = %s  
   7836:  (let* ((sql (format "SELECT pages_id FROM pages WHERE pages_area = %s AND pages_mediaurl = '%s'" area media))
   7846:  (let ((sql (format "SELECT pages_id FROM pages WHERE pages_mediaurl = '%s'" media)))
   7892:  (let ((sql (format "SELECT mediatypes_name FROM pages, mediatypes WHERE mediatypes_id = pages_mediatypes AND pages_id = %s" page-id)))
   7897:  (let ((sql (format "SELECT pages_mediaurl FROM pages WHERE pages_id = %s" page-id)))
   7996:	 (sql (format "SELECT pages_id FROM pages WHERE pages_area = %s AND pages_categories = %s AND pages_id != %s AND pages_filename = '%s' ORDER BY pages_id" area category checked-page slug))
   8019:  (let* ((sql (format "SELECT categories_id FROM categories WHERE categories_area = %s ORDER BY categories_id" area)))
   8025:	 (sql (format "SELECT pages_id FROM pages WHERE pages_area = %s AND pages_categories = %s %s ORDER BY pages_id" area category exclude-main)))
   8063:  (let ((sql (format "SELECT pages_id FROM pages WHERE pages_area = %s ORDER BY pages_id" area)))
   8116:    (let* ((sql (format "INSERT INTO hyobjects (hyobjects_language, hyobjects_name, hyobjects_link, hyobjects_description, hyobjects_text) SELECT pages_language, pages_title, '', pages_description, pages_content FROM pages WHERE pages_id = %s RETURNING hyobjects_id" id))
   8166:	 (sql (format "SELECT pages_id, pages_title, areas_name FROM pages, areas WHERE areas_id = pages_area AND pages_ogimage ~* %s ORDER BY pages_id" query)))
   8174:	 (sql (format "SELECT pages_id, pages_title, areas_name FROM pages, areas WHERE areas_id = pages_area AND pages_mediaurl ~* %s ORDER BY pages_id" query)))
   8255:         (sql (format "SELECT categories_id, categories_name FROM categories
   8280:         (sql (format "SELECT pages_id FROM pages WHERE pages_notinmenu IS NOT TRUE AND pages_title !~~ 'EMPTY' %s ORDER BY pages_priority" parent))
   8361:      (let ((sql (format "SELECT pages_id, pages_title, (select count(1) FROM relatedpages WHERE relatedpages_pages1 = pages_id OR relatedpages_pages2 = pages_id)::text AS related FROM pages WHERE pages_area = %d ORDER BY related DESC" area)))
   8370:	     (sql (format "SELECT pages_id, pages_title, (select count(1) FROM relatedpages WHERE relatedpages_pages1 = pages_id OR relatedpages_pages2 = pages_id)::text AS related FROM pages WHERE pages_area = %d AND (pages_content ~* %s OR pages_title ~* %s) ORDER BY related DESC" area query query)))
   8384:	 (sql (format "SELECT pages_id, pages_title FROM pages WHERE pages_content ~* %s" query)))
   8388:  (let* ((sql (format "SELECT categories_id, categories_name, count(pages_id)::text FROM categories, pages WHERE pages_categories = categories_id AND categories_area = %s GROUP BY categories_id ORDER BY categories_priority DESC" area)))
   8394:    (let ((sql (format "SELECT relatedpages_pages2, pages_title, areas_name FROM relatedpages, pages,areas WHERE relatedpages_pages1 = %d AND pages_id = relatedpages_pages2 AND pages_area = areas_id UNION SELECT relatedpages_pages1, pages_title, areas_name FROM relatedpages, pages, areas WHERE relatedpages_pages2 = %d AND pages_id = relatedpages_pages1 AND pages_area = areas_id" id id)))
   8444:  (let ((sql (format "SELECT targets_id FROM targets WHERE targets_area = %s AND targets_active IS TRUE" area)))
   8479:	 (sql (format "INSERT INTO personaltransactions (personaltransactions_name, personaltransactions_date, personaltransactions_amount, personaltransactions_currency, personaltransactions_fromperson, personaltransactions_fromaccount, personaltransactions_toperson, personaltransactions_toaccount, personaltransactions_description, personaltransactions_signature) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s) RETURNING personaltransactions_id" name date amount currency from-person from-account to-person to-account description signature)))
   8526:	 (sql (format "SELECT people_id, get_full_contacts_name(people_id), accounts_name FROM people, accounts WHERE people_account1 = accounts_id AND people_country1 = 224 AND accounts_name ~* 'jiji' AND people_mobilephone ~ '25677' AND people_id NOT IN (%s)" sms)))
   8539:	 (sql (format "SELECT people_id, get_full_contacts_name(people_id), accounts_name FROM people, accounts WHERE people_account1 = accounts_id AND 
   8556:	 (sql (format "SELECT people_id, get_full_contacts_name(people_id), accounts_name FROM people, accounts WHERE people_account1 = accounts_id AND 
   8591:	 (sql (format "SELECT people_id, get_full_contacts_name(people_id) FROM people WHERE (substring(people_officephone, 2, 6) IN (%s) OR substring(people_mobilephone, 2, 6) IN (%s) OR substring(people_homephone, 2, 6) IN (%s) OR substring(people_otherphone, 2, 6) IN (%s) OR substring(people_fax, 2, 6) IN (%s)) ORDER BY people_id DESC LIMIT %s" prefixes prefixes prefixes prefixes prefixes how-many)))
   8611:    (let ((sql (format "SELECT locations_id, locations_name, locations_priority::text FROM locations WHERE locations_locationsets = %s ORDER BY locations_priority, locations_id DESC" id)))
   8649:	     (sql (format "INSERT INTO locations (locations_locationsets, locations_geocoordformats, locations_name, locations_description, locations_latitude, locations_longitude, locations_contacts) VALUES (%s, %s, %s, %s, %s, %s, %s) RETURNING locations_id" id geocoordformat name description latitude longitude person))
   8665:	 (sql (format "INSERT INTO peoplegroups (peoplegroups_name, peoplegroups_description) VALUES (%s, %s) RETURNING peoplegroups_id" name description))
   8684:	 (sql (format "SELECT peoplegroupmembers_id, get_full_contacts_name(peoplegroupmembers_person), coalesce(get_accounts_name(people_account1),get_accounts_name(people_account2),get_accounts_name(people_account2),'UNKNOWN') FROM peoplegroupmembers, people WHERE people_id = peoplegroupmembers_person %s" group)))
   8697:	 (sql (format "INSERT INTO peoplegroupmembers (peoplegroupmembers_person, peoplegroupmembers_peoplegroups, peoplegroupmembers_description) VALUES (%s, %s, %s) RETURNING peoplegroupmembers_id" person group description))
   8715:      ;; 	   (sql (format "INSERT INTO contactskills (contactskills_skills, contactskills_contacts) VALUES (107, %s)" person)))
   8805:  (let ((sql (format "SELECT (DATE_PART('day', '%s'::timestamp - '%s'::timestamp) * 24 
   8826:  (let ((sql (format "SELECT '%s'::timestamp + interval '%s'" timestamp interval)))
   8833:	 (sql (format "SELECT CASE WHEN current_timestamp::time < '%s'
   8961:	 (sql (format "INSERT INTO reminders (reminders_name, reminders_remindertypes) 

-- 
Jean

Take action in Free Software Foundation campaigns:
https://www.fsf.org/campaigns

In support of Richard M. Stallman
https://stallmansupport.org/