jQuery, javascript array/object SQL injection issue

Ive got a problem and i don't have any idea how to fix it. Ive a module on my site that shows some data, it uses jquery dom and it creates some elements from a javascript array, witch contains javascript objects. This array is generated with php and it gets the data from a postgres database.

So if a user tries to inject the database the database stores the string as a normal entry, i have no problems with it. The problem appears when I want to list the content. But, only on client side. The listing is done, ive generated the string, i pass it to client side, the dom elements are created, but they have no content. Im using the php function addslashes() to escap开发者_StackOverflow中文版e the escapes, but i only get in trouble.

So: here is the array

[
    {"id": "26", "text": "RSS Feed collection"},
    {"id": "50", "text": "\\ \' "}
]

As you can see the last one contains the test injection

edit

HTML code:

 <div class="item">
     <span>Text</span>
 </div>

This is generated using jQuery.

JS part:

var myArray = [the upper array];
var container = $("#container");

for( nI=0, nC = myArray.length; nI<nC; nI++) {
    var currentObj = myArray[nI];
    var newItem = $("<div />").appendTo(container);
    $("<span />").appendTo(item).html(currentObj.text);
}

And the result is nothing visible, the array looks fine, but every place where normally the text should be is empty. The question is why?

Hmm, sounds like you are posting the slash (\) into the database to escape single quotations.

Mysql and other databases use repeat quotation marks to store varchar values (see below, thats a double single quotation: ' x 2).

INSERT INTO jsonTable (`ID`, `Json`) 
VALUES (5, 
'{ "field": "value with escaped ''single'' quotation marks"}');

Which gets turned into this:

ID    Json
-----------------------------------
5     { "field": "value with escaped 'single' quotation marks" }

You'd be better off leaving the php/javascript escape character (\) aside, and limit yourself to escaping quotations by either:

a) Using a parameterized query, this is the best way since you wont have to worry about what you have escaped or not:

$preparedStatement = 
  $db->prepare('INSERT INTO jsonTable (`ID`, `Json`) VALUES (5, :json');

$preparedStatement->execute(array(':json' => $json));

$rows = $preparedStatement->fetchAll();

b) Replacing single quotations by doubling them up:

$sqlEscaped = str_replace("'", "''", $json);

As for double quotation " marks: again you wont have to worry if you've used a parameterized query above, but if you plan on replacing characters in the SQL string they only become relevant if you are using double quotation marks within your INSERT / UPDATE script to delimit a string. ie:

INSERT INTO jsonTable (`ID`, `Json`) 
VALUES (5, 
/* Note how the string below begins and ends with (") */
"{ ""field"": ""value with escaped  \\""double\\"" quotation marks""}");

And:

ID    Json
-----------------------------------
5     { "field": "value with escaped \"double\" quotation marks" }

Sounds like you're just having a JavaScript issue. As a good practice, you should minimize the DOM manipulation actions by creating an array of the HTML items in order to append them with a single DOM method execution. Try this one:

var myArray = [{"text": "First"}, {"text": "Second"}, {"text": "\\ \' "}],
 items   = [];

for(nI = 0, nC = myArray.length; nI < nC; nI++) {
    items.push('<div><span>' + myArray[nI].text + '</span></div>');
}

$('#container').append(items.join(""));

Test it here