User settings - DB Error SQLSTATE[42000]

[jhs0]

Hello,

Just finished installing Part-DB Version: 0.5.6 (stable)
php 7.3.7
MySQL 8.0.16

When try going to User settings or User information,
I get this error message:

Database error:
SQLSTATE[42000]: Syntax error or access violation: 1064 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 'groups WHERE id='1'' at line 1

SQL-Query:
SELECT * FROM groups WHERE id=?

Parameter:
Array
(
[0] => 1
)

Please help me fix it.

Thank you,
Ivan

[jlegen]

Hi,

since MySQL 8.0.2, "groups" is a reserved word! So, your options are:

1. downgrade MySQL, or use MariaDB
2. use a backtick () to surround "groups" where ever it appears in the code ("select xy from groups.." => "select xy from groups` ...)
3. rename table "groups" to somehing else
   of course, 2) or 3) should be also considered by the maintainer...

[jbtronics]

@jhs0 Could you try the current version from the master branch (https://github.com/Part-DB/Part-DB/archive/master.zip) and check if the error is fixed?

All tablenames should now be escaped. That should hopefully fixes your problem.

If it works for you, i will put that patch into the stable branch, too.

[jhs0]

Hi,

since MySQL 8.0.2, "groups" is a reserved word! So, your options are:

1. downgrade MySQL, or use MariaDB
2. use a backtick () to surround "groups" where ever it appears in the code ("select xy from groups.." => "select xy from groups` ...)
3. rename table "groups" to somehing else
   of course, 2) or 3) should be also considered by the maintainer...

Thank you jlegen.
I would not consider option 1.
Options 2 and 3 I like, however, the Database.php file I have is different and the whitelist array does not contain 'groups'. In fact, the word 'groups' does not exist in this file:
$whitelist = array("parts", "categories", "footprints", "storelocations", "suppliers", "pricedetails", "orderdetails", "manufacturers", "attachements", "attachement_types", "devices", "device_parts");

Any ideas where to manipulate the 'groups' table name?
Database.zip

[jhs0]

@jbtronics I tried first with the current version.
I failed in the early stage of connecting to the DB.
If used the part-db user -> complained about permissions.
If used root user -> complained about a table not found.
I did not take any notes or screenshots though.
So I switched to 'stable' and here I am...

If you are interested, I could do it again and provide more info on the above.

[jbtronics]

@jhs0 for testing you could use the same database for the "master" version as you used now (just copy the data/config.php file into the folder of the master version). Then you could check if the error message about the database syntax has disappeared (in that case my fix has worked).

[jhs0]

@jbtronics Now the current version works. I will stick with it.
How far is it from stable? Can I expect some rough ride with it?

@jlegen You probably referred to the Database.php from the development version before the escaping of the tables names.

[jlegen]

@jlegen You probably referred to the Database.php from the development version before the escaping of the tables names.

i did not refer to any specific file - i just said "use a backtick" to enclose table names - where jbtronics used the more correct term "escaping"... :)
Also, "database.php" seems not to be the only file "affected" by non-escaped table names; if Oracle next week decides that they also want to reserve the string "users", then something in "User.php" may also break:
lib/User.php:807: $query = 'SELECT * FROM users WHERE name = ?';
But afaik jbtronics is already busy with refactoring the whole thing - thanks for that, btw!

[jhs0]

@jlegen @jbtronics
Many thanks to both of you!

Just one last thing.
Could you please help me fix the same error, coming from /edit_groups.php and /edit_users.php in the dev version?
I have troubles finding the locations to touch-up.

[jbtronics]

@jhs0 Could you show copy the exact error message? This makes it a lot easier to findout where the issue is.

@jbtronics Now the current version works. I will stick with it.
How far is it from stable? Can I expect some rough ride with it?

The dev version should be relativley stable now (i use it too for my database). Nevertheless i have put the patch from above in the stable version too (https://github.com/Part-DB/Part-DB/archive/stable-v0.5.zip).

[jhs0]

@jbtronics

Datenbankfehler:
SQLSTATE[42000]: Syntax error or access violation: 1064 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 'groups WHERE parent_id <=> NULL ORDER BY name ASC' at line 1

SQL-Query:
SELECT * FROM groups WHERE parent_id <=> ? ORDER BY name ASC

Parameter:
Array
(
[0] => 
)

Please see attached.

[jhs0]

...maybe StructuralDBElement.php
I will try to escape the table name there...

[jhs0]

yay, it worked!
in file lib/Base/StructuralDBElement.php added backticks around table name:

            $query_data = $this->database->query('SELECT * FROM `' . $this->tablename .
                '` WHERE parent_id <=> ? ORDER BY name ASC', array($id));

            'SELECT * FROM `' . $parent_element->getTablename() .
            '` WHERE name=? AND parent_id <=> ? AND id<>?',

thank you, thank you, thank you!!!
Cheers!

[jbtronics]

Should be fixed now in code too. See new Release Version 0.5.7