Alternative to the ' character for marking nonnumeric data?

[CHeighlund]

Is there an alternative way to mark nonnumeric data for database insertion, other than surrounding it with sinqle quote characters? I am working on a project designed to place information into a pg database, but some of the incoming data has apostrophes in it, and I have been informed it would not be a good idea to parse these from the data.

If there is no alternative way to mark nonnumeric data for insertion or updating, then is there some way to escape the apostrophe(s) when inserting/updating the statement(s) in question?

Thank you for your assistance.

 

[feherke]

Hi

Is there an alternative way to mark nonnumeric data for database insertion, other than surrounding it with sinqle quote characters?

No. But no need anyway. Just escape them.

[blue]psql=#[/blue] [b]insert[/b] [b]into[/b] [green][i]table[/i][/green] ([green][i]field[/i][/green]) [b]values[/b] ('Pascal''s style');
[gray]INSERT 41270136 1[/gray]
[blue]psql=#[/blue] [b]insert[/b] [b]into[/b] [green][i]table[/i][/green] ([green][i]field[/i][/green]) [b]values[/b] ('C\'s style');
[gray]INSERT 41270137 1[/gray]
[blue]psql=#[/blue] [b]select[/b] * [b]from[/b] [green][i]table[/i][/green];
     [green][i]field[/i][/green]
----------------
 Pascal's style
 C's style
[gray](2 rows)[/gray]

some of the incoming data has apostrophes in it

And what if there is not only apostrophe, but an apostrophe followed by malicious code ? Read about SQL injection and quote all single quotes ( ' ) and backslashes ( ' ) in your data before including them in SQL statements.

Feherke.

http://rootshell.be/~feherke/

 

[stefanwagner]

In general, whether your data is interpreted as number or as characters is a case of table definition, not insertion.

 kram=# \d foo
          Tabelle »public.foo«
 Spalte |        Typ        | Attribute
--------+-------------------+-----------
 nr     | integer           |
 name   | character varying |

kram=# insert into foo values ('100', '100');
INSERT 0 1
kram=# insert into foo values ('010', '010');
INSERT 0 1
kram=# select * from foo;
 nr  |       name
-----+-------------------
 100 | 100
  10 | 010

don't visit my homepage:

http://home.arcor.de/hirnstrom/bewerbung

 

[CHeighlund]

@feherke:

I recognize the need for protection against an injection attack. I'm simply presuming that, because the input in question is coming from a file prepared by another of our programs (in COBOL no less), which is initiated by my boss, that there wouldn't be too much of a problem with allowing it through. This is the only input the program will use.

And I thought I had tried escaping the code and it had failed; you were, however, correct. I simply had to add more backslashes; I was only adding a single one, and that was being eaten by the program before it got to the database insertion.

@stefanwagner:

Thank you, but that wasn't the problem. I had already discovered that the system would do that. The problem was that, if I may swipe one of your examples, the input in question would have looked like this:

kram=# insert into foo values ('010', '01'0');

Please note the extra apostrophe in the second value.


Again, problem has been solved. Thank you for your responses, though.