Issue with apostrophe " ' " while saving content to mysql database

Question

I am working on an application which allows users to create html templates and save them.The users can use different components like text,image etc and create html pages.

Issue: The problem I am facing is,when the user enters some text with apostrophe ',I get an mysql error(obviously it should).So,I added mysql_real_escape_string to the variable before passing it to the query.It works,but I want the data back for the user to edit the site.When I try to fetch it back,there is a error as the content returned has slashes added.
I cannot use stripslashes as my content may have slashes as a part of the text entered by user.

This is how add it to the database:

 $revisionContent = mysql_real_escape_string($_POST['txtComp']);

This is the query

  $query = "insert into revision (userId,revisionContent,webId,pageId,status,saveType,dateAdded) values ('".$_SESSION['gogiUserId']."','$revisionContent','$webId','$pageId','$status','$saveType','$toDate')";

I want the retrieved value in javascript variable,so I do it like this

 var getSavedContent = '<?php echo json_encode($IdLessContent); ?>';

But then i get this error!

    SyntaxError: missing ; before statement
     [Break On This Error]  
     ...helvetica,sans-serif;\"><strong>Text **Bo'x**(here is the apostrophe)<\/strong><\/span><\/span><\/p>\n<ol>\n...

If I remove json_encode it gives me this error for syntax.

   SyntaxError: syntax error
   [Break On This Error]     
   var getSavedContent = <div style="z-index: 1001; height: 241px; width: 725px; to...

This is strongly related to SQL injection attacks, a major security risk. Read about it. — leftclickben, Mar 01 '13 at 09:09
if I escape my variables with `mysql_real_escape_string`,it should remove the possibility of injection.Right? — KillABug, Mar 01 '13 at 09:11
You should use ' for apostrophe. refer http://www.tedmontgomery.com/tutorial/htmlchrc.html — عثمان غني, Mar 01 '13 at 09:12
You should use MySQLi or PDO with prepared statements, then this ceases to be a problem, and you're not going to get MySQL deprecated messages either — Mark Baker, Mar 01 '13 at 09:19

score 2 · Accepted Answer · answered Mar 01 '13 at 09:14

2

This has nothing to do with MySQL. You problems don't start until after you get data out of it.

You are taking a data structure, which may have an apostrophe in it, converting it to JSON and then turning that JSON text into a JavaScript string by wrapping quotes around it.

Since JSON is (more or less) a subset of JavaScript, you can deal with this problem by skipping the "Turn into a JS string" and "Parse string of JSON to a JS object" steps.

Remove the quotes:

 var getSavedContent = <?php echo json_encode($IdLessContent); ?>;

answered Mar 01 '13 at 09:14

Quentin

914,110
126
1,211
1,335

Thanks for the prompt reply.I had tried this,but for some reason it strips of the first character of the content.Example: If I have`
` I get `div class="hello">` which is invalid and appears as text on the console.
– KillABug Mar 01 '13 at 09:21
any thing that I might have missed!!Anything else I need to do get it working – KillABug Mar 01 '13 at 09:47
+1 answer this correct. The `mysql_real_escape_string` is fine; it's the JS output string that is the problem, and the problem with it is the quotes around it it. `json_encode()` already adds the quotes that it needs, so you don't need them. Re your comment, json_encode will not be removing the first character; there are likely to be other factors involved, but we don't have enough info to help with that. – SDC Mar 01 '13 at 09:58

Issue with apostrophe " ' " while saving content to mysql database

1 Answers1