MYSQL insert string from html's text area with semi-colon...advise?

Question

Hey ya guys

This is in regards to PHP, Mysql and Html's textarea.

I want to dump a user's input from html's textarea into my mysql database. The situation is that if the user has semi-colons within the text itself, mysql will reject the insert command for obvious reasons. What I want to know is that are there any ways to force mysql to take in a user's input regardless of its syntax?

Much appreciated in advance :)

Answer 1

You need to escape any SQL special characters, such as semi-colon. mysql_real_escape_string might be useful.

Don't forget to escape user input if you send it back to a browser, otherwise you will be vulnerable to XSS (cross-site scripting) attacks. Of course, this time you'll need to escape HTML special characters, not SQL.

Answer 2

Any text fields should be escaped using the php function "mysql_real_escape_string" before being inserted into MySQL:

http://us2.php.net/manual/en/function.mysql-real-escape-string.php

Answer 3

The above answer is correct, but to add to what he said, you can use strip_tags() to sanitize output to avoid XSS.

Answer 4

I want to dump a user's input from html's textarea into my mysql database. The situation is that if the user has semi-colons within the text itself, mysql will reject the insert command for obvious reasons.

No, it won't because the semicolon is inside single quotes -- it doesn't require escaping. IE:

CREATE TABLE `new_table` (
  `id` int(11) NOT NULL,
  `col` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
)

INSERT INTO new_table VALUES (1, 'test;')

...will insert just fine.

The bigger issue is that allowing other characters leaves you open to scripting attacks, because people could insert a javascript scriptlet in the textarea that will execute on the page load.

Answer 5

Much appreciated guys. Worked perfectly.

A small follow up question, is there a big difference between textarea and traditional text boxes? The reason why I ask this is because it seems like when I retrieve data from the database, strings that contains colons etc, have no problems being displayed inside a textarea but would not display if it was a text box.