`#` in SQL string gets truncated

[Altreus]

Try deploying this:

INSERT INTO test_strings (content_text)
VALUES
    (
        'This text is fine',
        'This gets #truncated'
    )
;

I get this error:

LINE 1: ...ings (content_text) VALUES ( 'This text is fine', 'This gets...
                                                             ^ at (eval 816) line 17
 (running line 'INSERT INTO test_strings (content_text) VALUES ( 'This text is fine', 'This gets )') at /opt/perl-5.28.1/lib/site_perl/5.28.1/DBIx/Class/DeploymentHandler/DeployMethod/SQL/Translator.pm line 172.

with

0.002230

[Altreus]

This seems relevant and very naïve

https://github.com/frioux/DBIx-Class-DeploymentHandler/blob/master/lib/DBIx/Class/DeploymentHandler/DeployMethod/SQL/Translator.pm#L280

[Altreus]

Theoretically this means I can't put -- in an SQL string either, and indeed I get the exact same behaviour when I do this.

Unless comment stripping is way smarter, I don't think it's remotely worth doing. The DB engine should be able to cope with them.

[mohawk2]

The reason to have the comment-stripping is a similar one to the one you're having: it needs to split statements on (usually) ;, which might occur in comments, since it can only rely on being able to execute a single SQL statement at a time. You're right it clearly needs to be smarter.

Which DB was this for?

[dmck2]

More generally ';', '#', '--' inside quoted strings in SQL are treated wrongly. They are treated as statement delimiters instead of part of the string. This is just plain WRONG.

[davewood]

im storing color codes as string in my DB.

CREATE TABLE "tag" (
  "id" serial NOT NULL,
  "name" character varying NOT NULL,
  "color" character varying DEFAULT '#ffffff' NOT NULL,
  PRIMARY KEY ("id"),
  CONSTRAINT "tag_name" UNIQUE ("name")
);

epplication-app | failed to run SQL in /home/epplication/EPPlication/script/../lib/EPPlication/Util/../../../db_upgrades/PostgreSQL/deploy/61/001-auto.sql: DBIx::Class::DeploymentHandler::DeployMethod::SQL::Translator::try {...} (): DBI Exception: DBD::Pg::db do failed: ERROR:  unterminated quoted string at or near "' PRIMARY KEY ("id"), CONSTRAINT "tag_name" UNIQUE ("name") )"
epplication-app | LINE 1: ...rying NOT NULL, "color" character varying DEFAULT ' PRIMARY ...
epplication-app |                                                              ^ at inline delegation in DBIx::Class::DeploymentHandler for deploy_method->deploy (attribute declared in /home/epplication/EPPlication/local/lib/perl5/DBIx/Class/DeploymentHandler/WithApplicatorDumple.pm at line 51) line 18

epplication-db | ERROR:  unterminated quoted string at or near "' PRIMARY KEY ("id"), CONSTRAINT "tag_name" UNIQUE ("name") )" at character 113
epplication-db | STATEMENT:  CREATE TABLE "tag" ( "id" serial NOT NULL, "name" character varying NOT NULL, "color" character varying DEFAULT ' PRIMARY KEY ("id"), CONSTRAINT "tag_name" UNIQUE ("name") )

[augensalat]

Fixed in v0.002233.