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