postgres bug - Mailing list pgsql-bugs

============================================================================
                        POSTGRESQL BUG REPORT TEMPLATE
============================================================================


Your name        :        NF Stevens
Your email address    :    norman@arcady.u-net.com


System Configuration
---------------------
  Architecture (example: Intel Pentium)      :

    processor    : 0
    vendor_id    : CyrixInstead
    cpu family    : 5
    model        : 4
    model name    : 6x86L 2x Core/Bus Clock
    stepping    : 2
    fdiv_bug    : no
    hlt_bug        : no
    sep_bug        : no
    f00f_bug    : no
    coma_bug    : yes
    fpu        : yes
    fpu_exception    : yes
    cpuid level    : 1
    wp        : yes
    flags        : fpu de cx8 mtrr
    bogomips    : 149.50

  Operating System (example: Linux 2.0.26 ELF)     :

    Linux arcady 2.2.10 #2 Sat Jul 17 15:52:35 BST 1999 i586 unknown

  PostgreSQL version (example: PostgreSQL-6.5.1):   PostgreSQL-6.5.1

  Compiler used (example:  gcc 2.8.0)        :

    gcc version egcs-2.91.60 19981201 (egcs-1.1.1 release)

  Libc version : glibc-2.1.1

Please enter a FULL description of your problem:
------------------------------------------------

Inserts into a varchar field where the value contains a quote character
(') fail when the statement has been dynamically prepared.


Please describe a way to repeat the problem.   Please try to provide a
concise reproducible example, if at all possible:
----------------------------------------------------------------------

#include <stdio.h>
#include <stdlib.h>
#include <string.h>

EXEC SQL INCLUDE sqlca;

int main (void)
{

    EXEC SQL BEGIN DECLARE SECTION;
    char Query [128] = "INSERT into e (name,size) values (:name,:size)";
    char name [1024];
    int size = 64;
    EXEC SQL END DECLARE SECTION;

    EXEC SQL CONNECT norman;

    /* Don't complain about table existing the second time it's run */
    EXEC SQL WHENEVER SQLERROR CONTINUE;
    EXEC SQL CREATE TABLE e (name varchar (32), size integer);
    EXEC SQL COMMIT;
    sqlca.sqlcode = 0;

    EXEC SQL WHENEVER SQLERROR Goto Error;
    EXEC SQL PREPARE Stmt FROM :Query;
    printf ("Prepared\n");

    strcpy (name, "abcdef");
    EXEC SQL EXECUTE Stmt USING :name,:size;
    printf ("Executed 1\n");

    strcpy (name, "abc'ef");
    EXEC SQL EXECUTE Stmt USING :name,:size;
    printf ("Executed 2\n");

    EXEC SQL COMMIT;

    printf ("Committed\n");
    return 0;
Error:
    printf ("Oops \"%s\"\n", name);
    printf ("%*.*s\n", sqlca.sqlerrm.sqlerrml,
         sqlca.sqlerrm.sqlerrml, sqlca.sqlerrm.sqlerrmc);
    return 2;
}

The output of this program is

Prepared
Executed 1
Oops "abc'ef"
Too many arguments line 34.

If you know how this problem might be fixed, list the solution below:
---------------------------------------------------------------------

Unknown at this time.

#include <stdio.h>
#include <stdlib.h>
#include <string.h>

EXEC SQL INCLUDE sqlca;

int main (void)
{

    EXEC SQL BEGIN DECLARE SECTION;
    char Query [128] = "INSERT into e (name,size) values (:name,:size)";
    char name [1024];
    int size = 64;
    EXEC SQL END DECLARE SECTION;

    EXEC SQL CONNECT norman;

    /* Don't complain about table existing the second time it's run */
    EXEC SQL WHENEVER SQLERROR CONTINUE;
    EXEC SQL CREATE TABLE e (name varchar (32), size integer);
    EXEC SQL COMMIT;
    sqlca.sqlcode = 0;

    EXEC SQL WHENEVER SQLERROR Goto Error;
    EXEC SQL PREPARE Stmt FROM :Query;
    printf ("Prepared\n");

    strcpy (name, "abcdef");
    EXEC SQL EXECUTE Stmt USING :name,:size;
    printf ("Executed 1\n");

    strcpy (name, "abc'ef");
    EXEC SQL EXECUTE Stmt USING :name,:size;
    printf ("Executed 2\n");

    EXEC SQL COMMIT;

    printf ("Committed\n");
    return 0;
Error:
    printf ("Oops \"%s\"\n", name);
    printf ("%*.*s\n", sqlca.sqlerrm.sqlerrml,
             sqlca.sqlerrm.sqlerrml, sqlca.sqlerrm.sqlerrmc);
    return 2;
}