Can't import client csv file

athornfam2

Where's the log file? @wrongecho_hiatus

wrongecho_hiatus

athornfam2

Probably /var/log/apache2/error.log

Do something like tail -F /var/log/apache2/error.log

And then do the import so you can narrow down the spedific lines

athornfam2

wrongecho_hiatus

[Fri Sep 20 12:33:53.573968 2024] [php:notice] [pid 3078232] [client 192.168.4.103:53355] PHP Notice: Only variables should be passed by reference in /var/www/itflow.##.com/post/client.php on line 386, referer: http://itflow.##.local/clients.php

[Fri Sep 20 12:33:53.575445 2024] [php:error] [pid 3078232] [client 192.168.4.103:53355] PHP Fatal error: Uncaught mysqli_sql_exception: Incorrect string value: '\\x92 Asso...' for column `itflow`.`clients`.`client_name` at row 1 in /var/www/itflow.##.com/post/client.php:531\nStack trace:\n#0 /var/www/itflow.##.com/post/client.php(531): mysqli_query()\n#1 /var/www/itflow.##.com/post.php(29): require_once('...')\n#2 {main}\n thrown in /var/www/itflow.##.com/post/client.php on line 531, referer: http://itflow.##.local/clients.php

wrongecho_hiatus

athornfam2

To confirm, these are after todays update?

athornfam2

wrongecho_hiatus That is correct.

I can't seem to import more than 3 at a time 4 if I'm lucky. It also has trouble with / ' , etc from what I've found when trying to import all these clients/leads

wrongecho_hiatus

Can you share the line in the CSV that starts with \\x92 Asso... please?

athornfam2

wrongecho_hiatus I don't have any line that starts with \\x92 asso or any combination of that.

wrongecho_hiatus

Could you run the following database query, either via phpMyAdmin or running mysql as root, and share the output?

use itflow; -- (or whatever your ITFlow DB is named)

show variables like "character_set_database";

johnny

@wrongecho_hiatus I think I know what the problem is: The function sanitizeInput() strips tags I wonder if we should probably just be using mysqlescapestring() function for CSV imports

wrongecho_hiatus

johnny I don't see why stripping tags would be a problem.

johnny

yeah me either @wrongecho_hiatus @athornfam2 could you share that file with us it will be confidential

Here's a secure drop link to send to us that CSV

https://cloud.itflow.org/s/5o4jFpcZb5ppGGm

athornfam2

johnny

Under the conditions that the data inside it will be confidential and only shared internally with ITflow staff I can do that.

athornfam2

Should have it now.

wrongecho_hiatus

athornfam2

Thanks. Only Johnny and I have access to NextCloud, we wouldn't share this beyond us and will delete once we're done testing.

At first glance, I am thinking it's an encoding issue with the file and/or your database. You have backticks (`) rather than apostrophes('), so maybe we should just strip those? @johnny

wrongecho_hiatus

@athornfam2

Correction here, the character is (’) - https://www.codetable.net/hex/92

This is apparently not a UTF-8 character but a Windows-1252 private use character - I wasn't even aware of the difference before looking this up. If you open your CSV in Notepad++ and select Encoding > UTF8 you see the same x92 we saw in your error message.

I've been pulling my hair out with this. It seems you'll only run into the issue when importing data via CSV because browsers will seemingly convert the character for you*?*

This PR should detect non-UTF8 characters and attempt to convert them. It might not get them 100% of the time, but it should sort it out for you based on what you were able to share.

itflow-org/itflow1067

Edit: This fix requires the MBString PHP extension.

wrongecho_hiatus

@athornfam2 Did the update fix the CSV import, or did we break your install for nothing? 🙃

athornfam2

wrongecho_hiatus Seems to be working just fine. I bulk added another 12 clients which didn't through any errors.