Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitc16a1bb

Browse files
committed
Add documentation and tests for quote marks in ECPG literal queries.
ECPG's PREPARE ... FROM and EXECUTE IMMEDIATE can optionally takethe target query as a simple literal, rather than the more usualstring-variable reference. This was previously documented asbeing a C string literal, but that's a lie in one critical respect:you can't write a data double quote as \" in such literals. That'sbecause the lexer is in SQL mode at this point, so it'll parsedouble-quoted strings as SQL identifiers, within which backslashis not special, so \" ends the literal.I looked into making this work as documented, but getting the lexerto switch behaviors at just the right point is somewhere betweenvery difficult and impossible. It's not really worth the trouble,because these cases are next to useless: if you have a fixed SQLstatement to execute or prepare, you might as well write it asa direct EXEC SQL, saving the messiness of converting it intoa string literal and gaining the opportunity for compile-timeSQL syntax checking.Instead, let's just document (and test) the workaround of writinga double quote as an octal escape (\042) in such cases.There's no code behavioral change here, so in principle this couldbe back-patched, but it's such a niche case I doubt it's worththe trouble.Per report from 1250kv.Discussion:https://postgr.es/m/673825.1603223178@sss.pgh.pa.us
1 parent3dfb194 commitc16a1bb

File tree

5 files changed

+67
-11
lines changed

5 files changed

+67
-11
lines changed

‎doc/src/sgml/ecpg.sgml

Lines changed: 54 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -7066,14 +7066,38 @@ EXECUTE IMMEDIATE <replaceable class="parameter">string</replaceable>
70667066
<term><replaceable class="parameter">string</replaceable></term>
70677067
<listitem>
70687068
<para>
7069-
A literalCstring or a host variable containing the SQL
7069+
A literal string or a host variable containing the SQL
70707070
statement to be executed.
70717071
</para>
70727072
</listitem>
70737073
</varlistentry>
70747074
</variablelist>
70757075
</refsect1>
70767076

7077+
<refsect1>
7078+
<title>Notes</title>
7079+
7080+
<para>
7081+
In typical usage, the <replaceable>string</replaceable> is a host
7082+
variable reference to a string containing a dynamically-constructed
7083+
SQL statement. The case of a literal string is not very useful;
7084+
you might as well just write the SQL statement directly, without
7085+
the extra typing of <command>EXECUTE IMMEDIATE</command>.
7086+
</para>
7087+
7088+
<para>
7089+
If you do use a literal string, keep in mind that any double quotes
7090+
you might wish to include in the SQL statement must be written as
7091+
octal escapes (<literal>\042</literal>) not the usual C
7092+
idiom <literal>\"</literal>. This is because the string is inside
7093+
an <literal>EXEC SQL</literal> section, so the ECPG lexer parses it
7094+
according to SQL rules not C rules. Any embedded backslashes will
7095+
later be handled according to C rules; but <literal>\"</literal>
7096+
causes an immediate syntax error because it is seen as ending the
7097+
literal.
7098+
</para>
7099+
</refsect1>
7100+
70777101
<refsect1>
70787102
<title>Examples</title>
70797103

@@ -7388,7 +7412,7 @@ EXEC SQL OPEN :curname1;
73887412

73897413
<refsynopsisdiv>
73907414
<synopsis>
7391-
PREPARE <replaceable class="parameter">name</replaceable> FROM <replaceable class="parameter">string</replaceable>
7415+
PREPARE <replaceable class="parameter">prepared_name</replaceable> FROM <replaceable class="parameter">string</replaceable>
73927416
</synopsis>
73937417
</refsynopsisdiv>
73947418

@@ -7421,15 +7445,40 @@ PREPARE <replaceable class="parameter">name</replaceable> FROM <replaceable clas
74217445
<term><replaceable class="parameter">string</replaceable></term>
74227446
<listitem>
74237447
<para>
7424-
A literal C string or a host variable containing a preparable
7425-
statement, one of the SELECT, INSERT, UPDATE, or
7426-
DELETE.
7448+
A literal string or a host variable containing a preparable
7449+
SQL statement, one of SELECT, INSERT, UPDATE, or DELETE.
7450+
Use question marks (<literal>?</literal>) for parameter values
7451+
to be supplied at execution.
74277452
</para>
74287453
</listitem>
74297454
</varlistentry>
74307455
</variablelist>
74317456
</refsect1>
74327457

7458+
<refsect1>
7459+
<title>Notes</title>
7460+
7461+
<para>
7462+
In typical usage, the <replaceable>string</replaceable> is a host
7463+
variable reference to a string containing a dynamically-constructed
7464+
SQL statement. The case of a literal string is not very useful;
7465+
you might as well just write a direct SQL <command>PREPARE</command>
7466+
statement.
7467+
</para>
7468+
7469+
<para>
7470+
If you do use a literal string, keep in mind that any double quotes
7471+
you might wish to include in the SQL statement must be written as
7472+
octal escapes (<literal>\042</literal>) not the usual C
7473+
idiom <literal>\"</literal>. This is because the string is inside
7474+
an <literal>EXEC SQL</literal> section, so the ECPG lexer parses it
7475+
according to SQL rules not C rules. Any embedded backslashes will
7476+
later be handled according to C rules; but <literal>\"</literal>
7477+
causes an immediate syntax error because it is seen as ending the
7478+
literal.
7479+
</para>
7480+
</refsect1>
7481+
74337482
<refsect1>
74347483
<title>Examples</title>
74357484
<programlisting>

‎src/interfaces/ecpg/preproc/pgc.l

Lines changed: 8 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -715,7 +715,14 @@ cppline{space}*#([^i][A-Za-z]*|{if}|{ifdef}|{ifndef}|{import})((\/\*[^*/]*\*+
715715
BEGIN(state_before_str_start);
716716
if (literallen ==0)
717717
mmerror(PARSE_ERROR, ET_ERROR,"zero-length delimited identifier");
718-
/* The backend will truncate the identifier here. We do not as it does not change the result. */
718+
/*
719+
* The server will truncate the identifier here. We do
720+
* not, as (1) it does not change the result; (2) we don't
721+
* know what NAMEDATALEN the server might use; (3) this
722+
* code path is also taken for literal query strings in
723+
* PREPARE and EXECUTE IMMEDIATE, which can certainly be
724+
* longer than NAMEDATALEN.
725+
*/
719726
base_yylval.str =mm_strdup(literalbuf);
720727
return CSTRING;
721728
}

‎src/interfaces/ecpg/test/expected/sql-execute.c

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -77,8 +77,8 @@ if (sqlca.sqlcode < 0) sqlprint();}
7777
#line 26 "execute.pgc"
7878

7979

80-
sprintf(command,"insert intotest(name, amount, letter) values ('db: ''r1''', 1, 'f')");
81-
{ECPGdo(__LINE__,0,1,NULL,0,ECPGst_exec_immediate,command,ECPGt_EOIT,ECPGt_EORT);
80+
/*testhandling of embedded quotes in EXECUTE IMMEDIATE "literal" */
81+
{ECPGdo(__LINE__,0,1,NULL,0,ECPGst_exec_immediate,"insert into test (name, \042amount\042, letter) values ('db: ''r1''', 1, 'f')",ECPGt_EOIT,ECPGt_EORT);
8282
#line 29 "execute.pgc"
8383

8484
if (sqlca.sqlcode<0)sqlprint();}

‎src/interfaces/ecpg/test/expected/sql-execute.stderr

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -10,7 +10,7 @@
1010
[NO_PID]: sqlca: code: 0, state: 00000
1111
[NO_PID]: ECPGtrans on line 26: action "commit"; connection "main"
1212
[NO_PID]: sqlca: code: 0, state: 00000
13-
[NO_PID]: ecpg_execute on line 29: query: insert into test (name, amount, letter) values ('db: ''r1''', 1, 'f'); with 0 parameter(s) on connection main
13+
[NO_PID]: ecpg_execute on line 29: query: insert into test (name,"amount", letter) values ('db: ''r1''', 1, 'f'); with 0 parameter(s) on connection main
1414
[NO_PID]: sqlca: code: 0, state: 00000
1515
[NO_PID]: ecpg_execute on line 29: using PQexec
1616
[NO_PID]: sqlca: code: 0, state: 00000

‎src/interfaces/ecpg/test/sql/execute.pgc

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -25,8 +25,8 @@ exec sql end declare section;
2525
exec sql create table test (name char(8), amount int, letter char(1));
2626
exec sql commit;
2727

28-
sprintf(command, "insert intotest(name, amount, letter) values ('db: ''r1''', 1, 'f')");
29-
exec sql execute immediate:command;
28+
/*testhandling of embedded quotes in EXECUTE IMMEDIATE "literal" */
29+
exec sql execute immediate"insert into test (name, \042amount\042, letter) values ('db: ''r1''', 1, 'f')";
3030

3131
sprintf(command, "insert into test (name, amount, letter) values ('db: ''r1''', 2, 't')");
3232
exec sql execute immediate :command;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp