0

I have gone through a variety of questions that have been asked with the same thing in mind but it doesn't seem to be working for me.

I have a HTML textarea from which I collect the comments of a user and I'm storing them in a MySql database. So I want the special characters that are entered by the users to also be accepted without any error/exception and store them in the database as well.

I came across a solution for the newline character and I added this line of code which works well.

var comment = document.getElementById("commentArea").value;
comment = comment.replace(/\n/g, '<br />');

The other characters like &$%#( are all getting inserted without any problem except for quotes. I'm not sure what should I do to escape them. I tried comment.replace("\'","&39") for escaping single quotes but that doesn't seem to work.

How can I escape both single and double quotes? Thanks in advance.

EDIT: I'm using Jsp and Servlets for my application. Should I escape these characters in the servlet then?

Let me know the reason before downvoting.

Update: As suggested in the answers and comments, I used a prepared statement and passed the string using the setString() method. However, the problem still persists.

The code snippet I've used is:

String query = "insert into db_name (column1,column2,column3,column4) values("SomeValue1","SomeValue2",?,"SomeValue3")";
st=conn.prepareStatement(query);
st.setString(1,"String_from_TextArea");
int rows = st.executeUpdate();
Anjan Baradwaj
  • 1,219
  • 5
  • 27
  • 54
  • 1
    What driver you use? NodeJs, PHP? You must escape the value on the server side(not the client side). – Andrew Dec 05 '13 at 07:18
  • Forget about doing this on the client side. Within the server-side code you need to use mysqli_real_escape_string before writting the comment to the database. –  Dec 05 '13 at 07:18
  • This really should be done on the server-side, rather than the client side. For instance, if you are using PHP, then you can escape the string using PHP's addslashes: http://cl1.php.net/addslashes If this is not an option, why must it be done by the client? – Matt Dec 05 '13 at 07:18
  • 2
    Don't MySQL escape in Javascript on the client! You need to escape on the server while you are creating your queries, no sooner, no later! Read http://kunststube.net/escapism. – deceze Dec 05 '13 at 07:18
  • 1
    So I escape them in the Servlet? – Anjan Baradwaj Dec 05 '13 at 07:23
  • @deceze A very good article! – Anjan Baradwaj Dec 05 '13 at 07:35

2 Answers2

3

There is no need to escape anything in JavaScript, you'll not insert it directly but via some kind of server-side script (like PHP). That's place to perform such tasks. Especially that you can never trust anything that is done using JavaScript. User can modify it easily or even disable JavaScript. That shouldn't brake your site or database!

On server-side you can use prepared statements to safely insert data to database.

Elon Than
  • 9,603
  • 4
  • 27
  • 37
  • So I escape them in the Servlet? Using the replace() method? – Anjan Baradwaj Dec 05 '13 at 07:26
  • @AnjanBaradwaj No, don't use something that is not create for escaping. I don't know what server-side language you are using so I can't tell you what it will be exactly but probably there is some dedicated functions for that. – Elon Than Dec 05 '13 at 07:27
  • 1
    @AnjanBaradwaj Didn't see last edit. You should use Hibernate Query object with parameters and `set*` methods on it (if you use Hibernate to contact with database). – Elon Than Dec 05 '13 at 07:31
  • 1
    @AnjanBaradwaj *Don't* escape for "storing in a database" - *do* escape when using in output (e.g. HTML). Java/JDBC/Hibernate/etc [support *placeholders*](http://bobby-tables.com/java.html) just fine. – user2864740 Dec 05 '13 at 07:31
  • @ElonThan I tried using prepared statement and passed it using the `setString()` method. But that still doesn't seem to work. :| Can you think of any reason why it isn't working? – Anjan Baradwaj Dec 05 '13 at 10:10
  • @ElonThan Check it out. – Anjan Baradwaj Dec 05 '13 at 10:26
  • @AnjanBaradwaj You've got syntax error with you quotes. Also don't put directly variables in query when you are using prepared statements. Put all as `?` and then set its values. – Elon Than Dec 05 '13 at 10:30
  • @ElonThan But the other values are getting stored in the db and when I print `rows` it shows 1 row affected. – Anjan Baradwaj Dec 05 '13 at 10:34
  • @AnjanBaradwaj Your code will not execute. If it's executing, then you pasted it wrong. And doesn't matter if it works. Never put values directly in prepared statements. It break whole idea of it and makes it insecure. – Elon Than Dec 05 '13 at 10:37
  • 2
    @ElonThan with technologies such as **Node.js** around, especially considering this question wasn't tagged _php_ and was asked since Node.js has been around, stating that one should never escape characters for storage into a database via javascript, and the other points you've made regarding javascript aren't exactly true: one cannot disable server-side javascript from their computer browser - nor can a client modify server-side javascript. However, when tools such as prepared statements exist, even when using Node.js, escaping is indeed less predictable and recommended against. – Rik May 16 '15 at 17:36
  • This answer needs to be updated for node.js, as @Rik points out. – Dshiz Apr 09 '17 at 21:13
  • I asked a similar question and got a very detailed answer here: http://stackoverflow.com/questions/43312239/how-to-escape-mysql-special-characters-with-sockets-io-node-js-javascript/43312492#43312492 – Dshiz Apr 10 '17 at 17:38
0

If you want to URI encode the string, you can use encodeURIComponent() and decodeURIComponent() functions. This is a link to the documentation page of the function.

http://www.w3schools.com/jsref/jsref_encodeuricomponent.asp

Hope that helps.

Rakesh Gopal
  • 580
  • 5
  • 11