Store html syntax in MySql

Question

I want to store a html syntax string in MySql table, for example <a href="https://www.google.com">Google</a>. if im directly trying to insert this, im getting sql syntax error. I saw the same question in Store HTML into MySQL database, here the solution I got from above link is added one "'" before and after the url. So i need to know any alternative on this? Because if I'm adding this, im able to save that in db, but while fetching this i need to pass the same string in to a jsp page, so i need to remove that extra chars added while saving the link to db. To avoid this is ther any alternatives possible?

Elaborating my requirement : i need to store a string like <a href="https://www.google.com">Google</a> into a table 'GoogleLinks' and column 'links' which have type Varchar. After that, i need to get the string back in a string variable and need to pass this to jsp page, where this string will come in between

tag. So is there any way to store this in database without modifying the html string, or is there any method we can apply before and after to avoid sql syntax error. Java is my backend language.

Possible duplicate of [Store HTML into MySQL database](https://stackoverflow.com/questions/2641561/store-html-into-mysql-database) — , Oct 18 '17 at 18:35
You need to understand the answer to the question you linked (no, the sum total of the answer was not to just add quotes around the url) - https://stackoverflow.com/q/2641561/4843530. Once you do, you will see that your question is a duplicate of that one. — , Oct 18 '17 at 18:36

score 1 · Answer 1 · answered Oct 18 '17 at 18:11

This is an example of doing what you describe:

mysql> create table GoogleLinks ( links varchar(255) );
Query OK, 0 rows affected (0.03 sec)

mysql> insert into GoogleLinks set links = '<a href="https://www.google.com">Google</a>';
Query OK, 1 row affected (0.02 sec)

mysql> select * from GoogleLinks;
+---------------------------------------------+
| links                                       |
+---------------------------------------------+
| <a href="https://www.google.com">Google</a> |
+---------------------------------------------+

If your string contains literal apostrophe characters ('), you have to escape them.

For what it's worth, I would never store HTML syntax in the database. I'd store the URL and the text separately.

mysql> create table GoogleLinks ( url varchar(255), linktext varchar(255) );
Query OK, 0 rows affected (0.03 sec)

mysql> insert into GoogleLinks set url = 'https://www.google.com', linktext = 'Google';
Query OK, 1 row affected (0.01 sec)

mysql> select * from GoogleLinks;
+------------------------+----------+
| url                    | linktext |
+------------------------+----------+
| https://www.google.com | Google   |
+------------------------+----------+

In your application code, fetch these two attributes as-is, and then format them into an HTML template.

Performing HTML formatting in the application view rather than the database allows you to customize presentation more easily, for example if you need to apply a CSS class to hyperlinks.

"For what it's worth, I would never store HTML syntax in the database. I'd store the URL and the text separately.".... Amen to that. — , Oct 18 '17 at 18:32

score 0 · Answer 2 · answered Oct 18 '17 at 18:06

0

You can decode and encode your string with Base64. That would make sure that no special characters exist in your string.

answered Oct 18 '17 at 18:06

vaio

91
11

Store html syntax in MySql

2 Answers2