Upload csv file to mysql database error in sql syntax

Question

I have a piece of javascript that fetches a file and generates a name. This data gets put into a FormData object. This gets send by ajax to the server. See the javascript below:

function create_form_data(option_file_name){

  var file = $('#'+option_file_name.id).prop('files')[0];

  var form_data = new FormData(); 

  form_data.append('file', file);

  form_data.append('tblname', option_file_name.id.slice(12));

  send_ajax_request(form_data);



}



function send_ajax_request(pdata){

  $.ajax({

    url: "set_points_data.php",

    data: pdata,

    contentType: false,

    processData: false,

    dataType: "text",

    type: 'post',

    success: function (data) {

      console.log(data);

    },

    error: function(a,b,c){

      console.log(a);

      console.log(b);

      console.log(c);

    }

  });

}

Now in the set_points_data.php i do the following:

include 'data.php';



if(isset($_FILES)){

    $data = $_FILES["file"]["name"];

    $db_name = mysqli_escape_string($conn, $_POST["tblname"]);

    return_ajax(create_table($conn, $db_name, $data));

}else{

    return_ajax("error");

}



function create_table($conn, $name, $data){

    send_sql($conn, "DROP TABLE IF EXISTS domestic_data_strings.".$name."");

    send_sql($conn, "CREATE TABLE $name (options varchar(255));");

    send_sql($conn, "LOAD DATA LOCAL INFILE $data INTO TABLE $name FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\n' (options)");



    return $name;



}

But I keep getting the following error :

Error: LOAD DATA LOCAL INFILE column_age.csv INTO TABLE age FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '
' (options)

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 'column_age.csv INTO TABLE age FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' at line 1

I have no idea what is going wrong here :( I tried different

.csv

files, but that didnt make a difference. I checked if the table existed with the correct column what is also correct. Any help is greatly appreciated!

If more context is required please let me know!

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Answer 1

I'm not sure but I think you have to surround the file name in your query with single quotes: send_sql($conn, "LOAD DATA LOCAL INFILE '" . $data . "' INTO TABLE $name FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\n' (options)");

Upload csv file to mysql database error in sql syntax

Sign up