[SOLVED] Help - string is being trimmed automatically by Mys

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Swede78
Forum Contributor
Posts: 198
Joined: Wed Mar 12, 2003 12:52 pm
Location: IL

Help - string is being trimmed automatically by Mysql

Post by Swede78 »

I'm having an issue with Mysql trimming a space character off the string data being inserted into my database. The field type is "varchar".

What I'm doing is encrypting a string and inserting it. The problem is that MCrypt sometimes uses a space as a part of the encryption string. If that space is at the end (probably the beginning too), my decryption fails because mysql trimmed the space off.

I can't seem to find any settings to turn this auto-trim off. Is this possible? How can I force Mysql to keep the end spaces?

I can solve this by testing the decryption, if it fails - add a space and try again. But, I'd like to solve this the right way.

Any help would be appreciated!
User avatar
infolock
DevNet Resident
Posts: 1708
Joined: Wed Sep 25, 2002 7:47 pm

Post by infolock »

what is the SIZE of your varchar? maybe your field is too short to hold it all?
magicrobotmonkey
Forum Regular
Posts: 888
Joined: Sun Mar 21, 2004 1:09 pm
Location: Cambridge, MA

Post by magicrobotmonkey »

i doubt its mysql is doing it - i would guess its php
User avatar
infolock
DevNet Resident
Posts: 1708
Joined: Wed Sep 25, 2002 7:47 pm

Post by infolock »

well, i don't think it's php or else how would he know the length of the value if he isn't echoing it out? varchar's accept space values, so i'd put money on it saying it's the field size being too small..
magicrobotmonkey
Forum Regular
Posts: 888
Joined: Sun Mar 21, 2004 1:09 pm
Location: Cambridge, MA

Post by magicrobotmonkey »

why does only happen with spaces then?

Swede, can you show code of encrypting and inserting into db and decrypting and pulling from db?
User avatar
infolock
DevNet Resident
Posts: 1708
Joined: Wed Sep 25, 2002 7:47 pm

Post by infolock »

well, my only argument is it's a php encryption method... so obviously it can't be php. especially if all he's doing is posting that encrypted string to a db and then calling it back
User avatar
markl999
DevNet Resident
Posts: 1972
Joined: Thu Oct 16, 2003 5:49 pm
Location: Manchester (UK)

Post by markl999 »

mysql auto trims CHAR and VARCHAR columns of trailing spaces. One way around it is to add a . onto the end of the string when inserting and remove it on retrieval (and yes, it is a hacky as it sounds ;))
Swede78
Forum Contributor
Posts: 198
Joined: Wed Mar 12, 2003 12:52 pm
Location: IL

Post by Swede78 »

All are valid points that I thought of too. I first thought I wasn't adding slashes or stripping slashes incorrectly. But, that wasn't the case. Then, I thought, maybe it should be a longer field length, so I doubled it from 32 to 64. This didn't make a difference. The size of the string never exceeds 32 chars.

I did a test - inserted a string with a character into the database. When pulling it from the database directly, the same string doesn't have the space at the end.

I just found this at http://www.peachpit.com while searching more for the solution:

"There is also a third, minor difference between these two: MySQL trims off extra spaces from CHAR columns when data is retrieved and from VARCHAR when it's inserted."

So, it looks like it is a MySQL thing. This article was talking about the differences in field types - no solution or info on how to disable this.

Any more suggestions?
Swede78
Forum Contributor
Posts: 198
Joined: Wed Mar 12, 2003 12:52 pm
Location: IL

Post by Swede78 »

Thanks, Mark. Didn't see your message until I was done. Extra confirmation!
Swede78
Forum Contributor
Posts: 198
Joined: Wed Mar 12, 2003 12:52 pm
Location: IL

Post by Swede78 »

That's a less hacky then my idea. At least it would be consistent.
Swede78
Forum Contributor
Posts: 198
Joined: Wed Mar 12, 2003 12:52 pm
Location: IL

Post by Swede78 »

OK, found the answer. Taken from mysql.com - surprise, surprise! Looks, like I'll have to use a BLOB or TEXT field. Hopefully, it doesn't use too much more space.

The CHAR and VARCHAR types are similar, but differ in the way they are stored and retrieved.

The length of a CHAR column is fixed to the length that you declare when you create the table. The length can be any value from 0 to 255. (Before MySQL 3.23, the length of CHAR may be from 1 to 255.) When CHAR values are stored, they are right-padded with spaces to the specified length. When CHAR values are retrieved, trailing spaces are removed.

Values in VARCHAR columns are variable-length strings. You can declare a VARCHAR column to be any length from 0 to 255, just as for CHAR columns. (Before MySQL 4.0.2, the length of VARCHAR may be from 1 to 255.) However, in contrast to CHAR, VARCHAR values are stored using only as many characters as are needed, plus one byte to record the length. Values are not padded; instead, trailing spaces are removed when values are stored. This space removal differs from the standard SQL specification.

No lettercase conversion takes place during storage or retrieval.

If you assign a value to a CHAR or VARCHAR column that exceeds the column's maximum length, the value is truncated to fit.

If you need a column for which trailing spaces are not removed, consider using a BLOB or TEXT type. If you want to store binary values such as results from an encryption or compression function that might contain arbitrary byte values, use a BLOB column rather than a CHAR or VARCHAR column to avoid potential problems with trailing space removal that would change data values.
magicrobotmonkey
Forum Regular
Posts: 888
Joined: Sun Mar 21, 2004 1:09 pm
Location: Cambridge, MA

Post by magicrobotmonkey »

ahh! I dismissed MYSQL because i have had problems where the spaces weren't removed from the beginning, so i assumed php was dropping it somewhere between the encryping and the inserting. well, glad you got it done!
Post Reply