0

I am trying to insert the text (UTF-8 Encoded) from an excel file that has 48 rows into a MySQL table. Only 38 of the rows get inserted. Some of the rows do have special characters like "/", "?", ":", ",", ".", "()", "'", """, "!". 

foreach ($ret as $rec) {
    if($count == 0) {
        $count++;
        continue;
    }

    $insert = "INSERT INTO chall (description, time, coin, mode, kids, teens, adults, theme_id, type_id) VALUES ('{$rec['A']}', '{$rec['B']}', '{$rec['C']}', '{$rec['D']}', '{$rec['E']}', '{$rec['F']}', '{$rec['G']}', '{$_REQUEST['theme_id']}', 1)";
    $ins_query = mysql_query($insert) or mysql_error();
    if(0)
    {
        die("Error! Can Not Upload Data...");
    }
}

Now below is an example of text that has succesfuly inserted into the db: Pretend to for E! News. This is an example. Ask two people to give you a quick answer to this question: "Which of these are not colors​—black, brown, gum, blue, Jim, blue, or green?"

Below is an example that did not get inserted into the db: Zig-a-zig-ah! Get a group of buds to reenact this test and remake a video by the Spice Girls in a public setting. Get strangers to join in on the fun!

I have researched as much as I could and have not found any answers. 10 of the rows are not inserting into the db table. Is there anything that I am missing? Thanks a lot for the help.

YakovL
  • 7,557
  • 12
  • 62
  • 102
Jesse T
  • 1
  • PHPExcel won't automagically escape the data in a cell for your database statement; there's a starting point – Mark Baker Aug 04 '16 at 14:01

2 Answers2

0

This is incorrect:

$ins_query=  mysql_query($insert) or mysql_error();
                                   ^^^^^^^^^^^^^^

mysql_error() returns a string. That means if mysql_query() fails (and returns a boolean false, you assign the error string to $ins_query. That means $ins_string cannot ever be a boolean false, which is what you should be testing for to detect a failure.

And then

if(0)

will always evaluate to false and not execute the die anyways, so there's NO way for you to detect when an error occurred.

You should have

 $result = mysql_query(...);
 if($result === false) {
     die(mysql_error());
 }

And note that you are vulnerable to sql injection attacks.

Marc B
  • 356,200
  • 43
  • 426
  • 500
  • Thanks a bunch for the help. Apparently the issue is the ' character. Im guessing I have to escape that character, but not sure how to implement that fix, can you help with that? – Jesse T Aug 04 '16 at 14:30
0

You should fix your mysql_error as @Marc recommended

Also you should use the fgetcsv function to parse your file and check if you have the right number of fields on each line.

Because of this

$_REQUEST['theme_id']

a malicious user can inject whatever he wants in your query (sql injection).

Never trust what the user send

Sylwit
  • 1,497
  • 1
  • 11
  • 20
  • doesn't matter if the data doesn't come from "outside". OP is directly stuffing text into a query. you can trivially inject YOURSELF. – Marc B Aug 04 '16 at 14:45
  • `fgetcsv()` is only appropriate if the file is a csv file, OP has said it's an xlsx (OfficeOpenXML format) Excel file, so fgetcsv() isn't much use here – Mark Baker Aug 04 '16 at 15:40
  • Most of people are talking about xlsx while it's simply CSV. – Sylwit Aug 04 '16 at 16:45
  • How do you know that it's simply csv in this case? If you need clarification, then ask in the comments, but OP specifically says an xlsx file, and making assumptions that they are wrong is rarely a good idea – Mark Baker Aug 04 '16 at 19:13
  • It is an xlsx file. After changing he code im able to see that the error is the ' symbol. I am trying to correctly escape '{$rec['A']}'. I tried using mysql_real_escape_string() on it but it escapes the 2 single quotes which causes issues when inserting. I then tried to implement the function with the single quotes outside of the function where what is left is "'".mysql_real_escape_string({$rec['A']})."'" but then then the single quotes are not being set as part of the escaped string. Im kind of confused as to how to do this. Any help would be greatly appreciated. – Jesse T Aug 05 '16 at 05:55