Cannot store special chars using c lib

Started by Alexander Reichstadtabout 14 years ago5 messagesgeneral

lxr@mac.com

about 14 years ago

Hi,

I need to be able to store special chars, German Umlaute, in my tables. This works when using pgPHPAdmin to store the same value to the same field. But when using the c-library it doesn't, fields stored are garbled.

I checked using \l to see what encoding the database is which is UTF8, UTF8 is what's required.

List of databases
Name | Owner | Encoding | Collation | Ctype | Access privileges
-----------+----------+----------+-----------+-------+-----------------------
MyDB | postgres | UTF8 | C | C |
alltypes | postgres | UTF8 | de_DE | C |
postgres | postgres | UTF8 | C | C |
template0 | postgres | UTF8 | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | C | C | =c/postgres +
| | | | | postgres=CTc/postgres

As a way to store things I use PGSQLKit, which in turn uses

const char *cString = [sql cStringUsingEncoding:defaultEncoding];
if (cString == NULL)
res = PQexec(pgconn, cString);

to store things. The defaultEncoding I also changed explicitly from defaultEncoding to UTF8 to try, but got the same result. As far as I can see this is not an error on part of the PGSQLKit.

From what I read there is no table specific encoding.

The collation is set to C, but that's something only relevant to sorting as far s I understand.

So, I am at a loss as to where things go wrong.

Any ideas?

Thanks

Alexander Reichstadt

lxr@mac.com

about 14 years ago

In reply to: Alexander Reichstadt (#1)

Re: Cannot store special chars using c lib

Sorry, there is a copy-paste error, actually the code really is:

const char *cString = [sql cStringUsingEncoding:defaultEncoding];
if (cString == NULL) {

blablabla
//This just catches cases where cString failed to encode.
}

res = PQexec(pgconn, cString);

Am 22.03.2012 um 09:02 schrieb Alexander Reichstadt:

Show quoted text

Hi,

I need to be able to store special chars, German Umlaute, in my tables. This works when using pgPHPAdmin to store the same value to the same field. But when using the c-library it doesn't, fields stored are garbled.

I checked using \l to see what encoding the database is which is UTF8, UTF8 is what's required.

List of databases
Name | Owner | Encoding | Collation | Ctype | Access privileges
-----------+----------+----------+-----------+-------+-----------------------
MyDB | postgres | UTF8 | C | C |
alltypes | postgres | UTF8 | de_DE | C |
postgres | postgres | UTF8 | C | C |
template0 | postgres | UTF8 | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | C | C | =c/postgres +
| | | | | postgres=CTc/postgres

As a way to store things I use PGSQLKit, which in turn uses

const char *cString = [sql cStringUsingEncoding:defaultEncoding];
if (cString == NULL)
res = PQexec(pgconn, cString);

to store things. The defaultEncoding I also changed explicitly from defaultEncoding to UTF8 to try, but got the same result. As far as I can see this is not an error on part of the PGSQLKit.

From what I read there is no table specific encoding.

The collation is set to C, but that's something only relevant to sorting as far s I understand.

So, I am at a loss as to where things go wrong.

Any ideas?

Thanks

Alexander Reichstadt

lxr@mac.com

about 14 years ago

In reply to: Alexander Reichstadt (#2)

Re: Cannot store special chars using c lib

Hi,

I found out that apparently in PGSQLKit there is an error in PQescapeStringConn or the way it is being used.

From the docu I take it this is to prevent SQL injection attacks. I removed any processing and it turned out the issue ceases, all works fine.

The call is here:

-(NSString *)sqlEncodeString:(NSString *)toEncode
{
return toEncode;
//size_t result;
int error;
char *sqlEncodeCharArray = malloc(1 + ([toEncode length] * 2)); // per the libpq doc.
const char *sqlCharArrayToEncode = [toEncode cStringUsingEncoding:defaultEncoding];
size_t length = strlen(sqlCharArrayToEncode);

PQescapeStringConn ((PGconn *)pgconn, sqlEncodeCharArray,
(const char *)[toEncode cStringUsingEncoding:defaultEncoding],
length, &error);

NSString *encodedString = [[[NSString alloc] initWithFormat:@"%s",sqlEncodeCharArray] autorelease];
free(sqlEncodeCharArray);

return encodedString;

}

This indicates that the problem is in PGSQLKit and not in any settings for tables or the database itself. So I take it to the Cocoa list at Apple.

Thanks and regards
Alex

Am 22.03.2012 um 09:06 schrieb Alexander Reichstadt:

Show quoted text

Sorry, there is a copy-paste error, actually the code really is:

const char *cString = [sql cStringUsingEncoding:defaultEncoding];
if (cString == NULL) {

blablabla
//This just catches cases where cString failed to encode.
}

res = PQexec(pgconn, cString);

Am 22.03.2012 um 09:02 schrieb Alexander Reichstadt:

Hi,

I need to be able to store special chars, German Umlaute, in my tables. This works when using pgPHPAdmin to store the same value to the same field. But when using the c-library it doesn't, fields stored are garbled.

I checked using \l to see what encoding the database is which is UTF8, UTF8 is what's required.

List of databases
Name | Owner | Encoding | Collation | Ctype | Access privileges
-----------+----------+----------+-----------+-------+-----------------------
MyDB | postgres | UTF8 | C | C |
alltypes | postgres | UTF8 | de_DE | C |
postgres | postgres | UTF8 | C | C |
template0 | postgres | UTF8 | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | C | C | =c/postgres +
| | | | | postgres=CTc/postgres

As a way to store things I use PGSQLKit, which in turn uses

const char *cString = [sql cStringUsingEncoding:defaultEncoding];
if (cString == NULL)
res = PQexec(pgconn, cString);

to store things. The defaultEncoding I also changed explicitly from defaultEncoding to UTF8 to try, but got the same result. As far as I can see this is not an error on part of the PGSQLKit.

From what I read there is no table specific encoding.

The collation is set to C, but that's something only relevant to sorting as far s I understand.

So, I am at a loss as to where things go wrong.

Any ideas?

Thanks

Laurenz Albe

laurenz.albe@cybertec.at

about 14 years ago

In reply to: Alexander Reichstadt (#1)

Re: Cannot store special chars using c lib

Alexander Reichstadt wrote:

I need to be able to store special chars, German Umlaute, in my tables. This works when using
pgPHPAdmin to store the same value to the same field. But when using the c-library it doesn't, fields
stored are garbled.

I checked using \l to see what encoding the database is which is UTF8, UTF8 is what's required.

List of databases
Name | Owner | Encoding | Collation | Ctype | Access privileges
-----------+----------+----------+-----------+-------+-----------------------
MyDB | postgres | UTF8 | C | C |
alltypes | postgres | UTF8 | de_DE | C |
postgres | postgres | UTF8 | C | C |
template0 | postgres | UTF8 | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | C | C | =c/postgres +
| | | | | postgres=CTc/postgres

As a way to store things I use PGSQLKit, which in turn uses

const char *cString = [sql cStringUsingEncoding:defaultEncoding];
if (cString == NULL)

res = PQexec(pgconn, cString);

to store things. The defaultEncoding I also changed explicitly from defaultEncoding to UTF8 to try,
but got the same result. As far as I can see this is not an error on part of the PGSQLKit.

From what I read there is no table specific encoding.

The collation is set to C, but that's something only relevant to sorting as far s I understand.

So, I am at a loss as to where things go wrong.

Any ideas?

I know nothing about PGSQLKit, but you should check what client_encoding is set to.
If it is set to something else than UTF8, say for example LATIN1, then PostgreSQL
will happily interpret the bytes in your UTF8 string as LATIN1 and convert them
to UTF8, resulting in things like 'schÃ¶ne ScheiÃ\u009Fe'.

Yours,
Laurenz Albe

Alexander Reichstadt

lxr@mac.com

about 14 years ago

In reply to: Laurenz Albe (#4)

Re: Cannot store special chars using c lib

Thanks, Albe, I had checked this, too, and it was ok. I already posted the solution to the board. It was an error due to an incorrect conversion between an object-instance and a char. It works now.

Am 22.03.2012 um 12:50 schrieb Albe Laurenz:

Show quoted text

Alexander Reichstadt wrote:

I need to be able to store special chars, German Umlaute, in my tables. This works when using
pgPHPAdmin to store the same value to the same field. But when using the c-library it doesn't, fields
stored are garbled.

I checked using \l to see what encoding the database is which is UTF8, UTF8 is what's required.

List of databases
Name | Owner | Encoding | Collation | Ctype | Access privileges
-----------+----------+----------+-----------+-------+-----------------------
MyDB | postgres | UTF8 | C | C |
alltypes | postgres | UTF8 | de_DE | C |
postgres | postgres | UTF8 | C | C |
template0 | postgres | UTF8 | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | C | C | =c/postgres +
| | | | | postgres=CTc/postgres

As a way to store things I use PGSQLKit, which in turn uses

const char *cString = [sql cStringUsingEncoding:defaultEncoding];
if (cString == NULL)

res = PQexec(pgconn, cString);

to store things. The defaultEncoding I also changed explicitly from defaultEncoding to UTF8 to try,
but got the same result. As far as I can see this is not an error on part of the PGSQLKit.

From what I read there is no table specific encoding.

The collation is set to C, but that's something only relevant to sorting as far s I understand.

So, I am at a loss as to where things go wrong.

Any ideas?

I know nothing about PGSQLKit, but you should check what client_encoding is set to.
If it is set to something else than UTF8, say for example LATIN1, then PostgreSQL
will happily interpret the bytes in your UTF8 string as LATIN1 and convert them
to UTF8, resulting in things like 'schÃ¶ne ScheiÃ\u009Fe'.

Yours,
Laurenz Albe

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general