Tagged: buddypress, HTML Forms
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.12WordPress URL: http://localhost/%5Bmysitename%5D
Home URL: http://localhost/%5Bmysitename%5DContent 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/uploadsCookie Domain: Disabled
Multi-Site Active: NoPHP 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: 2MWP Options Count: 198
WP Options Size: 59.95kb
WP Options Transients: 1WP_DEBUG: Disabled
SCRIPT_DEBUG: Disabled
SAVEQUERIES: Not set
AUTOSAVE_INTERVAL: 60
WP_POST_REVISIONS: 1Operating 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.36Active Theme:
– Orbis-child 1.0.0Active 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
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
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
BrajeshHi 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
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.