bug#37459: 26.2; sql syntax highlight problem when escaping single quote

bug#37459: 26.2; sql syntax highlight problem when escaping single quote

[ndame]

[-- Attachment #1: Type: text/plain, Size: 188 bytes --]

If you try this simple sql

  insert into test (test) values ('test\'test')

then the string is not highlighted properly:

https://i.imgur.com/DE9TJUP.png

I guess it's a bug.
 

[-- Attachment #2: Type: text/html, Size: 257 bytes --]

bug#37459: 26.2; sql syntax highlight problem when escaping single quote

[ndame]

[-- Attachment #1: Type: text/plain, Size: 1760 bytes --]

I poked around for the problem and found this in sql-mode Help:

  Note that SQL doesn't have an escape character unless you specify
  one.  If you specify backslash as escape character in SQL, you
  must tell Emacs.  Here's how to do that in your init file:

  (add-hook 'sql-mode-hook
            (lambda ()
              (modify-syntax-entry ?\\ "." sql-mode-syntax-table)))


And sure enough, the syntax of \ was punctuation.

However, adding the above add hook did nothing. The string
highlighting was still wrong.

It's because the suggestion is wrong, because it sets slash to
punctuation again.

Somebody who has access to the repo could simply fix that by changing
the syntax string in the example:

  (modify-syntax-entry ?\\ "\\" sql-mode-syntax-table)


So it worked, but then I realized there are different sql modes and
the default is ANSI, so I changed it to Mysql to test it, but that
didn't help either.

Then I checked the mysql syntax setting and backslash was missing
there too, though backslash is an escape character for mysql by
default:

  https://dev.mysql.com/doc/refman/8.0/en/string-literals.html

So I changed the syntax for mysql by adding backslash:

           :syntax-alist ((?# . "< b") (?\\ . "\\"))

and this fixed it.


There are 3 takeaways:

1. The incorrect example in sql-mode Help should be fixed.

2. Since backslash is an escape character in mysql it may be set as
   such by default if the user chooses mysql mode.

3. The user may not realize he is in an incorrect sql mode. Maybe when
   the user activates sql mode for the first time emacs should tell him
   that ansi is the default and ask him if he wants to change it?
   

[-- Attachment #2: Type: text/html, Size: 2216 bytes --]

bug#37459: Acknowledgement (26.2; sql syntax highlight problem when escaping single quote)

[ndame]

[-- Attachment #1: Type: text/plain, Size: 1176 bytes --]

Browsing sql.el I found this code:
 
;; MariaDB is a drop-in replacement for MySQL, so just make the
;; MariaDB variables aliases of the MySQL ones.
 
(defvaralias 'sql-mariadb-program 'sql-mysql-program)
(defvaralias 'sql-mariadb-options 'sql-mysql-options)
(defvaralias 'sql-mariadb-login-params 'sql-mysql-login-params)
 
 

If mariadb is a drop in mysql replacement then all mysql-related fixes
should apply to it too, though later in the code mariadb sets up syntax
independetly, so the same bug affects it which the patch here fixes.
The correct solution could be setting up a mariadb syntax variable
and alias it to the mysql syntax variable to avoid duplicating the same code:

 
(mariadb

:name "MariaDB"

:free-software t

:font-lock sql-mode-mariadb-font-lock-keywords

:sqli-program sql-mariadb-program

:sqli-options sql-mariadb-options

:sqli-login sql-mariadb-login-params

:sqli-comint-func sql-comint-mariadb

:list-all "SHOW TABLES;"

:list-table "DESCRIBE %s;"

:prompt-regexp "^MariaDB \\[.*]> "

:prompt-cont-regexp "^ [\"'`-]> "

:syntax-alist ((?# . "< b"))
:input-filter sql-remove-tabs-filter)



 

[-- Attachment #2: Type: text/html, Size: 2762 bytes --]

bug#37459: Corrects sql-mode help about escape character syntax

[Kristian Hole]

[-- Attachment #1.1: Type: text/plain, Size: 1 bytes --]



[-- Attachment #1.2: Type: text/html, Size: 22 bytes --]

[-- Attachment #2: 0001-Corrects-sql-mode-help-about-escape-character-syntax.patch --]
[-- Type: application/octet-stream, Size: 846 bytes --]

From 49a0af005d770cedc54a954a811c903b8dfe9a93 Mon Sep 17 00:00:00 2001
From: kahole <kristian@hole.priv.no>
Date: Sat, 26 Oct 2019 17:07:27 +0200
Subject: [PATCH 1/2] Corrects sql-mode help about escape character syntax

Changes the example from the incorrect use of
punctuation rule, to the escape character rule (Bug#37459).
---
 lisp/progmodes/sql.el | 2 +-
 1 file changed, 1 insertion(+), 1 deletion(-)

diff --git a/lisp/progmodes/sql.el b/lisp/progmodes/sql.el
index b17364b08f..f985157202 100644
--- a/lisp/progmodes/sql.el
+++ b/lisp/progmodes/sql.el
@@ -4244,7 +4244,7 @@ sql-mode
 
 \(add-hook \\='sql-mode-hook
           (lambda ()
-	    (modify-syntax-entry ?\\\\ \".\" sql-mode-syntax-table)))"
+	    (modify-syntax-entry ?\\\\ \"\\\\\" sql-mode-syntax-table)))"
   :group 'SQL
   :abbrev-table sql-mode-abbrev-table
 
-- 
2.18.0

bug#37459: Adds backslash as escape character to mysql syntax-alist

[Kristian Hole]

[-- Attachment #1.1: Type: text/plain, Size: 473 bytes --]

I've generated patches for takeaways (1) and (2)

Patch attached to previous message:
  Corrects sql-mode help about escape character syntax

  Changes the example from the incorrect use of
  punctuation rule, to the escape character rule (Bug#37459).

This patch:
  Adds backslash as escape character to mysql syntax-alist

  In MySQL syntax backslash denotes an escape sequence.
  This change adds backslash to the syntax-alist of
  MySQL in sql-mode as per (Bug#37459).

[-- Attachment #1.2: Type: text/html, Size: 602 bytes --]

[-- Attachment #2: 0002-Adds-backslash-as-escape-character-to-mysql-syntax-a.patch --]
[-- Type: application/octet-stream, Size: 884 bytes --]

From 4953d7bb249502fd8d87295b81bd381e70134fc0 Mon Sep 17 00:00:00 2001
From: kahole <kristian@hole.priv.no>
Date: Sat, 26 Oct 2019 17:08:17 +0200
Subject: [PATCH 2/2] Adds backslash as escape character to mysql syntax-alist

In MySQL syntax backslash denotes an escape sequence.
This change adds backslash to the syntax-alist of
MySQL in sql-mode as per (Bug#37459).
---
 lisp/progmodes/sql.el | 2 +-
 1 file changed, 1 insertion(+), 1 deletion(-)

diff --git a/lisp/progmodes/sql.el b/lisp/progmodes/sql.el
index f985157202..81e0d8cf89 100644
--- a/lisp/progmodes/sql.el
+++ b/lisp/progmodes/sql.el
@@ -461,7 +461,7 @@ sql-product-alist
      :prompt-regexp "^mysql> "
      :prompt-length 6
      :prompt-cont-regexp "^    -> "
-     :syntax-alist ((?# . "< b"))
+     :syntax-alist ((?# . "< b") (?\\ . "\\"))
      :input-filter sql-remove-tabs-filter)
 
     (oracle
-- 
2.18.0

bug#37459: Adds backslash as escape character to mysql syntax-alist

[Stefan Kangas]

Hi Michael,

Kristian Hole <kristian@hole.priv.no> writes:

> I've generated patches for takeaways (1) and (2) 
>
> Patch attached to previous message:
>   Corrects sql-mode help about escape character syntax
>
>   Changes the example from the incorrect use of
>   punctuation rule, to the escape character rule (Bug#37459).
>
> This patch:
>   Adds backslash as escape character to mysql syntax-alist
>
>   In MySQL syntax backslash denotes an escape sequence.
>   This change adds backslash to the syntax-alist of
>   MySQL in sql-mode as per (Bug#37459).

Could you please take a look at the below patch?  TIA.

Best regards,
Stefan Kangas

>
> From 4953d7bb249502fd8d87295b81bd381e70134fc0 Mon Sep 17 00:00:00 2001
> From: kahole <kristian@hole.priv.no>
> Date: Sat, 26 Oct 2019 17:08:17 +0200
> Subject: [PATCH 2/2] Adds backslash as escape character to mysql syntax-alist
>
> In MySQL syntax backslash denotes an escape sequence.
> This change adds backslash to the syntax-alist of
> MySQL in sql-mode as per (Bug#37459).
> ---
>  lisp/progmodes/sql.el | 2 +-
>  1 file changed, 1 insertion(+), 1 deletion(-)
>
> diff --git a/lisp/progmodes/sql.el b/lisp/progmodes/sql.el
> index f985157202..81e0d8cf89 100644
> --- a/lisp/progmodes/sql.el
> +++ b/lisp/progmodes/sql.el
> @@ -461,7 +461,7 @@ sql-product-alist
>       :prompt-regexp "^mysql> "
>       :prompt-length 6
>       :prompt-cont-regexp "^    -> "
> -     :syntax-alist ((?# . "< b"))
> +     :syntax-alist ((?# . "< b") (?\\ . "\\"))
>       :input-filter sql-remove-tabs-filter)
>  
>      (oracle

bug#37459: Adds backslash as escape character to mysql syntax-alist

[Lars Ingebrigtsen]

Kristian Hole <kristian@hole.priv.no> writes:

> I've generated patches for takeaways (1) and (2) 
>
> Patch attached to previous message:
>   Corrects sql-mode help about escape character syntax
>
>   Changes the example from the incorrect use of
>   punctuation rule, to the escape character rule (Bug#37459).
>
> This patch:
>   Adds backslash as escape character to mysql syntax-alist
>
>   In MySQL syntax backslash denotes an escape sequence.
>   This change adds backslash to the syntax-alist of
>   MySQL in sql-mode as per (Bug#37459).

[...]

> -     :syntax-alist ((?# . "< b"))
> +     :syntax-alist ((?# . "< b") (?\\ . "\\"))
>       :input-filter sql-remove-tabs-filter)

I don't use mysql in sql mode, but this looks "obviously" correct, so
I've applied it to Emacs 28 (along with the doc fix).  Perhaps Michael
can chime in...

-- 
(domestic pets only, the antidote for overdose, milk.)
   bloggy blog: http://lars.ingebrigtsen.no

bug#37459: Adds backslash as escape character to mysql syntax-alist

[Lars Ingebrigtsen]

Lars Ingebrigtsen <larsi@gnus.org> writes:

> I don't use mysql in sql mode, but this looks "obviously" correct, so
> I've applied it to Emacs 28 (along with the doc fix).  Perhaps Michael
> can chime in...

Or not?  Somebody has manually blacklisted my MTA, which only sends out
emails from me?

Well I have never!

  michael@mauger.com
    host mx.netidentity.com.cust.hostedemail.com [216.40.42.4]
    SMTP error from remote mail server after initial connection:
    554 5.7.1 Service unavailable; Client host [95.216.78.240] blocked using urbl.hostedemail.com; Your IP has been manually blacklisted


-- 
(domestic pets only, the antidote for overdose, milk.)
   bloggy blog: http://lars.ingebrigtsen.no