Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

My customer is using a very outdated ecommerce management system on my hosting service. For that product, no support is being provided anymore by the vendor.

Brief explanation: the shop website, that claims to run under LAMP stack, is built by an old Visual Basic Windows application running on MS Access. The user constructs the shop, defines the HTML template, adds products and categories, etc.

Then the VB exe builds the PHP pages (one for each template page) and the SQL script to run on MySQL. It also uploads everything via FTP and runs the installation/upgrade script on its own.

The problem

Browsing the website, many products' descriptions are cut before the euro sign. For example, what was supposed to be "Product price €1000" becomes "Product price"

The analysis

  • MySQL contains a cutted description until the € sign, so it's not PHP fault
  • The Access databases contain full description with € sign, so it's not fault of the webmaster writing bad description or eDisplay cutting them
  • The SQL that will run once the site gets uploaded, stored on my local machine before upload, contains the € sign
  • The same script, after being FTPed by eDisplay and opened with nano from SSH, shows the € sign messed up like this:
  • vsftpd log reports (obfuscated for privacy) Sat Dec 15 11:16:57 2012 22 xxx.xxx.128.13 1112727 /srv/www/domains/xxxxxx.it/htdocs/db.sql b _ i r xxxxxxx ftp 0 * c which seems to be a binary transfer (and also a huge security vulnerability because you can download the whole database from unauthenticated HTTP)
  • The eDisplay internal FTP client provides no option for ascii/binary transfer modes
  • [Add] Trying to manually upload the SQL file via SFTP shows messing up euro
  • [Add2] Trying to manually upload using Xftp client with explicit ASCII mode doesn't fix too

It looks like the file gets uploaded as binary. Perhaps on the customer's previous host it all worked fine because that was a Windows host.

The server

It's an Azure virtual machine running openSUSE 12.2 with both vsftpd and openSSH

The question

Without asking the customer to manually upload files using FileZilla or replacing with €, because he refuses, what can I do on server side to prevent vsftpd to screw up euro sign?

share|improve this question
    
What you are seeing in nano is a red herring, it is displaying that because it doesn't know that it's a €. –  Iain Dec 15 '12 at 12:33
    
Fire your customer. –  Tom O'Connor Dec 15 '12 at 13:02
2  
It would probably be less work to set up a real e-commerce site and import all the data from this abomination, than to fix the misplaced € sign. –  Michael Hampton Dec 15 '12 at 15:17
    
@MichaelHampton I would upvote 10x –  djechelon Dec 15 '12 at 16:30
add comment

2 Answers

You can try altering the encoding of the SQL file along with the line delimiter. Or YOU could change all the symbol to €.

share|improve this answer
    
The second is the real problem. Everytime somebody changes the offline DB (ie. add new product) I would need to manually change the euro in that row. Anyway changing the encoding of MySQL to latin1_gen_ci seemed to partially work. I said partially because now the text doesn't get truncated but still messed up. I'm working on that. –  djechelon Dec 15 '12 at 12:11
add comment
up vote 0 down vote accepted

The Partial fix

By setting MySQL's default character set different than UTF-8 I can display Euro correctly

Source

#default-character-set = utf8
collation-server = latin1_general_ci
#init-connect='SET NAMES latin1_general_ci'
character-set-server = latin1

First line is commented because my version of MySQL didn't recognize it Third is commented because I got an error on every connection logged on server log and connection truncated (so every site offline)

I'm saying the fix is partial because I bypassed the FTP upload and ran the SQL directly into phpMyAdmin with correct Euro sign

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.