LOAD DATA LOAD FILE on webserver

[horrridd]

Describe the bug
After uploading lwt to webhoster the save/save and open/check text functionalities return an error.

Fatal Error in SQL Query: LOAD DATA LOCAL INFILE '/storage/ssd2/332/18504332/tmp/tmpti.txt' INTO TABLE temptextitems FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n' (@w,@c) set TiSeID = @b, TiCount = (@d:=@d+CHAR_LENGTH(@c))+1-CHAR_LENGTH(@c), TiOrder = if(@c like "%\r",case when (@c:=REPLACE(@c,"\r","")) is NULL then NULL when (@b:=@b+1) is NULL then NULL when @d:= @e is NULL then NULL else @a:=@a+1 end, @a:=@a+1), TiText = @c,TiWordCount=@w

Error Code & Message: [1047] Unsupported 'LOAD DATA LOCAL INFILE' command

Backtrace:

#0  do_mysqli_query(LOAD DATA LOCAL INFILE '/storage/ssd2/332/18504332/tmp/tmpti.txt' INTO TABLE temptextitems FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n' (@w,@c) set TiSeID = @b, TiCount = (@d:=@d+CHAR_LENGTH(@c))+1-CHAR_LENGTH(@c), TiOrder = if(@c like "%\r",case when (@c:=REPLACE(@c,"\r","")) is NULL then NULL when (@b:=@b+1) is NULL then NULL when @d:= @e is NULL then NULL else @a:=@a+1 end, @a:=@a+1), TiText = @c,TiWordCount=@w) called at [/storage/ssd2/332/18504332/public_html/inc/database_connect.php:764]
#1  splitCheckText(test text upload, 1, 10) called at [/storage/ssd2/332/18504332/public_html/edit_texts.php:346]

Expected behavior
Page with added text available, ready to read.

Desktop:

• OS: Windows 7 SP1
• Browser: Chrome 98.0.4758.102
• PHP Version: 8.0.7
• MySQL Version: 10.5.12-MariaDB

Additional context
It worked with original lwt version (same hoster, same setting up proces). After opening text after this error, text doesn't appear (blank space in place of text/dictionaries).
After installing the LWT demo database there's an error as well

*** Error: Demo Database NOT restored - 418 queries - 23 successful (17/0 tables dropped/created, 0 records added), 395 failed. ***

Again it seems like texts don't upload. Languages and audio files do.

[HugoFara]

Hello,

Sorry for the delay, I won't be able to contribute much during the next couple of weeks.

Your problem seems to be related to a simple permission issue. Here are my suggestions, ideas 1 and 2 are for records only (as they do not apply to you) :

1. Double check the installation procedure (docs/install.md). Particularly, don't forget to allow read/write (the sudo chmod -R 755 /var/www/html/lwt part on Linux).
2. Check your server authorizations. You might have a setting saying something "allow local files imports" or "file_uploads".
3. Check your database users authorizations. Could you run MySQL, execute SHOW GRANTS; and paste the result?
4. Run MySQL and check that SHOW VARIABLES LIKE ‘%local_infile%’; is "ON"?

Generally speaking, it seems that your server configuration does not allow you to use the LOCAL INFILE command. I did not add this code myself, so I might break things trying to get remove it. Try to find any option related to it in your server settings and contact your web hoster, they may be accustomed to this kind of issue.

I hope that will help you!

[HugoFara]

Hello,

After an extensive study of your problem, I don't think it can be "solved" on my side, Here are the conclusions:

• The LOCAL INFILE statement is used at several places of the code, so I may remove it completely or not at all.
• It is a standard statement, but is requires permissions on the server side. If you are unable to edit the php.ini configuration file, it won't work, otherwise it will. So it is not a bug but a normal secure behavior.

In a nutshell, I cannot bring a "fix". As I am aware that many users will face the same issue, I may use a workaround to avoid this statement. In the meantime, the issue will unfortunately persist. Of course, I am open to any pull request from anyone wanting to solve this problem!

[HugoFara]

Hi! This issue have been fixed with the release 2.5.1. It still remains on terms import, I may take care of this part later.

[HugoFara]

I removed the use of LOCAL INFILE in most parts of the code, except the "Import terms" feature (upload_words.php). If you want to use this feature here is the general procedure.

To allow "LOCAL INFILE":

• Open a command prompt
• Find the location of your php.ini file. You can run php -r "phpinfo();" | grep php.ini to help you.
• Edit the file, for instance sudo nano <LOCATION>/php.ini (for instance /etc/php/8.1/php.ini).
• Search the line "mysqli.allow_local_infile = 1" (you can use Ctrl+W). It tells PHP that it can is LOCAL INFILE, but you need to set the permission in MySQL as well (next step).
• Edit MySQL configuration

sudo mysql -u root -p
abcxyz

• In the MySQL console
  SET GLOBAL local_infile="On";