-3

I am new to php. I am facing problem while inserting record into database that contains ' character. I am traying to insert this record in database Men's Oxfords Bates High Gloss But it giving an error

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 's Oxfords Bates High Gloss.

My isert query is :

 $iq="INSERT INTO `TABLE 1`(`id`, `title`, `description`, `condition`, `price`, `availability`, `link`, `image_link`, `mpn`, `brand`, `google_product_category`, `Gender`, `Age Group`, `size`, `Color`, `material`, `shipping_weight`, `product_type`, `adwords_labels`)VALUES ('".$cols[$i][0]."','".$cols[$i][1]."','".$cols[$i][2]."','".$cols[$i][3]."','".$cols[$i][4]."','".$cols[$i][5]."','".$cols[$i][6]."','".$cols[$i][7]."','".$cols[$i][8]."','".$cols[$i][9]."','".$cols[$i][10]."','".$cols[$i][11]."','".$cols[$i][12]."','".$cols[$i][13]."','".$cols[$i][14]."','".$cols[$i][15]."','".$cols[$i][16]."','".$cols[$i][17]."','".$cols[$i][18]."')";

where $cols[][] is multinational array that stores tab serrated values from .text file.

sachin_ware
  • 1,411
  • 6
  • 20
  • 31
  • You need to escape the single quote. The best way to do so is by using the `mysql_real_escape_string()` function, (or the mysqli version) –  Nov 12 '13 at 08:23
  • 1
    Please don't participate the development of public projects with that knowledge. – Hilmi Erdem KEREN Nov 12 '13 at 08:32
  • If you're trying to load data into MySQL from a TSV file you might find it easier, and faster, to use `LOAD DATA INFILE` ([MySQL reference](http://dev.mysql.com/doc/refman/5.6/en/load-data.html)) –  Nov 12 '13 at 08:46
  • You're using `INSERT INTO TABLE 1` --- that alone will produce an error. Table names cannot have spaces. You probably meant to use `INSERT INTO TABLE_1` with an underscore(?) Plus `TABLE` is a [**reserved word**](http://dev.mysql.com/doc/refman/5.5/en/reserved-words.html) which, if used must be wrapped with backticks. – Funk Forty Niner Nov 12 '13 at 13:15
  • @Fred-ii-, Great finding. I also had a look at that before. But when i read the error message `.....version for the right syntax to use near 's Oxfords Bates High Gloss` i jumped there. – Shankar Damodaran Nov 12 '13 at 13:25
  • I think it's a mix of one or more problems @ShankarDamodaran but that one stood out too much. – Funk Forty Niner Nov 12 '13 at 13:27

1 Answers1

2

You should use a parameterized query to avoid not only issues like this, but also SQL injection in general.

See How can I prevent SQL injection in PHP? or parameters in MySQLi, for example.

Community
  • 1
  • 1
Christian Specht
  • 33,837
  • 14
  • 123
  • 176
  • Strictly speaking, a link to existing answer should go into closeveote reason, not brand new answer. But nobody cares. – Your Common Sense Nov 12 '13 at 08:25
  • I'd agree with you if my answer would just say "see this link for a solution". But in this case, my actual answer "use a parameterized query to avoid SQL injection" isn't mentioned in the link at all - I just use the link because that answer has example code I'd need to create again. – Christian Specht Nov 12 '13 at 08:29
  • neither this question has anything to do with injections. But, as I said, nobody follows this rule of closing questions, so, I am not question your excusable desire to get an occasional rep. Everyone does it. – Your Common Sense Nov 12 '13 at 08:32