Bristol Digest, Vol 563, Issue 1

Send Bristol mailing list submissions to
bristol@mailman.lug.org.uk

To subscribe or unsubscribe via the World Wide Web, visit
https://mailman.lug.org.uk/mailman/listinfo/bristol
or, via email, send a message with subject or body 'help' to
bristol-request@mailman.lug.org.uk

You can reach the person managing the list at
bristol-owner@mailman.lug.org.uk

When replying, please edit your Subject line so it is more specific
than "Re: Contents of Bristol digest..."


Today's Topics:

1. PHP5 MCrypt Postgres encoding (Martin Moore)
2. Re: PHP5 MCrypt Postgres encoding (David Fear)


----------------------------------------------------------------------

Message: 1
Date: Tue, 12 Aug 2014 15:00:33 +0100
From: "Martin Moore" <martinm@it-helps.co.uk>
To: <bristol@mailman.lug.org.uk>
Subject: [bristol] PHP5 MCrypt Postgres encoding
Message-ID:
<!&!AAAAAAAAAAAYAAAAAAAAAFLxZtQqo65Oo+1jhlUB9DvCgAAAEAAAAPtRwkx0XABAn2JEZ3ZXBU4BAAAAAA==@it-helps.co.uk>

Content-Type: text/plain; charset="us-ascii"

I'm trying to store an MCrypted string in a Postgres text field, but I'm
having encoding issues.

I had stored the encrypted string OK via PGAdmin but a query to check it
from PHP failed with an 'invalid for UFT-8' type error.

I've recreated the DB


CREATE DATABASE vidarc
WITH OWNER = postgres
ENCODING = 'LATIN1'
TABLESPACE = pg_default
LC_COLLATE = 'en_GB'
LC_CTYPE = 'en_GB'
CONNECTION LIMIT = -1;


and now can't insert the string via PGAdmin :

ERROR: character 0xe2809e of encoding "UTF8" has no equivalent in "LATIN1"
SQL state: 22P05


I always struggle with Postgres encoding, how do I get round this (happy to
consider other reversible encryption methods if it helps!)



Martin.





------------------------------

Message: 2
Date: Tue, 12 Aug 2014 15:53:39 +0100
From: David Fear <david@dfear.co.uk>
To: martinm@it-helps.co.uk, Bristol and Bath Linux User Group
<bristol@mailman.lug.org.uk>
Subject: Re: [bristol] PHP5 MCrypt Postgres encoding
Message-ID: <53EA2A73.9090609@dfear.co.uk>
Content-Type: text/plain; charset=windows-1252; format=flowed

On 12/08/14 15:00, Martin Moore wrote:
> I'm trying to store an MCrypted string in a Postgres text field, but I'm
> having encoding issues.
>
> I had stored the encrypted string OK via PGAdmin but a query to check it
> from PHP failed with an 'invalid for UFT-8' type error.
>
> I've recreated the DB
>
>
> CREATE DATABASE vidarc
> WITH OWNER = postgres
> ENCODING = 'LATIN1'
> TABLESPACE = pg_default
> LC_COLLATE = 'en_GB'
> LC_CTYPE = 'en_GB'
> CONNECTION LIMIT = -1;
>
>
> and now can't insert the string via PGAdmin :
>
> ERROR: character 0xe2809e of encoding "UTF8" has no equivalent in "LATIN1"
> SQL state: 22P05
>
>
> I always struggle with Postgres encoding, how do I get round this (happy to
> consider other reversible encryption methods if it helps!)
>
>
>
> Martin.
>
>
>
> _______________________________________________
> Bristol mailing list
> Bristol@mailman.lug.org.uk
> https://mailman.lug.org.uk/mailman/listinfo/bristol
>
Hi

Read this:
http://stackoverflow.com/questions/11519294/encrypt-data-in-postgres-with-php

and:
http://stackoverflow.com/questions/15196151/preparing-storing-retrieving-encrypted-data-in-postgresql


The basic answer is:

from:
http://php.net/manual/en/function.pg-escape-bytea.php


===============================================
Example #1 pg_escape_bytea() example

<?php
// Connect to the database
$dbconn = pg_connect('dbname=foo');

// Read in a binary file
$data = file_get_contents('image1.jpg');

// Escape the binary data
$escaped = pg_escape_bytea($data);

// Insert it into the database
pg_query("INSERT INTO gallery (name, data) VALUES ('Pine trees',
'{$escaped}')");
?>
===============================================

I would create the database with UTF-8 encoding and make the field type
BYTEA.

PROCESS:

To store in the Postgress RDBMS:
1) MCrypt the data using mcrypt_generic()
2) Use pg_escape_bytea() to escape the MCrypted data
3) insert into the data base.

To retrieve, do the reverse of the above.



Regards

-----------------------------------
Dave Fear :: david@dfear.co.uk

Order your free giffgaff SIM card through my page and get 5 pounds free
credit http://t.co/z1KJF5y



------------------------------

_______________________________________________
Bristol mailing list
Bristol@mailman.lug.org.uk
https://mailman.lug.org.uk/mailman/listinfo/bristol

End of Bristol Digest, Vol 563, Issue 1
***************************************