0

I'm trying to insert an emoji to my MySQL database with a querystring and I don´t know what Im doing wrong?

I have a MySQL database that is set to utf8mb4 and I can insert and emoji to a table with the Mysqlworkbench and it shows as a real emoji. So the database is working as it should. And if I just try to show that on a .asp page then I shows an emoji as it should. I have set the database, table, and column to be utf8mb4_general_ci, is this right?

And my db connection (koppling.asp):

strConn = "driver={MySQL ODBC 5.2 Unicode Driver};server=localhost;uid=xxx;pwd=xxx;database=emojtest;option=3;charset=utf8mb4;" 
set conn = Server.CreateObject("ADODB.Connection")
conn.Open strConn

To my insertemoji.asp page I'm sending the querystring/variable like this.

www.somepage.se/emojitest/insertemoji.asp?text=

And my insertemoji.asp looks like this.

<!--#include file="koppling.asp" -->
<% 
Response.charset="utf-8"

thetext = request.querystring("text")   


sql= "INSERT INTO news (text) VALUES ('"&thetext&"');"
conn.Execute (sql)
response.write thetext (this is showing 😜😀😊😃)
%>

But the emoji is not stored as an emoji, only like this 😜😀😊😃 and if it was encoded right it would be something like this I think \xF0\x9F\x98\x82

So what am I missing? Where do I go wrong?

If it should be stored like \xF0\x9F\x98\x82, how do I then convert/encode it to a real emoji when I display it on my .asp page?

How is an emoji suppose to be stored in the DB?

  1. Should it be like a visible real emoji?

  2. Or like this 😜😀😊😃

  3. Or like this \xF0\x9F\x98\x82

Solved

I finally figured out the problem, I used the MySQL ODBC 5.2 Unicode Driver and when I changed to MySQL ODBC 3.51 Driver it worked!

Community
  • 1
  • 1
Claes Gustavsson
  • 5,509
  • 11
  • 50
  • 86
  • Is this embedded in a HTML page? Does the HTML page have `` ? – Evert Oct 06 '18 at 18:10
  • Its an .asp page and I set Response.charset="utf8mb4" in it. – Claes Gustavsson Oct 06 '18 at 18:18
  • Are you opening your .asp page in a browser? Then it's still a HTML file. And `utf8mb4` is a mysql-specific character set. Browsers don't understand it. Add the meta tag – Evert Oct 06 '18 at 18:21
  • So it should be Response.charset="utf8"? I thought that I needed to have it as utf8mb4? Have to go out, will be back in an hour. Thanks Evert – Claes Gustavsson Oct 06 '18 at 18:28
  • Evert. If we start with the db. Is my database set up right? Should it be utf8mb4_general_ci on the column or should it be utf8mb4_bin? Should it be general on the db and table and bin on the column? – Claes Gustavsson Oct 06 '18 at 19:00

1 Answers1

0

, when Mojibaked, becomes 😜😀😊😃. This because of confusion over the hex F09F989CF09F9880F09F988AF09F9883, which is treated as Emoji in utf8mb4, but that gibberish by latin1.

To see what probably went wrong in the code, see Trouble with UTF-8 characters; what I see is not what I stored

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