BuddyDev

Search

My code with $wpdb and a condition is not working (HTML Forms/BuddyPress)

Tagged: ,

  • Participant
    Level: Initiated
    Posts: 8
    goyelle on September 8, 2018 at 9:34 am #17187

    Hi,

    I am a newbie with quite everything and I have a problem with a condition that doesn’t work in a code part where I’m using the $wpdb global variable. I’ve really tried to fix it on my own but I think I’m missing something obvious because of my poor knowledge.

    For my site using BuddyPress, I’m trying to modify the HTML Forms plugin to create different forms (with different ids), each of them displayed in a different tab in a member page, to store data linked to the logged in user (like the profile tab). I can’t use field groups because I need too many forms and it wouldn’t fit in the subnav bar.
    (I’m not sure this is the better way to do it but I didn’t find any plugin doing that, and I need to control my forms, including JavaScript to complicated conditional fields.)

    Initially, the HTML Forms plugin saves each submitted form in a new row without the current user id, and I need it to update the row where the current user id AND the current form id are stored, or create a new row if it doesn’t exist. This way I would have no duplicate information and I could display it in a page/profile tab in a member account. (again, like the profile tab)

    I’ve managed to add the current user id column in the table where the forms are stored, and I now I want to check the table and ask for an “update” or an “insert” function.

    If I write the update line (as following), if the row exists with the double condition, the row is updated.
    But if the row doesn’t exist, it’s not created.
    If I delete my update command, a new row is created.

    I hope I’m clear enough.

    Here is the code I’m using: (it’s the HTML Forms plugin function, I’ve commented my own code lines)

    
public function save() {
        global $wpdb;
        $table = $wpdb->prefix .'hf_submissions';

        $data = array(
            'data' => json_encode( $this->data ),
            'form_id' => $this->form_id,
        );

// I've added 'userID' in the following line
         foreach( array( 'userID', 'ip_address', 'user_agent', 'submitted_at', 'referer_url', ) as $prop ) { 
            $data[ $prop ] = $this->$prop;
        }

// following lines added by me
        $foruserid = $data['userID']; 
        $forformid = $data['form_id'];

        $results = $wpdb->get_var( "SELECT COUNT(*) FROM $table WHERE form_id = $forformid AND userID = $foruserid",ARRAY_A);

        if ($results=1){
            $wpdb->update( $table, $data, array( 'userID' => $foruserid, 'form_id' => $forformid) );
        } 
        else {
// end of following lines added by me

            if( ! empty( $this->id ) ) {
                $wpdb->update( $table, $data, array( 'id' => $this->id ) );
                return;
            }

            // insert new row
            $num_rows = $wpdb->insert( $table, $data );
            if( $num_rows > 0 ) {
                $this->id = $wpdb->insert_id;    
            }
        } // added by me
    }

    If anyone can see what is wrong, please tell me! I’ve tried so many things to understand and then writing this… (in so many days…) I’m quite desperate.

    Thanks to anyone who’s reading this!

    Goyelle
    ——————————————–

    Here are my system informations:

    WordPress Version: 4.9.8
    PHP Version: 5.4.12
    MySQL Version: 5.6.12-log
    Web Server: Apache/2.4.4 (Win64) PHP/5.4.12

    WordPress URL: http://localhost/%5Bmysitename%5D
    Home URL: http://localhost/%5Bmysitename%5D

    Content Directory: C:\wamp\www\[mysitename]/wp-content
    Content URL: http://localhost/%5Bmysitename%5D/wp-content
    Plugins Directory: C:\wamp\www\[mysitename]/wp-content/plugins
    Plugins URL: http://localhost/%5Bmysitename%5D/wp-content/plugins
    Uploads Directory: C:\wamp\www\[mysitename]/wp-content/uploads

    Cookie Domain: Disabled
    Multi-Site Active: No

    PHP cURL Support: Yes
    PHP GD Support: Yes
    PHP Memory Limit: -1
    PHP Memory Usage: 68.99M (-6899%)
    PHP Post Max Size: 8M
    PHP Upload Max Size: 2M

    WP Options Count: 198
    WP Options Size: 59.95kb
    WP Options Transients: 1

    WP_DEBUG: Disabled
    SCRIPT_DEBUG: Disabled
    SAVEQUERIES: Not set
    AUTOSAVE_INTERVAL: 60
    WP_POST_REVISIONS: 1

    Operating System: Windows
    Browser: Google Chrome 68.0.3440.106
    User Agent: Mozilla/5.0 (Windows NT 6.2; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/68.0.3440.106 Safari/537.36

    Active Theme:
    – Orbis-child 1.0.0

    Active Plugins:
    – BuddyPress 3.1.0
    https://buddypress.org/

    – BuddyPress Conditional Field Groups 0.1.0
    http://wordpress.org/extend/plugins

    – BuddyPress Group Calendar 1.4.9
    https://premium.wpmudev.org/project/buddypress-group-calendar/

    – Buddypress Xprofile Fields Custom Css Classes 1.0
    http://codepixlabs.com/plugins/buddypress-xprofile-fields-custom-css-classes

    – Conditional Profile Fields for BuddyPress 1.2.2
    https://buddydev.com/plugins/conditional-profile-fields-for-buddypress/

    – Export Users to CSV 1.1.1
    http://wordpress.org/extend/plugins/export-users-to-csv/

    – HTML Forms 1.3.2
    https://www.htmlforms.io/#utm_source=wp-plugin&utm_medium=html-forms&utm_campaign=plugins-page

    – MediaPress 1.4.1
    https://buddydev.com/mediapress/

    – Orbis 1.3.3
    http://www.pronamic.eu/plugins/orbis/

    – Orbis Tasks 1.1.1
    http://www.orbiswp.com/

    – s2Member Framework 170722
    http://s2member.com/

    – s2member Secure-File Uploader 0.0.2
    http://lewayotte.com/plugins/s2member-secure-file-uploader/

    – s2member Secure File Browser 0.4.19
    http://www.potsky.com/code/wordpress-plugins/s2member-secure-file-browser/

    – SuitePlugins – Advanced XProfile Fields for BuddyPress 1.0.3
    http://suiteplugins.com

    – SysInfo 1.1.0
    http://wordpress.org/extend/plugins/sysinfo/

    – User Switching 1.3.1
    https://johnblackbourn.com/wordpress-plugin-user-switching/

    – WP Inspect 1.0.0
    http://wp-inspect.com/

    – WPS Hide Login 1.4.3

  • Participant
    Level: Initiated
    Posts: 8
    goyelle on September 8, 2018 at 11:11 am #17190

    I found a solution! This site is my lucky charm, after so many days…

    I couldn’t understand why the update code was executed while the condition wasn’t true – and sometimes not, and actually I still don’t get it, but I changed the control and the comparison like that:

    $results = $wpdb->get_var( "SELECT COUNT(*) FROM $table WHERE form_id = $forformid AND userID = $foruserid",ARRAY_A);
    became
    $results = $wpdb->get_row( "SELECT * FROM $table WHERE userID = $foruserid AND form_id = $forformid",ARRAY_A);

    and

    if ($results=1)
    became
    if (!empty($results))

    The function is now:

    public function save() {
        global $wpdb;
        $table = $wpdb->prefix .'hf_submissions';

        $data = array(
            'data' => json_encode( $this->data ),
            'form_id' => $this->form_id,
        );

         foreach( array( 'userID', 'ip_address', 'user_agent', 'submitted_at', 'referer_url', ) as $prop ) { 
            $data[ $prop ] = $this->$prop;
        }

        $foruserid = $data['userID'];
        $forformid = $data['form_id'];

        $results = $wpdb->get_row( "SELECT * FROM $table WHERE userID = $foruserid AND form_id = $forformid",ARRAY_A);
        if (!empty($results)){            
           $wpdb->update( $table, $data, array( 'userID' => $foruserid, 'form_id' => $forformid) );
        } 

        else {
            if( ! empty( $this->id ) ) {
                $wpdb->update( $table, $data, array( 'id' => $this->id ) );
                return;
            }

            // insert new row
            $num_rows = $wpdb->insert( $table, $data );
            if( $num_rows > 0 ) {
                $this->id = $wpdb->insert_id;    
            }
        }
    }

    I can know store form data from different users and different forms, one and only row for each pair on the user id and the form id, I can update or insert forms!

    Now I need to add the format parameter to protect the update command from SQL injection, that’s right?
    I can’t find the right format for the timestamp data. Is it a string? If anyone know, I’d be happy to know as well!

    If anyone can tell me if my code is ok or awful, it would be a great help to improve myself. I’m trying to understand so many things that I can’t judge the efficiency of my code and its security level.

    Maybe this would help anyone else too, anywhere else.

    Thanks in advance!

    Goyelle

  • Keymaster
    (BuddyDev Team)
    Posts: 24623
    Brajesh Singh on September 9, 2018 at 7:13 am #17201

    Hi Goyelle,
    I am glad that it is working on it but there are still some issues with the code and I will post with explanation for the first/2nd post.

    
 if ($results=1){

    That code does not check whether $results is 1. It assigns the value of 1 to $results and will always be true.

    That’s why the code inside

    
if ($results=1){
            $wpdb->update( $table, $data, array( 'userID' => $foruserid, 'form_id' => $forformid) );
        }

    Will always run.

    Now, that you have changed it to

    
if (!empty($results)){            
           $wpdb->update( $table, $data, array( 'userID' => $foruserid, 'form_id' => $forformid) );
        }

    It is working correctly as you are updating by checking if the result has some value, then update.

    The problem with your first fetch was you were using incorrect signature for the get_var() method.

    https://codex.wordpress.org/Class_Reference/wpdb#SELECT_a_Variable

    Instead of changing that code, Let us change it to a better option like this

    
$results = $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM {$table} WHERE form_id = %d AND userID = %d", $forformid, $foruserid ) );

    Hope it helps.

    Regards
    Brajesh

  • Participant
    Level: Initiated
    Posts: 8
    goyelle on September 9, 2018 at 12:53 pm #17202

    Hi Brajesh, thank you again for your help!

    I had a doubt about the “=” and checked the different comparison operators, then first changed to “==” but as you guess, it didn’t work either!

    Thanks for your clarification, I will change that later, learn more about selecting a variable and get back.

    Thanks a lot again, you “hope it helps” but it sure will!

    Regards,

    Goyelle

  • Participant
    Level: Initiated
    Posts: 8
    goyelle on September 10, 2018 at 8:04 am #17207

    Hi Brajesh,

    As expected, your code works like a charm! Thank you very much.
    If you have the time to one last advice, about the timestamp format in the update function, this would be great. I’m trying to improve my attention on security matters, but I know nothing about SQL injections… But I won’t avoid this subject, of course.

    If you don’t have the time, no worries.

    Thanks a lot, again, you have no idea ho much you’ve helped me.

    Have a nice day!

    Goyelle

You must be logged in to reply to this topic.

This topic is: not resolved