如何使用Javascript和PHP中的XMLHttpRequest将HTML内容发送到MSSQL数据库字段？

Question

I have a HTML editor for a website and I want to save the content of it as HTML into a MSSQL field.

In MSSQL, I am using a varchar(max) datatype to store the HTML.

This is the following code:

HTML:

<div id="content">CUSTOM HTML CODE HERE</div>

Javascript:

    var content = document.getElementById('content').value;
    var xhr = new XMLHttpRequest;
    xhr.open('GET', "php/editscriptpage.php?pagecontent="+content);
    xhr.send();

PHP:

<?php
ini_set("magic_quotes_sybase",1);
function mssql_escape($str)
{
   if(get_magic_quotes_gpc())
   {
    $str= stripslashes($str);
   }
   return str_replace("'", "''", $str);
}
$serverName = "servernamehere";
$connInfo = array("Database"=>"db_name", "UID"=>"sa", "PWD"=>"xxxxxxxx");
$conn = sqlsrv_connect($serverName, $connInfo);
$pagecontent =  mssql_escape($_GET["pagecontent"]);

if($conn){
  $sql = "update script_master set page_content = '$pagecontent'";
  $stmt = sqlsrv_query( $conn, $sql);
  if( $stmt === false ) {
              die( print_r( sqlsrv_errors(), true));
  }
}
else {
  die( print_r( sqlsrv_errors(), true));
}
?>

The above code works perfectly if I put in plain text, for example:

This saves perfectly:

"Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua."

This issue is: Whenever I use HTML, either some of it seems to get truncated at some point OR nothing saves at all.

For example:

This does not save in MSSQL:

<div><span style="color: rgb(0, 0, 0); font-family: &quot;Open Sans&quot;, Arial, sans-serif; text-align: justify; background-color: rgb(255, 255, 255);">"Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.</span></div>

I have a feeling that the problem lies within sending the Ajax request. (Something in the HTML code is messing with the request).

My Question is: Are there any best practices for saving HTML code into MSSQL using AJAX? Am I missing something important or is there a better way to do this?

Answer 1

For the JavaScript, you should be using a library such as jQuery to deal with various browser incompatibilities, and you should not be sending complex strings like this through GET requests. You aren't getting anything, you're submitting something to a database:

<form id="the_form">
    <textarea id="pagecontent"></textarea>
    <button type="submit">Submit</button>
</form>

<script>
    $("#the_form").submit(function(e) {
        e.preventDefault();
        var data = {"pagecontent": $("#content").val()};
        $.post("php/editscriptpage.php", data);
    });
</script>

Keeping in mind that "magic quotes" have been deprecated for years, and not available since PHP 5.4, we can simplify this code. Also you should be using parameterized queries, not just sticking strings together; this provides many benefits including the "escaping" you may have been getting previously.

<?php
$serverName = "servernamehere";
$connInfo = array("Database"=>"db_name", "UID"=>"sa", "PWD"=>"xxxxxxxx");
$conn = sqlsrv_connect($serverName, $connInfo);
$pagecontent = $_POST["pagecontent"];

if($conn){
  $sql = "UPDATE script_master SET page_content = ?";
  $stmt = sqlsrv_query($conn, $sql, array($pagecontent));
  if( $stmt === false ) {
    die(print_r(sqlsrv_errors(), true));
  }
}
else {
  die(print_r(sqlsrv_errors(), true));
}
?>