1

I'm scraping data from third party sites using an html parser and then inserting the info that I need into MySQL. It just so happens that a lot of the info contains apostrophes in it. The trouble is that for the SQL query as follows:

INSERT INTO table (column) VALUE ('".$value."')

If the $variable has an apostrophe in it, the query breaks. There is simply too much info for me to manually insert it all. It would be entirely too time consuming. Any suggestions for me to make this query work?

thumbtackthief
  • 5,585
  • 6
  • 36
  • 72
Lance
  • 4,488
  • 16
  • 49
  • 85

2 Answers2

4

This is a classic SQL injection flaw. If the value of $value is controlled by the user, they can basically do anything right now. The quote in the scraped string breaks out of the quoted environment within the SQL string, and thus allows for arbitrary SQL commands to be executed.

The short answer is, use mysql_real_escape_string.

Of course, there's the obligatory "mysql_* is deprecated, use mysqli or PDO" part of this too.

slugonamission
  • 9,328
  • 29
  • 39
  • Sorry. I'm a noob. But, what is PDO? I know about sql injections, but only wrap the value of something that is being used in a query in mysql_real_escape_string() when a user of my site is using my site. Obviously, I'm not going to do something harmful to my own DB. – Lance Oct 11 '12 at 08:51
  • http://php.net/manual/en/book.pdo.php. Especially look at the `prepare` and `execute` sections. It allows you to build up a query with placeholders, then bind these to values when executing the query. Since they are done this way, SQL injection doesn't become an issue. – slugonamission Oct 11 '12 at 08:52
  • Thanks a bunch. I'll look into that. Does it increase execution time? – Lance Oct 11 '12 at 08:53
  • Erm, maybe? In any case, not significantly. If you're worried about execution time though, I have to ask why you're using PHP... – slugonamission Oct 11 '12 at 08:58
  • Ehhh. Not particularly concerned about execution time. Just wondering. – Lance Oct 11 '12 at 09:02
  • In any case, it won't have a significant performance impact. Test it ;) – slugonamission Oct 11 '12 at 09:04
1

So in other words, your query is vulnerable with SQL Injection. Better use PDO or MySQLi Extension since you tagged `PHP.

Take time to read on this article: Best way to prevent SQL injection in PHP?

Community
  • 1
  • 1
John Woo
  • 238,432
  • 61
  • 456
  • 464