adam/Piwigo
1
0 Fork 0
mirror of https://github.com/Piwigo/Piwigo.git synced 2026-10-30 21:52:00 +01:00
Code Issues 657 Packages Projects Releases Wiki Activity

error in SQL syntax to use near ')' #2136

New Issue
Open
opened 2026-10-05 01:07:20 +02:00 by adam · 2 comments
adam commented 2026-10-05 01:07:20 +02:00
Owner
Copy link
Copy source

I regularly encounter this error when I try to view the details of an image:

Fatal error: Uncaught mysqli_sql_exception: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 3 in /var/www/vhosts/myhost.fr/medias.myhost.fr/include/dblayer/functions_mysqli.inc.php:132 Stack trace: #0 /var/www/vhosts/myhost.fr/medias.myhost.fr/include/dblayer/functions_mysqli.inc.php(132): mysqli->query() #1 /var/www/vhosts/myhost.fr/medias.myhost.fr/include/dblayer/functions_mysqli.inc.php(888): pwg_query() #2 /var/www/vhosts/myhost.fr/medias.myhost.fr/include/functions.inc.php(1683): query2array() #3 /var/www/vhosts/myhost.fr/medias.myhost.fr/picture.php(946): hash_from_query() #4 {main} thrown in /var/www/vhosts/myhost.fr/medias.myhost.fr/include/dblayer/functions_mysqli.inc.php on line 132

So I replaced the code located in /include/dblayer/functions_mysqli.inc.php

By the following code :

function query2array($query, $key_name=null, $value_name=null)
{
  if (preg_match('/\bIN\s*\(\s*\)/i', $query)) {
    error_log('[PIWIGO] Requête SQL invalide détectée dans query2array (IN () vide) : ' . $query);
    error_log('[PIWIGO] Backtrace query2array : ' . print_r(debug_backtrace(DEBUG_BACKTRACE_IGNORE_ARGS), true));
    return array();
  }

  $result = pwg_query($query);
  $data = array();

  if (isset($key_name))
  {
    if (isset($value_name))
    {
      while ($row = pwg_db_fetch_assoc($result))
        $data[ $row[$key_name] ] = $row[$value_name];
    }
    else
    {
      while ($row = pwg_db_fetch_assoc($result))
        $data[ $row[$key_name] ] = $row;
    }
  }
  else
  {
    if (isset($value_name))
    {
      while ($row = pwg_db_fetch_assoc($result))
        $data[] = $row[$value_name];
    }
    else
    {
      while ($row = pwg_db_fetch_assoc($result))
        $data[] = $row;
    }
  }

  return $data;
}

Is this correct in your opinion?
Thank you for your help.

I regularly encounter this error when I try to view the details of an image: > Fatal error: Uncaught mysqli_sql_exception: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 3 in /var/www/vhosts/myhost.fr/medias.myhost.fr/include/dblayer/functions_mysqli.inc.php:132 Stack trace: #0 /var/www/vhosts/myhost.fr/medias.myhost.fr/include/dblayer/functions_mysqli.inc.php(132): mysqli->query() #1 /var/www/vhosts/myhost.fr/medias.myhost.fr/include/dblayer/functions_mysqli.inc.php(888): pwg_query() #2 /var/www/vhosts/myhost.fr/medias.myhost.fr/include/functions.inc.php(1683): query2array() #3 /var/www/vhosts/myhost.fr/medias.myhost.fr/picture.php(946): hash_from_query() #4 {main} thrown in /var/www/vhosts/myhost.fr/medias.myhost.fr/include/dblayer/functions_mysqli.inc.php on line 132 So I replaced the code located in [/include/dblayer/functions_mysqli.inc.php](https://github.com/Piwigo/Piwigo/blob/7cb8b615b8a3cef275efadae55243eeecd16a2c3/include/dblayer/functions_mysqli.inc.php#L886) By the following code : ```php function query2array($query, $key_name=null, $value_name=null) { if (preg_match('/\bIN\s*\(\s*\)/i', $query)) { error_log('[PIWIGO] Requête SQL invalide détectée dans query2array (IN () vide) : ' . $query); error_log('[PIWIGO] Backtrace query2array : ' . print_r(debug_backtrace(DEBUG_BACKTRACE_IGNORE_ARGS), true)); return array(); } $result = pwg_query($query); $data = array(); if (isset($key_name)) { if (isset($value_name)) { while ($row = pwg_db_fetch_assoc($result)) $data[ $row[$key_name] ] = $row[$value_name]; } else { while ($row = pwg_db_fetch_assoc($result)) $data[ $row[$key_name] ] = $row; } } else { if (isset($value_name)) { while ($row = pwg_db_fetch_assoc($result)) $data[] = $row[$value_name]; } else { while ($row = pwg_db_fetch_assoc($result)) $data[] = $row; } } return $data; } ``` Is this correct in your opinion? Thank you for your help. <!-- github-sync-id:3662611511 -->
adam commented 2026-10-05 01:07:21 +02:00
Author
Owner
Copy link
Copy source

Hi @gregouzdev

So your solution is to return an empty result when you have a IN (1,2,3) but with an empty list instead of {1,2,3}. I don't really agree with your solution. You have no idea how the IN () is meant to be used. Maybe it's going to be NOT IN ..., maybe it's going to be in a UNION query.

My opinion is that we should avoid sending an empty list instead. So let's fix the bug.

If I look at line 946 in file picture.php, I understand that your problem happens when a photo is in no album. Meaning the photo is orphan. You should never see a photo in the gallery page picture.php when it is orphan. So i wonder how you end up in this situation. Can you provide details?

Hi @gregouzdev So your solution is to return an empty result when you have a `IN (1,2,3)` but with an empty list instead of `{1,2,3}`. I don't really agree with your solution. You have no idea how the ` IN ()` is meant to be used. Maybe it's going to be `NOT IN ...`, maybe it's going to be in a `UNION` query. My opinion is that we should avoid sending an empty list instead. So let's fix the bug. If I look at line 946 in file `picture.php`, I understand that your problem happens when a photo is in no album. Meaning the photo is orphan. You should never see a photo in the gallery page `picture.php` when it is orphan. So i wonder how you end up in this situation. Can you provide details? <!-- github-comment-id:3597892718 -->
adam commented 2026-10-05 01:07:22 +02:00
Author
Owner
Copy link
Copy source

Okay, thank you for your help;
how can I determine this from SQL ?

Okay, thank you for your help; how can I determine this from SQL ? <!-- github-comment-id:3627549952 -->
Sign in to join this conversation.
No Branch/Tag Specified
Labels
No items
No labels
Milestone
No items
No Milestone
Projects
Clear projects
No projects
Assignees
Clear assignees
adam (Adam Wojcieszonek)
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: adam/Piwigo#2136