anybody ever tried to use backward slash???

[thedumbkid]

anybody ever tried to do:
INSERT INTO mytable (column1) VALUES ('\');

that doesn't seem to work but
INSERT INTO mytable (column1) VALUES ('\\'); works...

why is that???

 

[Leland123]

Hi thedumbkid,

Postgresql and certain other languages use special characters and key words as part of the programming language. To have postgresql see one of its special characters literally, the character must be "escaped". A character is [escaped] by placing a backword slash in front of the special character. The following code will result in an error:

select * from thetable where company='Peg's Antiques';

The error results because postgres will become confused with the apostrophe in "Peg's". However, the following should work:

select * from thetable where company='Peg\'s Antiques';

Lelandj


Leland F. Jackson, CPA
Software - Master (TM)

http://www.smvfp.com

Nothing Runs Like the Fox

 

[thedumbkid]

yea...

or SELECT * FROM thetable WHERE company='Peg''s Antiques'; seems to work too

but I have one more question...

placing double \\ works for INSERT and UPDATE... when I view the data, there's one \ in it
what about when doing searches???

like I wanna do: SELECT * FROM thetable WHERE company LIKE '%\\%';
to search for anything with \ in it???

however that returns no result no matter what
I read a bit on PostgreSQL documentation and found that
SELECT * FROM thetable WHERE company LIKE '%\\%' ESCAPE ''; works by adding the optional [ESCAPE '']

is that the only way to solve my problem?
or is there a way to make that the default behaviour somehow???

 

[Leland123]

Hi thedumbkid,

So far as I know, there is no way to have postgresql destinquest regular input from programming special characters, so you would need to take responsiblity for the behavior through your application.

I often use perl/apache/postgres in developing web based application. When a guest visits my site and completes a form that updates the postgresql database when submitted, the update or insert would silently fail where special characters are part of the form's input.

To prevent the update/insert failure, I have perl strip all special characters from the form's input prior to performing the insert/update. A better sulotion would be to have the perl application replace all special characters with an escaped special character. Then, when selects are performed all special characters would again need to be escaped.

LelandJ

Leland F. Jackson, CPA
Software - Master (TM)

http://www.smvfp.com

Nothing Runs Like the Fox

 

[Hendricus]

Hi there,

I seem to have a similar problem, I've been working on a website driven by phpmysql. Now the client wants it to be in PostGreSQL. I've no experience at all with PostGreSQL but I started converting the mysql database anyways. Here is what I made of my MySQL create table code:

CREATE TABLE `content` (
`autoID` int(11) NOT NULL auto_increment,
`section` tinytext NOT NULL,
`sectionID` tinytext NOT NULL,
`subsection` tinytext NOT NULL,
`subsectionID` tinytext NOT NULL,
`content` longtext NOT NULL,
`public` tinytext NOT NULL,
`date` datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`autoID`),
UNIQUE KEY `autoID` (`autoID`)
) TYPE=MyISAM PACK_KEYS=0 AUTO_INCREMENT=96 ;

became in PostgreSQL

CREATE TABLE "content" (
"autoID" int8 NOT NULL,
"section" text NOT NULL,
"sectionID" text NOT NULL,
"subsection" text NOT NULL,
"subsectionID" text NOT NULL,
"content" text NOT NULL,
"public" text NOT NULL,
"date" timestamp NOT NULL
);
CREATE UNIQUE INDEX "content_autoID_key" ON "content" ("autoID"

I'm not sure if I did this correctly but it seems to work. Any hints and tips are more than welcome though!

With some cutting and pasting and finding and replacing I converted the insert code into this:

Met wat knip en plak werk en find and replace heb ik de content uit MySQL dump omgezet naar PostGrez. Hierbij kwam ik een probleem tegen:

INSERT INTO "content" ("autoID", "section", "sectionID", "subsection", "subsectionID", "content", "public", "date&quot VALUES(1, 'Home', '1', 'Raves & amp ; parties', '0', 'Fake text fake text', 'show', '2003-11-14 16:26:25+01');

Now I have a problem. And I think it's the insertion of:
'Raves & amp ; parties'

Because this piece of date contains a ; character phppgadmin won't complete the query. This wasn't a problem in phpmyadmin or with php/mysql...

Any solutions to this?? Can I change something in my datatypes settings in order to fix this?

Thanx a million,
Cheers,
Hendricus

 

[ceco]

i don't use phppgadmin and don't know what exactly is the problem

but
CREATE TABLE "content" (
"autoID" int8 NOT NULL,
"section" text NOT NULL,
"sectionID" text NOT NULL,
"subsection" text NOT NULL,
"subsectionID" text NOT NULL,
"content" text NOT NULL,
"public" text NOT NULL,
"date" timestamp NOT NULL
);
CREATE UNIQUE INDEX "content_autoID_key" ON "content" ("autoID"

should work, but it is not 100% equivelent to the mysql code you gave. It is missing the primary key, and the auto_increment

you should do it
CREATE TABLE "content" (
"autoID" serial8 NOT NULL primary key,
"section" text NOT NULL,
"sectionID" text NOT NULL,
"subsection" text NOT NULL,
"subsectionID" text NOT NULL,
"content" text NOT NULL,
"public" text NOT NULL,
"date" timestamp NOT NULL
);
CREATE UNIQUE INDEX "content_autoID_key" ON "content" ("autoID"

but using psql it works
when making your own programs with the php's pg_query you can call many commands at once separated with ; (unlike mysql_query) so you should be very carefull that malicious user doesn't stop your string with ' or " and then types ;delete from xxxx (i'm not sure it's clear ;-))