Critical database error after search Critical database error after search
 

News:

cpg1.5.48 Security release - upgrade mandatory!
The Coppermine development team is releasing a security update for Coppermine in order to counter a recently discovered vulnerability. It is important that all users who run version cpg1.5.46 or older update to this latest version as soon as possible.
[more]

Main Menu

Critical database error after search

Started by steveclv, April 20, 2014, 10:39:05 PM

Previous topic - Next topic

0 Members and 1 Guest are viewing this topic.

steveclv

If I search my gallery http://www.picturethis.com.mt/gallery/ and then click on one of the results (try using DOME as the search term) I then get a critical database error.

I enabled debug mode and this is what I get - can anyone help please?

While executing query 'SELECT COUNT(*) FROM cpg15x_pictures AS p
                LEFT JOIN `picture_db`.cpg15x_users AS u ON p.owner_id = u.user_id
                WHERE ((p.title LIKE '%dome%')) AND approved = 'YES'
                AND (title < 'Mellieha Church Dome and Bell Tower' OR title = 'Mellieha Church Dome and Bell Tower' AND pid < 446)' in include/search.inc.php on line 255

mySQL error: Column 'title' in where clause is ambiguous

USER:
------------------
Array
(
    [ID] => f8bc9479b69450327e43736e3e494227
    [am] => 1
    [lang] => english
    [liv] => Array
        (
           
  • => 257
                [1] => 24
                [2] => 25
                [3] => 37
                [4] => 41
            )

        [upload_method] => swfupload
        [liv_a] => Array
            (
               
  • => 1
                [1] => 2
            )

        [search] => Array
            (
                [params] => Array
                    (
                        [title] => on
                        [type] => full
                    )

                [search] => dome
            )

    )

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

        [disk_max] => 0
        [disk_min] => 0
        [can_rate_pictures] => 1
        [can_send_ecards] => 1
        [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
    (
       
  • => SELECT name, value FROM cpg15x_config [include/init.inc.php:179] (1.59 ms)
        [1] => SELECT * FROM cpg15x_plugins ORDER BY priority [include/plugin_api.inc.php:52] (1.07 ms)
        [2] => SELECT user_id, time FROM `picture_db`.cpg15x_sessions WHERE session_id = '82fa16383265a4fe16fd0dffe62abd14' [bridge/coppermine.inc.php:267] (0.94 ms)
        [3] => SELECT user_id, user_password FROM `picture_db`.cpg15x_users WHERE user_id = 1 [bridge/coppermine.inc.php:279] (0.95 ms)
        [4] => SELECT u.user_id AS id, u.user_name AS username, user_password AS password, u.user_group AS group_id FROM `picture_db`.cpg15x_users AS u LEFT JOIN `picture_db`.cpg15x_usergroups AS g ON u.user_group=g.group_id WHERE u.user_id='1' [bridge/udb_base.inc.php:70] (1.14 ms)
        [5] => SELECT user_group_list FROM `picture_db`.cpg15x_users AS u WHERE user_id='1' AND user_group_list <> '' [bridge/coppermine.inc.php:205] (0.92 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 cpg15x_usergroups WHERE group_id in (1) [bridge/udb_base.inc.php:323] (1.14 ms)
        [7] => SELECT group_name FROM cpg15x_usergroups WHERE group_id= 1 [bridge/udb_base.inc.php:327] (0.9 ms)
        [8] => SELECT COUNT(*) FROM cpg15x_categorymap WHERE group_id in (1) [bridge/udb_base.inc.php:340] (0.73 ms)
        [9] => SELECT lang_id FROM cpg15x_languages WHERE enabled='YES' [include/init.inc.php:330] (1.01 ms)
        [10] => SELECT user_favpics FROM cpg15x_favpics WHERE user_id = 1 [include/init.inc.php:388] (0.76 ms)
        [11] => SELECT name, value FROM cpg15x_cpgshop_config [plugins/cpgshop/libs/cpgDB.class.php:24] (1.12 ms)
        [12] => SHOW TABLES LIKE 'cpg15x_languages' [include/functions.inc.php:4133] (1.54 ms)
        [13] => SELECT * FROM cpg15x_languages [include/functions.inc.php:4140] (1.26 ms)
        [14] => SELECT cart FROM `cpg15x_cpgshop_cart` WHERE cid='2' AND save=0 LIMIT 1; [plugins/cpgshop/libs/cpgDB.class.php:36] (1.04 ms)
        [15] => DELETE FROM cpg15x_banned WHERE expiry < '2014-04-20 16:22:24' [include/init.inc.php:444] (0.81 ms)
        [16] => SELECT null FROM cpg15x_banned WHERE (user_id=1 OR '92.251.76.241' LIKE ip_addr ) AND brute_force=0 LIMIT 1 [include/init.inc.php:460] (0.8 ms)
        [17] => SELECT title FROM cpg15x_pictures WHERE pid = 446 [include/search.inc.php:236] (0.98 ms)
        [18] => SELECT COUNT(*) FROM cpg15x_pictures AS p
                    LEFT JOIN `picture_db`.cpg15x_users AS u ON p.owner_id = u.user_id
                    WHERE ((p.title LIKE '%dome%')) AND approved = 'YES'
                    AND (title < 'Mellieha Church Dome and Bell Tower' OR title = 'Mellieha Church Dome and Bell Tower' AND pid < 446) [include/search.inc.php:255] (0.83 ms)
        [19] => SELECT COUNT(*) FROM cpg15x_pictures WHERE approved = 'NO' [include/functions.inc.php:2421] (1.43 ms)
        [20] => SELECT lang_id, abbr FROM cpg15x_languages WHERE available='YES' AND enabled='YES' [include/themes.inc.php:2292] (1.2 ms)
        [21] => SELECT user_name FROM cpg15x_users WHERE user_id = '' [plugins/description_metas/codebase.php:188] (0.85 ms)
    )

    ==========================
    GET :
    ------------------
    Array
    (
        [album] => search
        [cat] => 0
        [pid] => 446
    )

    ==========================
    POST :
    ------------------
    Array
    (
    )

    ==========================
    COOKIE :
    ------------------
    Array
    (
        [cpg15x_data] => YTo3OntzOjI6IklEIjtzOjMyOiJmOGJjOTQ3OWI2OTQ1MDMyN2U0MzczNmUzZTQ5NDIyNyI7czoyOiJhbSI7aToxO3M6NDoibGFuZyI7czo3OiJlbmdsaXNoIjtzOjM6ImxpdiI7YTo1OntpOjA7czozOiIyNTciO2k6MTtzOjI6IjI0IjtpOjI7czoyOiIyNSI7aTozO3M6MjoiMzciO2k6NDtzOjI6IjQxIjt9czoxMzoidXBsb2FkX21ldGhvZCI7czo5OiJzd2Z1cGxvYWQiO3M6NToibGl2X2EiO2E6Mjp7aTowO2k6MTtpOjE7aToyO31zOjY6InNlYXJjaCI7YToyOntzOjY6InBhcmFtcyI7YToyOntzOjU6InRpdGxlIjtzOjI6Im9uIjtzOjQ6InR5cGUiO3M6NDoiZnVsbCI7fXM6Njoic2VhcmNoIjtzOjQ6ImRvbWUiO319
        [cpg15x_cpgshop] => YToxOntzOjc6ImNhcnRfaWQiO2k6Mjt9
        [f17bba29415467b2b37b6086fd25218c] => c98c0d80f2432428e7f3d0593fce1efa
        [PHPSESSID] => 84cdd0250e67bbab4dd4522a3f5fe584
    )

    ==========================
    SESSION :
    ------------------
    Array
    (
        [php_captcha] => 1ae86dbd34a4ee7a77a4a5d5bf9f0c7f
    )

    ==========================
    VERSION INFO :
    ------------------
    PHP version        5.2.17           OK   
    MySQL version      5.0.67-community OK   
    Coppermine version 1.5.28           stable

    ==========================
    Module: GD
    ------------------
    Exact version                    2.0.34                     
    GD Version                       bundled (2.0.34 compatible)
    FreeType Support                 1                         
    FreeType Linkage                 with freetype             
    T1Lib Support                                               
    GIF Read Support                 1                         
    GIF Create Support               1                         
    JPG 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.picturethis.com.mt/gallery/     
    charset              utf-8                                       
    allow_private_albums 1                                           
    cookie_name          cpg15x                                     
    cookie_path          /                                           
    impath               /usr/bin/                                   
    lang                 english                                     
    main_page_layout     breadcrumb/catlist/alblist/random,2/lastup,2
    silly_safe_mode      0                                           
    smtp_host                                                       
    theme                zaffatasa                                   
    thumb_method         gd2                                         

    ==========================
    Plugins
    ------------------
    Name           Delete Favorite                                                                                                                               
    Actions        plugin_install, plugin_uninstall, page_start                                                                                                   
    Filters        page_html                                                                                                                                     
    --------------
    Name           File replacer                                                                                                                                 
    Actions        page_start                                                                                                                                     
    Filters        file_data                                                                                                                                     
    --------------
    Name           cpgShop                                                                                                                                       
    Actions        plugin_install, plugin_configure, plugin_uninstall, plugin_cleanup, page_start, profile_display_form, profile_submit_form, register_form_submit
    Filters        register_form_create, admin_menu, gallery_header, gallery_footer, file_data                                                                   
    --------------
    Name           SocialShare Plugin                                                                                                                             
    Actions        page_start, plugin_install, plugin_uninstall                                                                                                   
    Filters        file_data                                                                                                                                     
    --------------
    Name           Add Meta Description                                                                                                                           
    Actions        plugin_install, plugin_uninstall                                                                                                               
    Filters        theme_pageheader_params                                                                                                                       
    --------------
    Name           Sitemap Generator                                                                                                                             
    Actions        plugin_install, plugin_uninstall                                                                                                               
    Filters                                                                                                                                                       
    --------------
    Name           Search Engine Friendly URLs                                                                                                                   
    Actions        plugin_install, plugin_uninstall, plugin_configure, plugin_cleanup                                                                             
    Filters        page_html                                                                                                                                     
    --------------

    ==========================
    Server restrictions
    ------------------
    safe_mode                0                                               
    safe_mode_exec_dir                                                       
    safe_mode_gid                                                           
    safe_mode_include_dir                                                   
    sql.safe_mode                                                           
    disable_functions                                                       
    file_uploads             1                                               
    include_path             .:/usr/local/lib/php                           
    open_basedir             /home/picture/:/tmp:/var/tmp:/usr/local/lib/php/
    allow_url_fopen          1                                               
    max_execution_time       30                                             
    max_input_time           60                                             
    upload_max_filesize      32M                                             
    post_max_size            8M                                             
    memory_limit             64M                                             
    suhosin.post.max_vars                                                   
    suhosin.request.max_vars                                                 


    ==========================
    Page (performance)
    ------------------
    Parameter        Current   Peak     
    Memory usage     5.17 MiB  5.25 MiB
    Page generation  325.51 ms 325.51 ms
    Page query time  23 ms     23 ms   
    Page query count 22        22       


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

steveclv

I did find another thread that talked about a plugin adding a column called 'Title' to the user table and suggesting a fix to search_inc.php

I checked the php code and the 'fix' was already in there but I also checked the user table and there is a column in there called 'title' - I renamed it to 'title2' and it fixed the problem but how can I fix the problem correctly in the code without changing the database as I don't know what other errors may now occur.

gmc

Since both pictures (cpg15x_pictures AS p) from base code and users (cpg15x_users AS u) from a mod/plugin have a field named 'title' - it needs to be qualified as p.title in the sql being issued to tell MySQL it is referring the the title in the pictures title...
That qualification comes from the variable '$FORBIDDEN_SET' which is used in many queries - and they may not all define 'p' as pictures...

I'd need to look closer at the other thread and the code (search seems to be having some issues at the moment...)
I'll take a look tomorrow unless someone else gets to it first.

(The change you made renaming title to title2 in users table won't affect base code - but will affect whatever mod is expecting it to be there.)
Thanks!
Greg
My Coppermine Gallery
Need a web hosting account? See my gallery for an offer for CPG Forum users.
Send me money

Αndré

The following mod is not tested, but should work. Open include/search.inc.php, find
$sort_order = "$criteria $direction '$criteria_pid' OR $criteria = '$criteria_pid' AND pid < $pid";
and above, add
if ($criteria == 'title') {
    $criteria = 'p.title';
}

steveclv


Αndré

Marking thread as solved. Next time, please do this yourself as explained in the board rules:

Quote from: Joachim Müller on September 28, 2008, 12:46:26 PM
Resolve your threads
If you have found an answer to your question, resolve your thread. Don't just post "I have found the answer", but tell others what you actually did to solve your issues. Posting a link to the page where you found the answer might help. Describing what you did might help as well. Finally, you can tag your thread as "solved" by clicking on the "Topic Solved" button on the bar at the left hand side at the bottom of your thread.