Syntax error when importing mysql table with html code in it

Question

Im trying to import a mysql table with data that has html code in it and it generates syntax error. Can someone tell me how to properly import mysql with html code

Sytanx error :

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''<ul>\r\n<li>Unpack the equipment and materials</li>\r\n<li>Secure the mounting '

Wallas

Please post the exact error you get – Shut

@Shut edited the post – Wallas

How did you create that file? Some tools should not be used for DB export. – Verboten

Show us the SQL statement. – Russellrusset

How are you trying to import the data? mysql command line, mysql workbench, phpmyadmin, php, ??? It's hard to solve the problem without knowing more details. – Hitherto

To answer your question, we'll need a bit more. If you post a MVCE, I expect you'll have an answer in under 30 minutes. – Eudoca

Please tell us how are you importing the data? As, I'm able to import mysql table containing html text using phpmyadmin – Kila

It tells us ''< you have to single quotes (the first is the start of the single quotes being used by my sql the second is a rouge quote in your SQL) in there so it's complaining your HTML has single quotes in it and not ' as it should have for single quotes. i'm also unsure why you have a single quote in front of a <ul> that makes no sense... you need to go through and escape all your HTML to be SQL safe – Gauguin

Whatever program generated the SQL, it did it wrong. Export it again using a proper tool – Ommatidium

Answer 1

By the very little you have shown us the error i can see that it's complaining about a rouge single quote '

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''<ul>\r\n<li>Unpack the equipment and materials</li>\r\n<li>Secure the mounting '

If you notice at the start of the quoted text from MySQL it has '' and at the end it only has ' MySQL errors show the problem code at the start location in single quotes so we remove one single quote wrapping the errored syntax we end up with

'<ul>\r\n<li>Unpack the equipment and materials</li>\r\n<li>Secure the mounting

So it's complaining about a single quote before a <ul>. you need to make sure you HTML is MySQL safe before entering it into the Database so replace single quotes as \' or if it's supposed to be rendered you should use '

if your using php you can use addslashes to escape any single quotes ' with \' before entry.

Another option is to stop using directly constructed queries and use prepare & bind_param in your connector as it will then prevent this from happening.

Unfortunately, however, If you're using mysqlimport cli and this is a prebuilt .sql file your gonna have to go through it by hand and fix it I highly recommend a syntax enabled editor to do this MySQL workbench it the best for MySQL syntax

Gauguin