4

I am trying to implement Summernote WYSIWYG editor into my form, when i submit the form to write to the mssql table, it accepts most special characters ( $%!< > etc) even double quotes are fine, however when I try and use a single quote I get an sql error come back. Can you help me with what I have missed.

The first line implements the plugin

 <textarea name="majorupdate" id="summernote" rows="10" class="form-control"></textarea>

when submitted its Posting it to a second page which uses the following values (taken out some of the irrelevant entries to keep it simple)

$majorupdateX = $_POST['majorupdate'];

$query = mssql_query("INSERT INTO PRJ_Update1 (update1) VALUES ('$majorupdateX') ");

when calling back the entry from the sql table, it is displayed on the page like so

echo                '<p>'.$row[update1].'</p>';

I suspect I need to somehow replace the special characters for escaped html equivalents? not sure how best to do that, being new to this I had hoped the summernote text editor would of taken care of that. I tried using the htmlspecialchars() function on the POST value, but had no success. It may be the case I was not using it correctly.

the error

Warning: mssql_query() [function.mssql-query]: message: Line 1: Incorrect syntax near 's'. (severity 15) in C:\WPServer\Web\dev\Rehan\ProjectTracker\PR_projectUpdates1_formpost.php on line 47

Warning: mssql_query() [function.mssql-query]: message: Unclosed quotation mark before the character string ') '. (severity 15) in C:\WPServer\Web\dev\Rehan\ProjectTracker\PR_projectUpdates1_formpost.php on line 47
Ray_Hack
  • 973
  • 2
  • 9
  • 27
  • *"when I try and use a single quote I get an sql error come back"* - being? share that. we can't help if we don't know what the error is. – Funk Forty Niner Apr 23 '15 at 15:15
  • 1
    - escape/sanitize your data – Funk Forty Niner Apr 23 '15 at 15:16
  • as per your edit, see comment #2 ;-) consult http://stackoverflow.com/q/60174/ or use prepared statements. – Funk Forty Niner Apr 23 '15 at 15:21
  • 1
    Aside from the fact it's much better in terms of security (SQL Injection), using prepared statements will escape all data regardless. Also I would highly recommend you move over to the Microsoft PHP Drivers for SQL Server: http://www.microsoft.com/en-gb/download/details.aspx?id=20098, as this will allow parameterised queries. Then check here for the BOL: http://php.net/manual/en/book.sqlsrv.php. @Fred-ii- you can't come here! You're a Romulan! ;) – John Bell Apr 23 '15 at 15:22
  • @JohnnyBell I am? gee, thanks ;-) here I thought I was Italian. – Funk Forty Niner Apr 23 '15 at 15:24
  • 1
    Try to use a parameterized query as suggested here; http://stackoverflow.com/questions/15840642/how-to-correctly-sanitize-mssql-query-that-stores-emails – Totoro53 Apr 25 '15 at 09:15
  • Following your suggestion I looked at using Parameterized queries and my understanding from everything I have read is in order to do this I would need to move to the srvsql driver. As this server is being shared between a team of us and contains many internal sites using mssql, is there any way I can set up the srvsql driver to be used without compromising or needing to update the existing content ? i.e let them continue to use the mssql commands. – Ray_Hack Apr 27 '15 at 09:51

1 Answers1

0

Use this

ALTER DATABASE databasename CHARACTER SET utf8 COLLATE utf8_unicode_ci;

ALTER TABLE tablename CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
SuReSh
  • 1,503
  • 1
  • 22
  • 47