* Re: [BUG] ob-sql should escape the password [9.7.3 (9.7.3-2f1844 @ /home/andrea/.emacs.d/elpa/org-9.7.3/)]
2024-06-06 22:58 [BUG] ob-sql should escape the password [9.7.3 (9.7.3-2f1844 @ /home/andrea/.emacs.d/elpa/org-9.7.3/)] Andrea
2024-06-07 0:20 ` Phil
@ 2024-06-08 15:21 ` Ihor Radchenko
1 sibling, 0 replies; 3+ messages in thread
From: Ihor Radchenko @ 2024-06-08 15:21 UTC (permalink / raw)
To: Andrea; +Cc: emacs-orgmode
[-- Attachment #1: Type: text/plain, Size: 426 bytes --]
Andrea <andrea-dev@hotmail.com> writes:
> ob-sql.el has a function org-babel-execute:sql. This function extracts
> the password to connect to your database of choice as dbpassword.
> It then uses it like this:
>
> (if dbpassword
> (format "PGPASSWORD=%s " dbpassword)
> "")
>
> If the password contains an & character, the execution of a block fails.
Thanks for reporting!
May you please try the attached patch?
[-- Warning: decoded text below may be mangled, UTF-8 assumed --]
[-- Attachment #2: 0001-ob-sql-Quote-all-the-shell-arguments-originating-fro.patch --]
[-- Type: text/x-patch, Size: 8673 bytes --]
From 0b59737d9e343b495f5567d45ff68e002e0cc8d6 Mon Sep 17 00:00:00 2001
Message-ID: <0b59737d9e343b495f5567d45ff68e002e0cc8d6.1717860058.git.yantar92@posteo.net>
From: Ihor Radchenko <yantar92@posteo.net>
Date: Sat, 8 Jun 2024 17:18:46 +0200
Subject: [PATCH] ob-sql: Quote all the shell arguments originating from Org
buffer
* lisp/ob-sql.el (org-babel-sql-dbstring-mysql):
(org-babel-sql-dbstring-postgresql):
(org-babel-sql-dbstring-oracle):
(org-babel-sql-dbstring-mssql):
(org-babel-sql-dbstring-sqsh):
(org-babel-sql-dbstring-vertica):
(org-babel-sql-dbstring-saphana):
(org-babel-execute:sql): Quote all the shell arguments to avoid
unexpect shell expansion. Do not quote port as it is a number; make
sure that port is really demanded a number in the format strings.
Reported-by: Andrea <andrea-dev@hotmail.com>
Link: https://orgmode.org/list/DU2P193MB24225F623DBF8B3D254D3C0E88FA2@DU2P193MB2422.EURP193.PROD.OUTLOOK.COM
---
lisp/ob-sql.el | 90 ++++++++++++++++++++++++++++----------------------
1 file changed, 51 insertions(+), 39 deletions(-)
diff --git a/lisp/ob-sql.el b/lisp/ob-sql.el
index dc067a417..e51eed1bc 100644
--- a/lisp/ob-sql.el
+++ b/lisp/ob-sql.el
@@ -117,23 +117,27 @@ (defun org-babel-edit-prep:sql (info)
(defun org-babel-sql-dbstring-mysql (host port user password database)
"Make MySQL cmd line args for database connection. Pass nil to omit that arg."
- (combine-and-quote-strings
+ (mapconcat
+ #'identity
(delq nil
- (list (when host (concat "-h" host))
+ (list (when host (concat "-h" (shell-quote-argument host)))
(when port (format "-P%d" port))
- (when user (concat "-u" user))
- (when password (concat "-p" password))
- (when database (concat "-D" database))))))
+ (when user (concat "-u" (shell-quote-argument user)))
+ (when password (concat "-p" (shell-quote-argument password)))
+ (when database (concat "-D" (shell-quote-argument database)))))
+ " "))
(defun org-babel-sql-dbstring-postgresql (host port user database)
"Make PostgreSQL command line args for database connection.
Pass nil to omit that arg."
- (combine-and-quote-strings
+ (mapconcat
+ #'identity
(delq nil
- (list (when host (concat "-h" host))
+ (list (when host (concat "-h" (shell-quote-argument host)))
(when port (format "-p%d" port))
- (when user (concat "-U" user))
- (when database (concat "-d" database))))))
+ (when user (concat "-U" (shell-quote-argument user)))
+ (when database (concat "-d" (shell-quote-argument database)))))
+ " "))
(defun org-babel-sql-dbstring-oracle (host port user password database)
"Make Oracle command line arguments for database connection.
@@ -149,8 +153,12 @@ (defun org-babel-sql-dbstring-oracle (host port user password database)
<user>/<password>@<database>
using its alias."
+ (when user (setq user (shell-quote-argument user)))
+ (when password (setq password (shell-quote-argument password)))
+ (when database (setq database (shell-quote-argument database)))
+ (when host (setq host (shell-quote-argument host)))
(cond ((and user password database host port)
- (format "%s/%s@%s:%s/%s" user password host port database))
+ (format "%s/%s@%s:%d/%s" user password host port database))
((and user password database)
(format "%s/%s@%s" user password database))
(t (user-error "Missing information to connect to database"))))
@@ -161,10 +169,10 @@ (defun org-babel-sql-dbstring-mssql (host user password database)
SQL Server on Windows and Linux platform."
(mapconcat #'identity
(delq nil
- (list (when host (format "-S \"%s\"" host))
- (when user (format "-U \"%s\"" user))
- (when password (format "-P \"%s\"" password))
- (when database (format "-d \"%s\"" database))))
+ (list (when host (format "-S \"%s\"" (shell-quote-argument host)))
+ (when user (format "-U \"%s\"" (shell-quote-argument user)))
+ (when password (format "-P \"%s\"" (shell-quote-argument password)))
+ (when database (format "-d \"%s\"" (shell-quote-argument database)))))
" "))
(defun org-babel-sql-dbstring-sqsh (host user password database)
@@ -172,10 +180,10 @@ (defun org-babel-sql-dbstring-sqsh (host user password database)
\"sqsh\" is one method to access Sybase or MS SQL via Linux platform"
(mapconcat #'identity
(delq nil
- (list (when host (format "-S \"%s\"" host))
- (when user (format "-U \"%s\"" user))
- (when password (format "-P \"%s\"" password))
- (when database (format "-D \"%s\"" database))))
+ (list (when host (format "-S \"%s\"" (shell-quote-argument host)))
+ (when user (format "-U \"%s\"" (shell-quote-argument user)))
+ (when password (format "-P \"%s\"" (shell-quote-argument password)))
+ (when database (format "-D \"%s\"" (shell-quote-argument database)))))
" "))
(defun org-babel-sql-dbstring-vertica (host port user password database)
@@ -183,11 +191,11 @@ (defun org-babel-sql-dbstring-vertica (host port user password database)
Pass nil to omit that arg."
(mapconcat #'identity
(delq nil
- (list (when host (format "-h %s" host))
+ (list (when host (format "-h %s" (shell-quote-argument host)))
(when port (format "-p %d" port))
- (when user (format "-U %s" user))
+ (when user (format "-U %s" (shell-quote-argument user)))
(when password (format "-w %s" (shell-quote-argument password) ))
- (when database (format "-d %s" database))))
+ (when database (format "-d %s" (shell-quote-argument database)))))
" "))
(defun org-babel-sql-dbstring-saphana (host port instance user password database)
@@ -195,13 +203,15 @@ (defun org-babel-sql-dbstring-saphana (host port instance user password database
Pass nil to omit that arg."
(mapconcat #'identity
(delq nil
- (list (and host port (format "-n %s:%s" host port))
- (and host (not port) (format "-n %s" host))
+ (list (and host port (format "-n %s:%s"
+ (shell-quote-argument host)
+ port))
+ (and host (not port) (format "-n %s" (shell-quote-argument host)))
(and instance (format "-i %d" instance))
- (and user (format "-u %s" user))
+ (and user (format "-u %s" (shell-quote-argument user)))
(and password (format "-p %s"
(shell-quote-argument password)))
- (and database (format "-d %s" database))))
+ (and database (format "-d %s" (shell-quote-argument database)))))
" "))
(defun org-babel-sql-convert-standard-filename (file)
@@ -276,21 +286,23 @@ (defun org-babel-execute:sql (body params)
(or cmdline "")
(org-babel-process-file-name in-file)
(org-babel-process-file-name out-file)))
- ((postgresql postgres) (format
- "%s%s --set=\"ON_ERROR_STOP=1\" %s -A -P \
+ ((postgresql postgres)
+ (format
+ "%s%s --set=\"ON_ERROR_STOP=1\" %s -A -P \
footer=off -F \"\t\" %s -f %s -o %s %s"
- (if dbpassword
- (format "PGPASSWORD=%s " dbpassword)
- "")
- (or (bound-and-true-p
- sql-postgres-program)
- "psql")
- (if colnames-p "" "-t")
- (org-babel-sql-dbstring-postgresql
- dbhost dbport dbuser database)
- (org-babel-process-file-name in-file)
- (org-babel-process-file-name out-file)
- (or cmdline "")))
+ (if dbpassword
+ (format "PGPASSWORD=%s "
+ (shell-quote-argument dbpassword))
+ "")
+ (or (bound-and-true-p
+ sql-postgres-program)
+ "psql")
+ (if colnames-p "" "-t")
+ (org-babel-sql-dbstring-postgresql
+ dbhost dbport dbuser database)
+ (org-babel-process-file-name in-file)
+ (org-babel-process-file-name out-file)
+ (or cmdline "")))
(sqsh (format "sqsh %s %s -i %s -o %s -m csv"
(or cmdline "")
(org-babel-sql-dbstring-sqsh
--
2.45.1
[-- Attachment #3: Type: text/plain, Size: 224 bytes --]
--
Ihor Radchenko // yantar92,
Org mode contributor,
Learn more about Org mode at <https://orgmode.org/>.
Support Org development at <https://liberapay.com/org-mode>,
or support my work at <https://liberapay.com/yantar92>
^ permalink raw reply related [flat|nested] 3+ messages in thread