0

Im searching all over for an answer to this and still cant seem to find it

Server : localhost
MySQL Version : 5.6.17

I have set my db's tables to utf8mb4 to graduate to more character possibilities. When i insert certain hashed information into my table, depending on the random sequence of characters in the string, only a fragment of the string actually gets inserted (if not left completely blank).

In my handle for my pdo statement, i do charset=utf8mb4 before prepping my query, and as fail safe i also SET NAMES utf8mb4 before. So i thought everything should be kosher with the CHARSET and COLLATE.

So my question is, what should i be doing to successfully put the entire string into field? Is there some character escaping i should be doing that im missing?

Edit : When i copy/paste the string into phpmyadmin, it successfully puts the entire string in the field.

TGammage
  • 134
  • 8
  • example of a srting i try to insert M^)E¬öJª1t®LÇÕtËe?~Ru4M‚ª ¡Ç5DRE#‘£zcð/Ó,Fà· –ÐoAK£ë¿à¾9Ú – TGammage Sep 08 '15 at 17:28
  • Really? That looks more like a `BLOB`, not some form of readable `TEXT` in any current language. – Rick James Sep 08 '15 at 22:45

2 Answers2

0

Let's see the hex for the truncated string.

Probably the answer lies in failure to establish that the client is using utf8mb4 via SET NAMES utf8mb4 or some parameter on the connection call.

http://php.net/manual/en/ref.pdo-mysql.connection.php

Rick James
  • 135,179
  • 13
  • 127
  • 222
0

OK, so i found the solution i was looking for and feel like an dummy for spending so much time on a fix. Maybe this will help someone traveling down the same path, so i'll post the answer myself.

More About The Behavior :

The reason the PDO would fail to put the string in the db field in its entirety was because it was not utf8_encoded. (This is where i felt dumb). Thinking it was already prepped for entry, and all i had to do was bind, was wrong. Instead it would terminate the string at the first unrecognizable character (using utf8mb4), or put the whole string, and replace unrecognizable characters with ? (using just utf8).

The Solution :

What i ended up doing was taking the original string, run it through ut8_encode() and since this was information provided by the program and not user input, i didnt bother to bind and instead used $pdoHandle->quote($utf8String) which prepped it for insertion.

After executing, the whole string was inserted. Now when i select the hashed info, i run utf8_decode and have exactly what i inserted in the db.

Better Practices? If you know a better way to approach this solution, im open to hear alternatives. Do please include a bit on why, so i can grasp an understanding of your approach.

TGammage
  • 134
  • 8