1

I want to use this mysql select to get local file data.

SELECT
  body
FROM posts
WHERE
  id in (load_file("/Users/abc/sql/ids"));

What in /Users/abc/sql/ids:

1,2,3

It didn't cause error but didn't get result.

How to use it in this case?

iooi
  • 453
  • 2
  • 10
  • 23
  • 1
    Load_file returns the file contents as one blob, not as comma-separated values. – CodeCaster Dec 25 '20 at 08:33
  • @CodeCaster It doesn't matter - the value datatype will be altered implicitly. – Akina Dec 25 '20 at 09:52
  • 1
    @Akina my point was that it's one piece of data, not comma-separated, their code becomes `WHERE id IN ('1,2,3')`. – CodeCaster Dec 25 '20 at 11:04
  • 1
    @CodeCaster +100500. This is the case when FIND_IN_SET is used (which, by the way, does not have an inverse function). – Akina Dec 25 '20 at 11:10

2 Answers2

2

This will work (in MySQL 8.0 an above):

set @a = (select cast(load_file('/temp/abc.txt') as char));
select @a;

set @b = concat('select i from integers where i in (',@a,')');
select @b;

prepare st from @b;
execute st;

In this example I do select from a different table, but in the end the SQL statement that you want to execute should be prepared in @b. (Changing that is easy )

See: CAST, LOAD_FILE, PREPARE

EDIT (about the SQL injection vulnerability):

If the abc.txt file contains something like this:

1,2;select * from integers;

above will fail with an error ERROR 1243 (HY000): Unknown prepared statement handler (st) given to EXECUTE

This is because select i from integers where i in (1,2;select * from integers;) is not a valid SQL statement.

So, conclusion, yes there might be a possibility for SQL injection, but it is hard to find a way to do it. This will not make above statemens free from SQL injection!.

Luuk
  • 12,245
  • 5
  • 22
  • 33
  • 1
    And now you have an SQL injection vulnerability by file. – CodeCaster Dec 25 '20 at 08:48
  • @CodeCaster: The vulnerability should be solved, an example using PHP is [here](https://stackoverflow.com/questions/3703180/a-prepared-statement-where-in-query-and-sorting-with-mysql), but i do not know how to do it in MySQL without using anything externally... – Luuk Dec 25 '20 at 09:02
  • Which mysql version are you using? I got this error when run the `prepare` line: `ERROR 1064 (42000): 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 'NULL' at line 1` at 5.7. – iooi Dec 25 '20 at 09:10
  • 1
    @iooi: MySQL 8.0. "MySQL 8.0 provides support for server-side prepared statements." (from: [PREPARE](https://dev.mysql.com/doc/refman/8.0/en/sql-prepared-statements.html)) – Luuk Dec 25 '20 at 09:33
1
SELECT body
FROM posts
WHERE FIND_IN_SET(id, load_file("/Users/abc/sql/ids"));

This may work even with multiline text file. If:

  • all rows except last one are finalized with a comma (not 1,2,3 but 1,2,3,) - if not then last value will be ignored;
  • all rows except first one are started from a comma (not 11,22,33 but ,11,22,33) - if not then first value will be ignored.
Akina
  • 39,301
  • 5
  • 14
  • 25
  • The explanation about the multiline text is confusing. But it makes clear that attention is needed about the format of the file which is read. It should not start, or end, with a comma, and preferably not be multiline. – Luuk Dec 25 '20 at 10:13
  • 1
    @Luuk *The explanation about the multiline text is confusing* Why? you may test... FIND_IN_SET uses `,` as values delimiter. Any other byte (including `\n`) is treated as value char. I.e. the file `1,2,3\n4,5,6` contains not 6 values (like we see on the screen in a viewer) but 5 values, and 3rd value is `3\n4`, not values `3` and `4`. – Akina Dec 25 '20 at 10:23
  • Yes, that why i wrote "preferably not be multiline". – Luuk Dec 25 '20 at 10:26
  • It can't get data by MySQL 5.7. – iooi Dec 25 '20 at 13:00