How to insert a hyperlink into a database?

Question

Hi guys I am trying to put a social share button to my article dynamically. I want to insert a social share script along with my article text into a database and display it with a unique share link. so I am coding this way. This might not be a good idea but I would like to know if its possible this way.

string socialButton = "
<div id='social_nav_horizontal'>    <h3>        Bookmark or Share This Post</h3>    <ul>        <li>            <a class='delicious' href='http://del.icio.us/post?url=Your website title' title='Share this on del.icio.us'>Delicious</a></li>     <li>            <a class='facebook' href='http://www.facebook.com/sharer.php?u=http://yourwebsite.com'>Facebook</a></li>        <li>            <a class='stumbleupon' href='http://www.stumbleupon.com/submit? url=http://www.yoursite.com/'>Stumble</a></li>      <li>            <a class='twitter' href='http://twitter.com/home?status=Your Website Title- http://yourwebsite.com@TwitterUserName'>Twitter</a></li>    </ul></div><p>  &nbsp;</p>";

So "insert into myArticleTable (articleText) values ('"+socialButton + articleText.Text+"')"; But it throws an exception when trying to insert.

System.Data.OleDb.OleDbException: Syntax error (missing operator) in query expression ''

This work only if I directly past it to my ckeditor (wysiwyg) rich text editor and execute the insert statement on a button click event.

Can you help?

Thank you

Answer 1

Because a single quote is used for indicating the start and end of a string, you need to escape it. The short answer is to use two single quotes - '' - in order for a SQL database to store the value as '.

Ex:-
Insert into Person
(First, Last)
Values
('Joe', 'O''Brien')

Look at using REPLACE to sanitize incoming values:

• Oracle REPLACE
• SQL Server
• REPLACE MySQL
• REPLACE PostgreSQL REPLACE

You want to check for '''', and replace them if they exist in the string with '''''' in order to escape the lone single quote.

Answer 2

You haven't yet realized it, but you just discovered SQL injection vulnerability in your code. Basically anyone who can run this code on the server can put malicious SQL inside your article and it will be executed.

This is what the error is trying to say in disguise: it interpreted the contents of your link/article and made them part of the SQL - which became syntactically incorrect. Don't try to escape the value socialButton and articleText.Text manually. Use named parameters instead:

insert into myArticleTable (articleText) values (?)

And populate this parameter with something like this:

cmd.Parameters.AddWithValue("parm1", socialButton + articleText.Text);

Remember, never generate SQL manually by concatenating strings. Moreover, named parameters are much cleaner.

Disclaimer: I am not a .NET developer, copied code snippet from How to parameterize complex OleDB queries? and how to update a table using oledb parameters? But the same rules apply in virtually every language.