CODEWITHSUNDEEP
mysqlsql
Jam
Jam

Reputation: 17

Store html syntax in MySql

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.

Upvotes: 0

Views: 919

Answers (2)

Bill Karwin
Bill Karwin

Reputation: 562270

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.

Upvotes: 1

vaio
vaio

Reputation: 101

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

Upvotes: 0

Related Questions