1

I use parametric queries for normal insert/updates for security.
How do I do that for queries like this:

LOAD DATA INFILE '/filepath' INTO TABLE mytable

In my case, the path to the file would be different everytime (for different requests). Is it fine to proceed like this (since I am not getting any data from outside, the file is from the server itself):

 path = /filepath
 "LOAD DATA INFILE" + path + "INTO TABLE mytable"
Mr.President
  • 163
  • 1
  • 9
  • Are you asking for a permission? – Paul Spiegel Jan 27 '20 at 14:40
  • @PaulSpiegel Thanks for replying, Sir. I have all the permissions. As everything is done internally, I thought I could format the query using string concatenation, and do away with parametric queries like for inserts/updates. Just wanted to confirm the same. – Mr.President Jan 27 '20 at 14:45
  • But why? Because `LOAD DATA` is not allowed in prepared statements? – Paul Spiegel Jan 27 '20 at 14:47
  • @PaulSpiegel Am using Python MySQLdb for this. – Mr.President Jan 27 '20 at 14:49
  • So what exactly is the problem? Why don't you use "parametric queries" like you do for inserts and updates? – Paul Spiegel Jan 27 '20 at 14:50
  • @PaulSpiegel `1.`I was wondering that if I do not get any data to insert from outside, would I still need to be cautious about sql injection. `2.` I have no idea about how to write a parametric query for this. – Mr.President Jan 27 '20 at 14:53
  • It wouldn't be different as for INSERT or UPDATE - You replace the string (path) with a placeholder: `LOAD DATA INFILE ? INTO TABLE mytable`. – Paul Spiegel Jan 27 '20 at 14:56

2 Answers2

1

Since LOAD DATA is not listed in SQL Syntax Allowed in Prepared Statements you can't prepare something like

LOAD DATA INFILE ? INTO TABLE mytable

But SET is listed. So a workaround could be to prepare and execute

SET @filepath = ?

And then execute

LOAD DATA INFILE @filepath INTO TABLE mytable

Update

In Python with MySQLdb the following query should work

LOAD DATA INFILE %s INTO TABLE mytable

since no prepared statement is used.

Paul Spiegel
  • 30,925
  • 5
  • 44
  • 53
  • I don't think the OP is asking about "prepared" statements, but rather "parameterized" statements which are handled client-side. See [this](https://stackoverflow.com/a/52020100/10682164) answer which suggests prepared statements aren't even possible with MySQLdb. – totalhack Jan 27 '20 at 15:08
  • @totalhack - If no prepared statements are used, then I see no problem at all. – Paul Spiegel Jan 27 '20 at 15:16
1

To answer your "is it fine to proceed like this" question, your example code will fail because the resulting query will be missing quotes around the filename. If you changed it to the following it could run, but is still a bad idea IMO:

 path = "/filepath"
 sql = "LOAD DATA INFILE '" + path + "' INTO TABLE mytable" # note the single quotes

While you may not be accepting outside input today, code has a way of sticking around and getting reused/copied, so you should use the API in a way that will escape your parameters:

sql = "LOAD DATA INFILE %s INTO TABLE mytable"
cursor.execute(sql, (path,))

And don't forget to commit if autocommit is not enabled.

totalhack
  • 2,298
  • 17
  • 23
  • Thanks a lot, Sir, for clearing doubts about using string concatenation. Sticking to your advice, would opt for parametric query in this case too. That autocommit remark should be in bold . – Mr.President Jan 27 '20 at 15:40