From lcofield@box-works.com Fri Sep 19 21:02:53 2003 X-Original-To: pgsql-interfaces-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 3226DD1B555 for ; Fri, 19 Sep 2003 21:02:53 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 51494-05 for ; Fri, 19 Sep 2003 18:02:23 -0300 (ADT) Received: from ns.lanterm.net (unknown [208.187.178.11]) by svr1.postgresql.org (Postfix) with ESMTP id B1B89D1B535 for ; Fri, 19 Sep 2003 18:02:05 -0300 (ADT) Received: from Louise ([208.187.178.2]) by ns.lanterm.net (8.11.6/8.11.6) with ESMTP id h8JK1Fs12073 for ; Fri, 19 Sep 2003 14:01:15 -0600 From: "Louise Cofield" To: Subject: How to insert a string with single quotes in the text Date: Fri, 19 Sep 2003 15:02:07 -0600 Message-ID: <030701c37ef1$493739c0$7801a8c0@Louise> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0308_01C37EBE.FE9CC9C0" X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.3416 Importance: Normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.1 tagged_above=0.0 required=5.0 tests=HTML_60_70 X-Spam-Level: X-Archive-Number: 200309/45 X-Sequence-Number: 4463 This is a multi-part message in MIME format. ------=_NextPart_000_0308_01C37EBE.FE9CC9C0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Greetings: I am creating a Libpq program that reads a text file, and inserts values into a Postgres table. An example of my insert statement looks like this: SPRINTF(str, "INSERT INTO itm_table (itm_num, itm_desc) values ('%s', '%s');", sitmnum, sitmdesc); The insert blows up when it hits an item description that contains a single quote in the text. Other than stripping the offending quote-marks from the text prior to processing, does anybody have any ideas how I can deal with this? I appreciate all help! Louise ------=_NextPart_000_0308_01C37EBE.FE9CC9C0 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Greetings:

I am creating a Libpq program that reads a text file, and inserts values into a Postgres table. An example of my insert stateme= nt looks like this:

SPRINTF(str, “INSERT INTO itm_table (itm_num, itm_= desc) values (‘%s’, ‘%s’);”, sitmnum, sitmdesc);

The insert blows up when it hits an item description that contains a single quote in the text.

Other than stripping the offending quote-marks from &nbs= p;the text prior to processing, does anybody have any ideas how I can deal with t= his?

I appreciate all help!

Louise

------=_NextPart_000_0308_01C37EBE.FE9CC9C0-- From olly@lfix.co.uk Sat Sep 20 17:25:07 2003 X-Original-To: pgsql-interfaces-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id D004CD1B967 for ; Sat, 20 Sep 2003 17:25:07 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 39117-09 for ; Sat, 20 Sep 2003 14:24:36 -0300 (ADT) Received: from anchor-post-32.mail.demon.net (anchor-post-32.mail.demon.net [194.217.242.90]) by svr1.postgresql.org (Postfix) with ESMTP id CFCE2D1B58C for ; Sat, 20 Sep 2003 14:23:51 -0300 (ADT) Received: from lfix.demon.co.uk ([80.177.205.209] helo=cerberus.lfix.co.uk) by anchor-post-32.mail.demon.net with esmtp (Exim 3.35 #1) id 1A0lSZ-000K6A-0W; Sat, 20 Sep 2003 18:23:51 +0100 Received: from linda.lfix.co.uk ([192.168.1.1]) by cerberus.lfix.co.uk with esmtp (Exim 4.22) id 1A0lSM-0007zy-G3; Sat, 20 Sep 2003 18:23:38 +0100 Subject: Re: How to insert a string with single quotes in the From: Oliver Elphick To: Louise Cofield Cc: pgsql-interfaces@postgresql.org In-Reply-To: <030701c37ef1$493739c0$7801a8c0@Louise> References: <030701c37ef1$493739c0$7801a8c0@Louise> Content-Type: text/plain Organization: LFIX Limited Message-Id: <1064078648.5362.559.camel@linda.lfix.co.uk> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.4 Date: Sat, 20 Sep 2003 18:24:08 +0100 Content-Transfer-Encoding: 7bit X-LFIX-MailScanner: Found to be clean X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200309/49 X-Sequence-Number: 4467 On Fri, 2003-09-19 at 22:02, Louise Cofield wrote: > I am creating a Libpq program that reads a text file, ... > The insert blows up when it hits an item description that contains a > single quote in the text. You also have to look out for backslashes. > Other than stripping the offending quote-marks from the text prior to > processing, does anybody have any ideas how I can deal with this? The libpq library includes the function PQescapeString, as described in the manual. -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C ======================================== "But my God shall supply all your need according to his riches in glory by Christ Jesus." Philippians 4:19 From bruno@wolff.to Sat Sep 20 17:32:32 2003 X-Original-To: pgsql-interfaces-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 2D935D1B550 for ; Sat, 20 Sep 2003 17:32:32 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 40693-09 for ; Sat, 20 Sep 2003 14:32:00 -0300 (ADT) Received: from wolff.to (wolff.to [66.93.249.74]) by svr1.postgresql.org (Postfix) with SMTP id 836B5D1B54C for ; Sat, 20 Sep 2003 14:31:59 -0300 (ADT) Received: (qmail 18071 invoked by uid 500); 20 Sep 2003 17:38:48 -0000 Date: Sat, 20 Sep 2003 12:38:47 -0500 From: Bruno Wolff III To: Louise Cofield Cc: pgsql-interfaces@postgresql.org Subject: Re: How to insert a string with single quotes in the text Message-ID: <20030920173847.GA17807@wolff.to> Mail-Followup-To: Louise Cofield , pgsql-interfaces@postgresql.org References: <030701c37ef1$493739c0$7801a8c0@Louise> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <030701c37ef1$493739c0$7801a8c0@Louise> User-Agent: Mutt/1.5.4i X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200309/51 X-Sequence-Number: 4469 On Fri, Sep 19, 2003 at 15:02:07 -0600, Louise Cofield wrote: > > I am creating a Libpq program that reads a text file, and inserts values > into a Postgres table. An example of my insert statement looks like > this: > > SPRINTF(str, "INSERT INTO itm_table (itm_num, itm_desc) values ('%s', > '%s');", sitmnum, sitmdesc); > > Other than stripping the offending quote-marks from the text prior to > processing, does anybody have any ideas how I can deal with this? Scan the input and put backslashes in front of backslashes and single quotes.