turning commas into html entities? is it possible?

Question

I'm wondering if it's possible to take a block of text, grab all the commas and convert them into it's corresponding html entity. Something along the lines of what htmlentities($var, ENTQUOTES) does, but for commas.

It could be that I'm overcomplicating the issue. What I'm trying to accomplish is getting a textarea value from a user that may include commas and thus messing up the following code:

$sql = "INSERT INTO blog (title, date, author, article, category) 
            VALUES (".$title.", ".$date.", ".$author.", ".$article.", ".$category.")";

Having the commas in there messes up the query. I guess I could figure out some other way of doing the insert. (I'm a n00b). Any help is greatly appreciated. Thanks in advance.

*edit: Thanks for the quick replies! The code is protected against injection attacks automatically (codeigniter framework).

The error message is this:

A Database Error Occurred Error Number: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'a test post, 07/22/2011, 1, '
This is a test post with all sorts of cool' at line 2

INSERT INTO blog (title, date, author, article, category) VALUES (this is a test post, 07/22/2011, 1, '
This is a test post with all sorts of cool things typed in here as if to be like a real article and everything, but it's not, it's fake. The ultimate blah blah blah repeat:
', news)

I assumed it was because of the commas although I can see now that if they are in the quotes it shouldn't matter. I guess I have a different issue here. Thanks for pointing that out.

*EDIT #2: I used codeigniter's $this->db->escape() on all of the variables and it worked. It wasn't what I thought it was. Sorry for the confusion and thanks for all the advice. The sql injection links have been bookmarked.

it doesn't make sense that commas should give you this trouble -- can you give us a value for `$article` which gives you this problem? — Dereleased, Jul 22 '11 at 21:03
I don't use Code Igniter, but I don't think your query will be protected against SQL injection - at least not the way it is currently formatted. See [Does Code Igniter automatically prevent SQL injection?](http://stackoverflow.com/questions/1615792/does-code-igniter-automatically-prevent-sql-injection) and [SQL Injection and Codeigniter](http://stackoverflow.com/questions/3797613/sql-injection-and-codeigniter) -- don't just assume that your framework will protect you. — Mike, Jul 22 '11 at 21:21

score 3 · Answer 1 · answered Jul 22 '11 at 21:01

3

Holy SQL Injection, Batman!

You need to sanitize your input, or use parametrized queries (see PDO). What happens when someone enters a title of "; DROP TABLE blog; --?

Have a read of the PHP documentation on this topic and it will become immediately apparent that you are asking the wrong question. If you use parametrized queries, you don't need to escape anything, and you eliminate an attack vector.

answered Jul 22 '11 at 21:01

cdhowie

158,093
24
286
300

well, to be perfectly clear, if the underlying mechanism is `mysql_query` he'll be OK since `mysql_query` does not allow sending multiple queries at once. HOWEVER, you are, of course, completely correct. – Dereleased Jul 22 '11 at 21:08

score 1 · Accepted Answer · answered Jul 22 '11 at 21:39

If you're using CodeIgniter, you still have to make sure you escape everything that goes in your queries. You'll have to add $this->db->escape() around every variable:

$sql = "INSERT INTO blog (title, date, author, article, category) 
        VALUES (".$this->db->escape($title).", ".$this->db->escape($date).", ".$this->db->escape($author).", ".$this->db->escape($article).", ".$this->db->escape($category).")";

CodeIgniter does automatically escape everything when you use Active Records. You could use something like this:

$data = array(
   'title' => $title,
   'date' => $date,
   'author' => $author,
   'article' => $article,
   'category' => $category
);

$this->db->insert('blog', $data);

score 0 · Answer 3 · answered Jul 22 '11 at 21:01

You have to escape ANY string data you're inserting into an SQL query, and surround it with quotes. Simply changing , to &#x2c (or equivalent) is NOT going to keep other things like quotes from breaking the query. In other words, go read up about Little Bobby Tables

score 0 · Answer 4 · answered Jul 22 '11 at 21:05

You can remove commas by function replace

$date = str_replace(',', ' ', $date);

Or add quotes to query:

$sql = "INSERT INTO blog (title, date, author, article, category) 
            VALUES ('".$title."', '".$date."', '".$author."', '".$this->db->escape($article)."', '".$category."')";

But don't forget to use mysql_escape_string function to secure the variables because of mysql injection.

$title = mysql_escape_string($title);

score 0 · Answer 5 · edited May 23 '17 at 09:58

Please don't do what you are doing! You are leaving yourself wide open to SQL injection. Look into using prepared statements instead, perhaps with PDO. Here's one way of doing it, but be sure to read the documentation too:

$dbh = new PDO('mysql:host=localhost;dbname=test', $user, $pass);

$sql = "INSERT INTO blog (title, date, author, article, category) " .
       "VALUES (?, ?, ?, ?, ?)"

$stmt = $dbh->prepare();
$stmt->execute( array($title, $date, $author, $article, $category) );

For more on SQL injection, see Bill Karwin's Sql Injection Myths and Fallacies talk and slides. Also see his answers to What is SQL injection?.

I also provided an example of SQL injection in PHP with the mysql API here.

turning commas into html entities? is it possible?

5 Answers5