MySQL error due to Title / Description.

[Montezuma]

I'm getting this warning message "[Warning] Statement may not be safe to log in statement format."

This is due to the Title or Description having an apostrophe in it.  E.G., Joe's ice house or Mike's new car.

Is there any fix for this?  It's only in the MySQL log file but it tend to generate a lot of errors.

[Αndré]

I wasn't able to reproduce that on my testbed (actually I found not even 1 error in any of the log files). Try to google for that error message, maybe you'll find a solution.

[Montezuma]

Okay.  I cleared all my Apache, MySQL and message logs.  I found a picture that has a apostrope in the title, went into edit mode, and applied changes.  This is the results:

Code Select Expand

111205 11:07:54 [Warning] Statement may not be safe to log in statement format. Statement: UPDATE cpg_pictures SET aid = '104',
title = 'Anne's mom with Elianna.', caption = '', keywords = '', approved = 'YES', user1 = '', user2 = '', user3 = '', user4 = '' WHERE pid='4054' LIMIT 1

Code Select Expand

USER:
------------------
Array
(
    [ID] => 12c8bea2082d6633d8290a5ec5615ce5
    [lang] => english
    [liv_a] => Array
        (
            [0] => 104
            [1] => 106
            [2] => 105
        )

    [liv] => Array
        (
            [0] => 4016
            [1] => 4017
            [2] => 4000
            [3] => 4019
            [4] => 198
        )

    [uid] => 9
    [am] => 1
)

==========================
USER DATA:
------------------
Array
(
    [user_id] => 1
    [user_name] => admin
    [groups] => Array
        (
            [0] => 1
        )

    [disk_max] => 0
    [disk_min] => 0
    [can_rate_pictures] => 1
    [can_send_ecards] => 0
    [can_post_comments] => 1
    [can_upload_pictures] => 1
    [can_create_albums] => 1
    [has_admin_access] => 1
    [access_level] => 3
    [pub_upl_need_approval] => 0
    [priv_upl_need_approval] => 0
    [group_name] => Administrators
    [can_create_public_albums] => 0
    [group_quota] => 0
    [can_see_all_albums] => 1
    [group_id] => 1
    [allowed_albums] => Array
        (
        )

)

==========================
Queries:
------------------
Array
(
    [0] => SELECT name, value FROM cpg_config [include/init.inc.php:181] (1 ms)
    [1] => SELECT * FROM cpg_plugins ORDER BY priority [include/plugin_api.inc.php:53] (0 ms)
    [2] => SELECT user_id, time FROM `cpg`.cpg_sessions WHERE session_id = '7c2997fd0af86fe2fdef1451745f8655' [bridge/coppermine.inc.php:264] (0 ms)
    [3] => SELECT user_id, user_password FROM `cpg`.cpg_users WHERE user_id = 1 [bridge/coppermine.inc.php:276] (0 ms)
    [4] => SELECT u.user_id AS id, u.user_name AS username, user_password AS password, u.user_group AS group_id FROM `cpg`.cpg_users AS u LEFT JOIN `cpg`.cpg_usergroups AS g ON u.user_group=g.group_id WHERE u.user_id='1' [bridge/udb_base.inc.php:70] (0 ms)
    [5] => SELECT user_group_list FROM `cpg`.cpg_users AS u WHERE user_id='1' AND user_group_list <> '' [bridge/coppermine.inc.php:202] (0 ms)
    [6] => SELECT MAX(group_quota) AS disk_max, MIN(group_quota) AS disk_min, MAX(can_rate_pictures) AS can_rate_pictures, MAX(can_send_ecards) AS can_send_ecards, MAX(can_post_comments) AS can_post_comments, MAX(can_upload_pictures) AS can_upload_pictures, MAX(can_create_albums) AS can_create_albums, MAX(has_admin_access) AS has_admin_access, MAX(access_level) AS access_level, MIN(pub_upl_need_approval) AS pub_upl_need_approval, MIN( priv_upl_need_approval) AS  priv_upl_need_approval FROM cpg_usergroups WHERE group_id in (1) [bridge/udb_base.inc.php:323] (0 ms)
    [7] => SELECT group_name FROM cpg_usergroups WHERE group_id= 1 [bridge/udb_base.inc.php:327] (0 ms)
    [8] => SELECT COUNT(*) FROM cpg_categorymap WHERE group_id in (1) [bridge/udb_base.inc.php:340] (0 ms)
    [9] => UPDATE `cpg`.cpg_sessions SET time = 1323104874 WHERE session_id = '7c2997fd0af86fe2fdef1451745f8655' [bridge/coppermine.inc.php:321] (0 ms)
    [10] => UPDATE `cpg`.cpg_users SET user_lastvisit = NOW() WHERE user_id = '1' [bridge/coppermine.inc.php:325] (0 ms)
    [11] => SELECT lang_id FROM cpg_languages WHERE enabled='YES' [include/init.inc.php:329] (0 ms)
    [12] => SELECT user_favpics FROM cpg_favpics WHERE user_id = 1 [include/init.inc.php:387] (0 ms)
    [13] => DELETE FROM cpg_banned WHERE expiry < '2011-12-05 11:07:54' [include/init.inc.php:443] (0 ms)
    [14] => SELECT null FROM cpg_banned WHERE (user_id=1 OR '10.1.10.43' LIKE ip_addr ) AND brute_force=0 LIMIT 1 [include/init.inc.php:459] (0 ms)
    [15] => SELECT category, owner_id, url_prefix, filepath, filename, pwidth, pheight, p.aid AS aid FROM cpg_pictures AS p INNER JOIN cpg_albums AS a ON a.aid = p.aid WHERE pid = '4054' [edit_one_pic.php:87] (0 ms)
    [16] => SELECT category FROM cpg_albums WHERE aid = '104' [edit_one_pic.php:103] (0 ms)
    [17] => SELECT extension, mime, content, player FROM cpg_filetypes [include/functions.inc.php:5809] (1 ms)
    [18] => UPDATE cpg_pictures SET aid = '104', title = 'Anne's mom with Elianna.', caption = '', keywords = '', approved = 'YES', user1 = '', user2 = '', user3 = '', user4 = '' WHERE pid='4054' LIMIT 1 [edit_one_pic.php:174] (0 ms)
    [19] => SELECT *, p.title AS title, p.votes AS votes FROM cpg_pictures AS p INNER JOIN cpg_albums AS a ON a.aid = p.aid WHERE pid = '4054' [edit_one_pic.php:377] (0 ms)
    [20] => SELECT aid, title, IF(category = 0, CONCAT('> ', title), CONCAT(name,' < ',title)) AS cat_title FROM cpg_albums LEFT JOIN cpg_categories ON category = cid WHERE category < '10000' ORDER BY cat_title [edit_one_pic.php:406] (1 ms)
    [21] => SELECT aid, title FROM cpg_albums WHERE category='10001'  OR category = 10003 ORDER BY title [edit_one_pic.php:301] (1 ms)
    [22] => SELECT COUNT(*) FROM cpg_pictures WHERE approved = 'NO' [include/functions.inc.php:2438] (2 ms)
    [23] => SELECT lang_id, abbr FROM cpg_languages WHERE available='YES' AND enabled='YES' [include/themes.inc.php:2316] (0 ms)
    [24] => DELETE FROM cpg_temp_messages WHERE time < 1323101274 [include/functions.inc.php:4947] (0 ms)
)

==========================
GET :
------------------
Array
(
)

==========================
POST :
------------------
Array
(
    [id] => 4054
    [aid] => 104
    [public_albums] => 93
    [private_albums] => 63,64,2,62,1,19,17,20,9,69,27,47,82,87,83,104,95,3
    [title] => Anne's mom with Elianna.
    [filename] => Anne_s_mom.jpg
    [caption] =>
    [keywords] =>
    [approved] => YES
    [apply_changes] => Apply changes
    [form_token] => 6dbdcf74a261ff17eaa3424bb9125f75
    [timestamp] => 1323104828
)

==========================
COOKIE :
------------------
Array
(
    [cpg_data] => YTo2OntzOjI6IklEIjtzOjMyOiIxMmM4YmVhMjA4MmQ2NjMzZDgyOTBhNWVjNTYxNWNlNSI7czo0OiJsYW5nIjtzOjc6ImVuZ2xpc2giO3M6NToibGl2X2EiO2E6Mzp7aTowO2k6MTA0O2k6MTtpOjEwNjtpOjI7aToxMDU7fXM6MzoibGl2IjthOjU6e2k6MDtzOjQ6IjQwMTYiO2k6MTtzOjQ6IjQwMTciO2k6MjtzOjQ6IjQwMDAiO2k6MztzOjQ6IjQwMTkiO2k6NDtzOjM6IjE5OCI7fXM6MzoidWlkIjtpOjk7czoyOiJhbSI7aToxO30=
    [3b639ebcf21066cd526f31e82cceed9d] => 49775344efaa4ea53bd1cabab564e32e
    [PHPSESSID] => lsng427rio8k0k6jirisa55h12
)

==========================
SESSION :
------------------
Array
(
    [php_captcha] => 6c3f82ea26451e891c3147dba09cae56
)

==========================
VERSION INFO :
------------------
PHP version        5.3.6-pl0-gentoo OK   
MySQL version      5.1.51-log       OK   
Coppermine version 1.5.16           stable

==========================
Module: GD
------------------
Exact version                    2.0.34                     
GD Version                       bundled (2.0.34 compatible)
FreeType Support                 1                         
FreeType Linkage                 with freetype             
T1Lib Support                    1                         
GIF Read Support                 1                         
GIF Create Support               1                         
JPEG Support                     1                         
PNG Support                      1                         
WBMP Support                     1                         
XPM Support                                                 
XBM Support                      1                         
JIS-mapped Japanese Font Support                           

==========================
Key config settings
------------------
site_url             http://www.cs-mn.com/cpg/                   
charset              utf-8                                       
allow_private_albums 1                                           
cookie_name          cpg                                         
cookie_path          /                                           
impath               /usr/bin/                                   
lang                 english                                     
main_page_layout     breadcrumb/catlist/alblist/random,2/lastup,2
silly_safe_mode      0                                           
smtp_host                                                       
theme                rainy_day                                   
thumb_method         im                                         

==========================
Plugins
------------------
Name           JUpload Coppermine Plugin
Actions        plugin_wakeup, page_start
Filters        add_file_data           
--------------

==========================
Server restrictions
------------------
safe_mode                                               
safe_mode_exec_dir                                       
safe_mode_gid                                           
safe_mode_include_dir                                   
sql.safe_mode                                           
disable_functions                                       
file_uploads             1                               
include_path             .:/usr/share/php5:/usr/share/php
open_basedir                                             
allow_url_fopen                                         
max_execution_time       130                             
max_input_time           160                             
upload_max_filesize      16M                             
post_max_size            20M                             
memory_limit             128M                           
suhosin.post.max_vars                                   
suhosin.request.max_vars                                 


==========================
Page (performance)
------------------
Parameter        Current  Peak   
Memory usage     3.06 MiB 4.28 MiB
Page generation  75 ms    75 ms   
Page query time  6 ms     6 ms   
Page query count 25       25     


==========================


Okay.  I wanted to test my theory about the apostrophe so I did the same with a different picture.

Code Select Expand

111205 11:26:35 [Warning] Statement may not be safe to log in statement format. Statement: UPDATE cpg_pictures SET aid = '63',
title = 'Karen.', caption = 'Born September 5, 1952', keywords = '', approved = 'YES', user1 = '', user2 = '', user3 = '', user4 = '' WHERE pid='3450' LIMIT 1


Code Select Expand

USER:
------------------
Array
(
    [ID] => 12c8bea2082d6633d8290a5ec5615ce5
    [lang] => english
    [liv_a] => Array
        (
            [0] => 104
            [1] => 106
            [2] => 105
        )

    [liv] => Array
        (
            [0] => 4016
            [1] => 4017
            [2] => 4000
            [3] => 4019
            [4] => 198
        )

    [uid] => 9
    [am] => 1
)

==========================
USER DATA:
------------------
Array
(
    [user_id] => 1
    [user_name] => admin
    [groups] => Array
        (
            [0] => 1
        )

    [disk_max] => 0
    [disk_min] => 0
    [can_rate_pictures] => 1
    [can_send_ecards] => 0
    [can_post_comments] => 1
    [can_upload_pictures] => 1
    [can_create_albums] => 1
    [has_admin_access] => 1
    [access_level] => 3
    [pub_upl_need_approval] => 0
    [priv_upl_need_approval] => 0
    [group_name] => Administrators
    [can_create_public_albums] => 0
    [group_quota] => 0
    [can_see_all_albums] => 1
    [group_id] => 1
    [allowed_albums] => Array
        (
        )

)

==========================
Queries:
------------------
Array
(
    [0] => SELECT name, value FROM cpg_config [include/init.inc.php:181] (1 ms)
    [1] => SELECT * FROM cpg_plugins ORDER BY priority [include/plugin_api.inc.php:53] (0 ms)
    [2] => SELECT user_id, time FROM `cpg`.cpg_sessions WHERE session_id = '7c2997fd0af86fe2fdef1451745f8655' [bridge/coppermine.inc.php:264] (0 ms)
    [3] => SELECT user_id, user_password FROM `cpg`.cpg_users WHERE user_id = 1 [bridge/coppermine.inc.php:276] (0 ms)
    [4] => SELECT u.user_id AS id, u.user_name AS username, user_password AS password, u.user_group AS group_id FROM `cpg`.cpg_users AS u LEFT JOIN `cpg`.cpg_usergroups AS g ON u.user_group=g.group_id WHERE u.user_id='1' [bridge/udb_base.inc.php:70] (0 ms)
    [5] => SELECT user_group_list FROM `cpg`.cpg_users AS u WHERE user_id='1' AND user_group_list <> '' [bridge/coppermine.inc.php:202] (0 ms)
    [6] => SELECT MAX(group_quota) AS disk_max, MIN(group_quota) AS disk_min, MAX(can_rate_pictures) AS can_rate_pictures, MAX(can_send_ecards) AS can_send_ecards, MAX(can_post_comments) AS can_post_comments, MAX(can_upload_pictures) AS can_upload_pictures, MAX(can_create_albums) AS can_create_albums, MAX(has_admin_access) AS has_admin_access, MAX(access_level) AS access_level, MIN(pub_upl_need_approval) AS pub_upl_need_approval, MIN( priv_upl_need_approval) AS  priv_upl_need_approval FROM cpg_usergroups WHERE group_id in (1) [bridge/udb_base.inc.php:323] (0 ms)
    [7] => SELECT group_name FROM cpg_usergroups WHERE group_id= 1 [bridge/udb_base.inc.php:327] (0 ms)
    [8] => SELECT COUNT(*) FROM cpg_categorymap WHERE group_id in (1) [bridge/udb_base.inc.php:340] (0 ms)
    [9] => SELECT lang_id FROM cpg_languages WHERE enabled='YES' [include/init.inc.php:329] (0 ms)
    [10] => SELECT user_favpics FROM cpg_favpics WHERE user_id = 1 [include/init.inc.php:387] (0 ms)
    [11] => DELETE FROM cpg_banned WHERE expiry < '2011-12-05 11:26:35' [include/init.inc.php:443] (0 ms)
    [12] => SELECT null FROM cpg_banned WHERE (user_id=1 OR '10.1.10.43' LIKE ip_addr ) AND brute_force=0 LIMIT 1 [include/init.inc.php:459] (0 ms)
    [13] => SELECT category, owner_id, url_prefix, filepath, filename, pwidth, pheight, p.aid AS aid FROM cpg_pictures AS p INNER JOIN cpg_albums AS a ON a.aid = p.aid WHERE pid = '3450' [edit_one_pic.php:87] (0 ms)
    [14] => SELECT category FROM cpg_albums WHERE aid = '63' [edit_one_pic.php:103] (0 ms)
    [15] => SELECT extension, mime, content, player FROM cpg_filetypes [include/functions.inc.php:5809] (1 ms)
    [16] => UPDATE cpg_pictures SET aid = '63', title = 'Karen.', caption = 'Born September 5, 1952', keywords = '', approved = 'YES', user1 = '', user2 = '', user3 = '', user4 = '' WHERE pid='3450' LIMIT 1 [edit_one_pic.php:174] (0 ms)
    [17] => SELECT *, p.title AS title, p.votes AS votes FROM cpg_pictures AS p INNER JOIN cpg_albums AS a ON a.aid = p.aid WHERE pid = '3450' [edit_one_pic.php:377] (0 ms)
    [18] => SELECT aid, title, IF(category = 0, CONCAT('> ', title), CONCAT(name,' < ',title)) AS cat_title FROM cpg_albums LEFT JOIN cpg_categories ON category = cid WHERE category < '10000' ORDER BY cat_title [edit_one_pic.php:406] (1 ms)
    [19] => SELECT aid, title FROM cpg_albums WHERE category='10001'  OR category = 10003 ORDER BY title [edit_one_pic.php:301] (1 ms)
    [20] => SELECT COUNT(*) FROM cpg_pictures WHERE approved = 'NO' [include/functions.inc.php:2438] (2 ms)
    [21] => SELECT lang_id, abbr FROM cpg_languages WHERE available='YES' AND enabled='YES' [include/themes.inc.php:2316] (0 ms)
    [22] => DELETE FROM cpg_temp_messages WHERE time < 1323102395 [include/functions.inc.php:4947] (0 ms)
)

==========================
GET :
------------------
Array
(
)

==========================
POST :
------------------
Array
(
    [id] => 3450
    [aid] => 63
    [public_albums] => 93
    [private_albums] => 63,64,2,62,1,19,17,20,9,69,27,47,82,87,83,104,95,3
    [title] => Karen.
    [filename] => 0000_Karen.jpg
    [caption] => Born September 5, 1952
    [keywords] =>
    [approved] => YES
    [apply_changes] => Apply changes
    [form_token] => 4b32f142ccd42609d401a98d2bf4a600
    [timestamp] => 1323105989
)

==========================
COOKIE :
------------------
Array
(
    [cpg_data] => YTo2OntzOjI6IklEIjtzOjMyOiIxMmM4YmVhMjA4MmQ2NjMzZDgyOTBhNWVjNTYxNWNlNSI7czo0OiJsYW5nIjtzOjc6ImVuZ2xpc2giO3M6NToibGl2X2EiO2E6Mzp7aTowO2k6MTA0O2k6MTtpOjEwNjtpOjI7aToxMDU7fXM6MzoibGl2IjthOjU6e2k6MDtzOjQ6IjQwMTYiO2k6MTtzOjQ6IjQwMTciO2k6MjtzOjQ6IjQwMDAiO2k6MztzOjQ6IjQwMTkiO2k6NDtzOjM6IjE5OCI7fXM6MzoidWlkIjtpOjk7czoyOiJhbSI7aToxO30=
    [3b639ebcf21066cd526f31e82cceed9d] => 49775344efaa4ea53bd1cabab564e32e
    [PHPSESSID] => lsng427rio8k0k6jirisa55h12
)

==========================
SESSION :
------------------
Array
(
    [php_captcha] => e110f6d5a55568e52fa85e9b96dc4916
)

==========================
VERSION INFO :
------------------
PHP version        5.3.6-pl0-gentoo OK   
MySQL version      5.1.51-log       OK   
Coppermine version 1.5.16           stable

==========================
Module: GD
------------------
Exact version                    2.0.34                     
GD Version                       bundled (2.0.34 compatible)
FreeType Support                 1                         
FreeType Linkage                 with freetype             
T1Lib Support                    1                         
GIF Read Support                 1                         
GIF Create Support               1                         
JPEG Support                     1                         
PNG Support                      1                         
WBMP Support                     1                         
XPM Support                                                 
XBM Support                      1                         
JIS-mapped Japanese Font Support                           

==========================
Key config settings
------------------
site_url             http://www.cs-mn.com/cpg/                   
charset              utf-8                                       
allow_private_albums 1                                           
cookie_name          cpg                                         
cookie_path          /                                           
impath               /usr/bin/                                   
lang                 english                                     
main_page_layout     breadcrumb/catlist/alblist/random,2/lastup,2
silly_safe_mode      0                                           
smtp_host                                                       
theme                rainy_day                                   
thumb_method         im                                         

==========================
Plugins
------------------
Name           JUpload Coppermine Plugin
Actions        plugin_wakeup, page_start
Filters        add_file_data           
--------------

==========================
Server restrictions
------------------
safe_mode                                               
safe_mode_exec_dir                                       
safe_mode_gid                                           
safe_mode_include_dir                                   
sql.safe_mode                                           
disable_functions                                       
file_uploads             1                               
include_path             .:/usr/share/php5:/usr/share/php
open_basedir                                             
allow_url_fopen                                         
max_execution_time       130                             
max_input_time           160                             
upload_max_filesize      16M                             
post_max_size            20M                             
memory_limit             128M                           
suhosin.post.max_vars                                   
suhosin.request.max_vars                                 


==========================
Page (performance)
------------------
Parameter        Current  Peak   
Memory usage     3.05 MiB 4.28 MiB
Page generation  66 ms    66 ms   
Page query time  6 ms     6 ms   
Page query count 23       23     


==========================


Obviously, I was wrong, it is not the apostrophe.  Do you see anything else that I am missing?

[Montezuma]

Update.  This is not directly a Coppermine problem but a MySQL one.

I'm hoping that someone else has had the problem and found a solution or if I find the answer first, it will be a solution to the next person.

[Αndré]

I also googled around and found an advice to adjust the MySQL error reporting level. But I don't know where you have to do that / what exactly you have to change. Additionally I don't know if it fixes your that issue.

[Montezuma]

That is the same type of info I got.  It seems to be a question in the minds of MySQL people whether it is a bug or a feature.  Last I saw was in 2010, it became a feature.  Luckily for me, only my family is using CPG and I get more log info from the attempted e-mail hackers than this.  So far, over a decade and they haven't beat my system.