PHP: put each data into single quote(' ') while inserting in SQL

Question

I have a code that allows me to import a data from csv files to my database. However, there's a problem inserting the data since the values that i am going to insert has no single quote (''). I tried to echo out the sql query and i got this:

INSERT INTO bill_of_materials(allotment_code, category_name) VALUES(Site Electrical,Aldea Electrical Work ),(CM-S24,Assembly ),(CM-S4,Assembly ),(CM-S4,Assembly ),(CM-S8,Assembly ),(CM-S3,Assembly ),(CM-S3,Assembly ),(CM-S3,Assembly ),(CM-S8,Assembly ),(CM-S4,Assembly ),(CM-S24,Assembly ),(CM-S8,Assembly ),(CM-S23,Assembly ),(CM-S23,Assembly ),(CM-S23,Assembly ),(CM-S22,Assembly ),(CM-S22,Assembly ),(CM-S22,Assembly ),(CM-S24,Assembly ),(CM-D2,Assembly ),(CM-D18,Assembly ),(CM-D18,Assembly ),(CM-D14,Assembly ),(CM-D14,Assembly ),(CM-D14,Assembly ),(CM-D20,Assembly ),(CM-D20,Assembly ),(CM-D20,Assembly ),(CM-D13,Assembly ),(CM-D13,Assembly ),(CM-D10,Assembly ),(CM-D18,Assembly ),(CM-D10,Assembly ),(CM-D13,Assembly ),(CM-D2,Assembly ),(CM-D2,Assembly ),(CM-D21,Assembly ),(CM-D21,Assembly ),(CM-D21,Assembly ),(CM-D11,Assembly ),(CM-D11,Assembly ),(CM-D11,Assembly ),(CM-D12,Assembly ),(CM-D12,Assembly ),(CM-D12,Assembly ),(CM-D10,Assembly ),(CM-D19,Assembly ),(CM-D17,Assembly ),(CM-D17,Assembly ),(CM-D19,Assembly ),(CM-D16,Assembly ),(CM-D15,Assembly ),(CM-D15,Assembly ),(CM-D15,Assembly ),(CM-D17,Assembly ),(CM-D19,Assembly ),(CM-D1,Assembly ),(CM-D1,Assembly ),(CM-D16,Assembly ),(CM-D16,Assembly ),(CM-D1,Assembly ),(CM-S17,Assembly ),(CM-S18,Assembly ),(CM-S18,Assembly ),(CM-D26,Assembly ),(CM-D26,Assembly ),(CM-D26,Assembly ),(CM-S16,Assembly ),(CM-S16,Assembly ),(CM-D4,Assembly ),(CM-D4,Assembly ),(CM-D3,Assembly ),(CM-D3,Assembly ),(CM-D25,Assembly ),(CM-S17,Assembly ),(CM-S21,Assembly ),(CM-D9,Assembly ),(CM-D9,Assembly ),(CM-D9,Assembly ),(CM-S17,Assembly ),(CM-D8,Assembly ),(CM-D8,Assembly ),(CM-D8,Assembly ),(CM-S12,Assembly ),(CM-S12,Assembly ),(CM-S12,Assembly ),(CM-D25,Assembly ),(CM-D25,Assembly ),(CM-D3,Assembly ),(CM-D5,Assembly ),(CM-S13,Assembly ),(CM-S13,Assembly ),(CM-S13,Assembly ),(CM-S19,Assembly ),(CM-S19,Assembly ),(CM-S19,Assembly ),(CM-S20,Assembly ),(CM-S20,Assembly ),(CM-S20,Assembly ),(CM-D7,Assembly ),(CM-D7,Assembly ),(CM-D7,Assembly ),(CM-S18,Assembly ),(CM-D5,Assembly ),(CM-S21,Assembly ),(CM-D22,Assembly ),(CM-D22,Assembly ),(CM-D22,Assembly ),(CM-S15,Assembly ),(CM-S15,Assembly ),(CM-S15,Assembly ),(CM-S11,Assembly ),(CM-S11,Assembly ),(CM-S11,Assembly ),(CM-D23,Assembly ),(CM-S21,Assembly ),(CM-D4,Assembly ),(CM-D5,Assembly ),(CM-D24,Assembly ),(CM-D24,Assembly ),(CM-D23,Assembly ),(CM-D23,Assembly ),(CM-D6,Assembly ),(CM-S14,Assembly )

i have my PHP codes below:

<form method="post" enctype="multipart/form-data">
<input type="file" name="csv" value="" />
<input type="submit" name="submit" value="Save" /></form>

<?php

$new_conn = mysqli_connect('localhost', 'root', '153624123', 'db_lazvasmunhomesinc');

if(isset($_FILES['csv']['tmp_name'])) {
    $data = $_FILES['csv']['tmp_name'];
    $handle = fopen($data, "r");
    $test = file_get_contents($data);

    if(!empty($data)) {

        if ($handle) {
            $counter = 0;
            //instead of executing query one by one,
            //let us prepare 1 SQL query that will insert all values from the batch
            $sql ="INSERT INTO bill_of_materials(allotment_code, category_name) VALUES";
            while (($line = fgets($handle)) !== false) {
              $sql .="($line),";
              $counter++;
            }
            $sql = substr($sql, 0, strlen($sql) - 1);
             if (mysqli_query($new_conn, $sql) === TRUE) {
                echo 'success';
            } else {
                echo $sql;
             }
            fclose($handle);
        } else {  
        } 
        //unlink CSV file once already imported to DB to clear directory
        unlink($data);
    } else
        echo '<script>alert("EMPTY!");</script>';
}
?>

I tried to use another code which is:

if(!empty($data)) {

if ($handle) {
    $counter = 0;
    //instead of executing query one by one,
    //let us prepare 1 SQL query that will insert all values from the batch
    $sql ="INSERT INTO bill_of_materials(allotment_code, category_name) VALUES";
    while (($line = fgets($handle)) !== false) {
      $sql .= "('".implode("', '", explode(",", $line))."'),";
      $counter++;
    }
    $sql = substr($sql, 0, strlen($sql) - 1);
     if (mysqli_query($new_conn, $sql) === TRUE) {
        echo 'success';
    } else {
        echo $sql;
     }
    fclose($handle);
}

it works but with a little problem. since it will split the data after comma, some of the data from CSV FILE has a comma which will result to this:

INSERT INTO bill_of_materials(allotment_code, category_name) VALUES('"OH:Fuel', ' Oil and Accessories"', 'Avanza Gray-OBNO-1782 ')

The data from the csv file is: oh: Fuel, Oil and Accessories and Avanza Gray-OBNO-1782

the expected output should be:

INSERT INTO bill_of_materials(allotment_code, category_name) VALUES('"OH:Fuel Oil and Accessories"', 'Avanza Gray-OBNO-1782')

Another code that i tried is this:

    $sql ="INSERT INTO bill_of_materials(allotment_code, category_name) VALUES";
    while (($line = fgets($handle)) !== false) {
      $sql .= "('{$line[0]}', '{$data[1]}'),";
      $counter++;
    }

but the inserted data is incorrect and it results to this:

my csv file looks like this:

some of the data from my csv has a comma.

the data is not inside the single quote. I hope you can help me. thanks in advance.

Possible duplicate of [How can I prevent SQL injection in PHP?](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) — chris85, Aug 02 '17 at 04:09
^ that is the answer, although not the topic you are asking about. — chris85, Aug 02 '17 at 04:10

Dan · Accepted Answer · 2017-08-02T05:03:58.963

1

Since you're using CSV files you can parse every line in your while loop with str_getcsv():

$values = str_getcsv('"OH:Fuel, Oil and Accessoires",Avanza Gray-OBNO-1779');

... and insert the values back into the sql statement:

$sql .= "('" . array_shift($values) . "', '" . (isset($values) ? implode(",", $values) : "") . "'),";

And dont forget to apply rtrim() to the sql statement, so the last comma gets removed:

$sql = rtrim($sql, ",");

You can also use fgetcsv() to read and parse the CSV file line by line as an array:

Example:

$file = __DIR__ . "/data.csv";
$sql = "INSERT INTO `bill_of_materials` (`allotment_code`, `category_name`) VALUES ";

$handle = fopen($file, "r");

while ($data = fgetcsv($handle)) {
    $sql .= "('" . array_shift($data) . "', '" . (isset($data) ? implode(",", $data) : "") . "'),";
}

fclose($handle);

$sql = rtrim($sql, ",");

edited Aug 02 '17 at 05:03

answered Aug 02 '17 at 03:29

Dan

5,140
2
15
30

this actually works but with a little problem.. some of my data inside the csv file has a comma which will also split as another data. how can i fix it sir? – Nibiru Nibiru Aug 02 '17 at 03:45
Is this data surrounded by single or double quotes? Can you post one of this specific lines? – Dan Aug 02 '17 at 03:51
the data is from the csv files which has no single quote or double quotes. i used your code: $sql .= "('".implode("', '", explode(",", $line))."'),"; and it actually work. there's just a little problem.. there's some data that has a comma like for example: OH: Fuel, Oil and Gas. since there's a comma, PHP will split it as another data. – Nibiru Nibiru Aug 02 '17 at 03:53
Okay, you read one line per iteration. Does some of this lines include more than one comma which is used to *split* values by each other? Because it's technically not possible in csv files _not_ to surround commas with quotes which must not get splitted. – Dan Aug 02 '17 at 03:56
some of the data from csv file has a comma sir. which is the problem – Nibiru Nibiru Aug 02 '17 at 03:57
Yes, the values of **one line** are separated by commas and the commas are being used to split the values. And in your code you read the file line by line and split the values. I don't understand the problem. – Dan Aug 02 '17 at 04:00
I'm sorry about that sir.. I'll rephrase the problem.. it goes like this. In my csv file, i have a tons of data. let's say i have a data of: **OH: Gas, Oil and Water** and the other data is **OH: Water and Oil** notice that the first data has a **comma** after the word **Gas** so it will split as well.. in the query, it will become **VALUES('OH: Gas', 'Oil and Water', 'OH: Water and Oil')** – Nibiru Nibiru Aug 02 '17 at 04:28
I adjusted my answer with a new solution matching your requirements. Maybe this will help you to solve the problem. – Dan Aug 02 '17 at 04:31
thanks for sharing your new answer sir. but unfortunately, it only inserts the first letter for the **allotment_code** and inserted a **:** sign. @Spingolini – Nibiru Nibiru Aug 02 '17 at 04:42
Can you add a few lines of your CSV file to your question post so everyone can participate? – Dan Aug 02 '17 at 04:44
i updated my question sir with photos for more information of my problem. thanks for helping me. – Nibiru Nibiru Aug 02 '17 at 04:50
The first solution should do it. – Dan Aug 02 '17 at 05:04
With the last code in you updated post you get every time you loop just one char from the string `$line` and one char from the `$data` string, because they're not arrays. – Dan Aug 02 '17 at 14:34

score 0 · Answer 2 · answered Aug 02 '17 at 03:30

0

Add the single quotes:

$sql .="('$line'),";

answered Aug 02 '17 at 03:30

meda

45,103
14
92
122

1

This causes something like this `INSERT INTO (col1, col2) VALUES ('value1, value2')` and would end in an mysql error. – Dan Aug 02 '17 at 03:34
@meda thanks for your answer sir.. but unfortunately, this won't work. – Nibiru Nibiru Aug 02 '17 at 03:34

PHP: put each data into single quote(' ') while inserting in SQL

2 Answers2