[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
Re: Emacs Modular Configuration: the preferable way.
From: |
Jean Louis |
Subject: |
Re: Emacs Modular Configuration: the preferable way. |
Date: |
Mon, 21 Jun 2021 23:36:25 +0300 |
User-agent: |
Mutt/2.0.7+183 (3d24855) (2021-05-28) |
* 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/
- Re: Emacs Modular Configuration: the preferable way., (continued)
- Re: Emacs Modular Configuration: the preferable way., Jean Louis, 2021/06/21
- Re: Emacs Modular Configuration: the preferable way., Emanuel Berg, 2021/06/21
- Re: Printf and quoting in general, SQL injection in particular, Jean Louis, 2021/06/21
- Re: Printf and quoting in general, SQL injection in particular, Emanuel Berg, 2021/06/26
- Re: Printf and quoting in general, SQL injection in particular, Yuri Khan, 2021/06/26
- Re: Printf and quoting in general, SQL injection in particular, Emanuel Berg, 2021/06/26
- Re: Printf and quoting in general, SQL injection in particular, tomas, 2021/06/26
- Re: Printf and quoting in general, SQL injection in particular, Jean Louis, 2021/06/28
- Re: Emacs Modular Configuration: the preferable way.,
Jean Louis <=
- Printf and quoting in general, SQL injection in particular [was: Emacs Modular Configuration: the preferable way], tomas, 2021/06/21
- Re: Printf and quoting in general, SQL injection in particular [was: Emacs Modular Configuration: the preferable way], Jean Louis, 2021/06/21
- Re: Printf and quoting in general, SQL injection in particular [was: Emacs Modular Configuration: the preferable way], Emanuel Berg, 2021/06/21
- Re: Printf and quoting in general, SQL injection in particular [was: Emacs Modular Configuration: the preferable way], Jean Louis, 2021/06/21
- Re: Printf and quoting in general, SQL injection in particular [was: Emacs Modular Configuration: the preferable way], Emanuel Berg, 2021/06/26
- Re: Printf and quoting in general, SQL injection in particular [was: Emacs Modular Configuration: the preferable way], Jean Louis, 2021/06/28
- Re: Printf and quoting in general, SQL injection in particular [was: Emacs Modular Configuration: the preferable way], Emanuel Berg, 2021/06/21
- Re: Printf and quoting in general, SQL injection in particular [was: Emacs Modular Configuration: the preferable way], Eli Zaretskii, 2021/06/22
- Re: Printf and quoting in general, SQL injection in particular [was: Emacs Modular Configuration: the preferable way], Jean Louis, 2021/06/22
- Re: Printf and quoting in general, SQL injection in particular [was: Emacs Modular Configuration: the preferable way], Eli Zaretskii, 2021/06/22