all messages for Emacs-related lists mirrored at yhetil.org
 help / color / mirror / code / Atom feed
* [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 Andrea
  2024-06-07  0:20 ` Phil
  2024-06-08 15:21 ` Ihor Radchenko
  0 siblings, 2 replies; 3+ messages in thread
From: Andrea @ 2024-06-06 22:58 UTC (permalink / raw)
  To: emacs-orgmode

Hello there, thanks for Org Mode!

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.
I solved like this:
    (if dbpassword
        (format "PGPASSWORD='%s' " dbpassword)
      "")

Hope this is of help,

Andrea


Emacs  : GNU Emacs 29.3 (build 1, x86_64-pc-linux-gnu, GTK+ Version 3.24.33, cairo version 1.16.0)
 of 2024-03-26, modified by Debian
Package: Org mode version 9.7.3 (9.7.3-2f1844 @ /home/andrea/.emacs.d/elpa/org-9.7.3/)


^ permalink raw reply	[flat|nested] 3+ messages in thread

* 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: Phil @ 2024-06-07  0:20 UTC (permalink / raw)
  To: Andrea, emacs-orgmode

* [2024-06-07 00:58] Andrea:
> org-babel-execute:sql. This function extracts
> the password to connect to your database of choice as dbpassword.
> It then uses it like this:

> 	(format "PGPASSWORD=%s " dbpassword)

> If the password contains an & character, the execution of a block fails.
> I solved like this:
>          (format "PGPASSWORD='%s' " dbpassword)

shell-quote-argument is designed for this case.

Phil


^ permalink raw reply	[flat|nested] 3+ messages in thread

* 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

end of thread, other threads:[~2024-06-08 15:21 UTC | newest]

Thread overview: 3+ messages (download: mbox.gz follow: Atom feed
-- links below jump to the message on this page --
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

Code repositories for project(s) associated with this external index

	https://git.savannah.gnu.org/cgit/emacs.git
	https://git.savannah.gnu.org/cgit/emacs/org-mode.git

This is an external index of several public inboxes,
see mirroring instructions on how to clone and mirror
all data and code used by this external index.