WordPress.org

Support

$wpdb

  • Hi everyone!

    I’m developing a plugin, and I’m almost done.
    I have some issues though.

    When I add something to my database, it’s shown on my admin-dashboard. That part works fine and dandy! But! I want a “Delete” button to each “item”..

    This is how i “Call” the items from my DB:

    global $wpdb;
    $table_name = $wpdb->prefix."comment_reminder";
    $cremindsql = $wpdb->get_results("SELECT * FROM $table_name ORDER BY id DESC");

    As I said, that part works great!
    Now, I want to add a DELETE button – I just can’t figure out how I do this. I have this code for now:
    $delblog = $wpdb->query("DELETE FROM $table_name WHERE id ='1');

    How can I make “WHERE id = ‘1’” to be automatic?
    I mean, I have a button beside each item, not I need, somehow, to put an “id” to each button?

    How do I do this? ๐Ÿ™

    Thanks
    Kucko

Viewing 15 replies - 1 through 15 (of 27 total)
  • Moderator bcworkz

    @bcworkz

    You assign the id to a variable, then use it like this:
    "...WHERE id = '$id'"
    So you need a way to get the id for the item. It could be passed as an url parameter in a delete link. Or in a hidden field to be passed as part of a form POST.

    Be sure to use a nonce as part of the request too so no one can fabricate a GET or POST request to do the same without credentials.

    Hi bcworkz,

    This is what my form looks like:

    <form method="post" action="options.php">
        <label>Blog URL</label>
        <input type="text" id="cr_url" name="cr_url">
        <label>Blog Name</label>
        <input type="text" id="cr_name" name="cr_name"><br><br>
        <input type="submit" class="button-primary" value="Save info" />
        </form>

    So now, I have to put ID in a hidden field you say?
    Or I can use a Delete link.ยจยจ

    I more interested in the delete link – but I really dont know how. Can you help me out a bit? ๐Ÿ™‚

    Moderator bcworkz

    @bcworkz

    I agree a link makes more sense than a form. When you are outputting the html for each item, I assume the item id is available in php code? So you could build a delete link who’s href might look like example.com/handlerpage.php?action=del&id=372 . You should pass this through wp_nonce_url() before echoing out or anyone could delete something by typing the link into their browser.

    The handler page receiving the request would get the parameters from the $_GET array. You would use check_admin_referer(); to verify the nonce is valid, then go ahead and do the delete query using the passed id value. If successful, some sort of verification should probably be sent to the user, or an error message on any failure.

    Yet another possibility is using ajax to send the request and ID, then the response can be reflected right on the current page without need to reload. There’s info in the Codex on how to do this, but it’s not that thorough. Various tutorials outside of wordpress.org are your best bet. If you get stuck, come back here.

    It WORKS!
    Thanks A LOT bcworkz!

    I created a delete.php and now it works!
    If anyone else needs help, read this – it worked for me!

    http://www.phpsimple.net/mysql_delete_record.html

    Oh I have another question.
    WordPress.org tells me I can’t include wp-config
    That there’s other ways to do it – this is what they write:

    It’s best if you tie your processing functions (the ones that need but don’t have access to core functions) into an action hook, such as “init” or “admin_init”.

    Can anyone help me out here?

    Moderator bcworkz

    @bcworkz

    That doesn’t make sense to me, you need access to core functions to add an action hook, how can it be done if you don’t have access to core functions? (Rhetorical question, no answer expected.)

    Why were you including wp-config? You need access to the $wpdb global from your delete.php page? In most cases you should require_once( $path . 'wp-load.php' ); . But since you would want the user doing the deleting to be logged in to delete records, I assume, you should require wp-admin/admin.php instead. This does not mean the user needs to be admin, just that they need to be logged in.

    This is what my delete.php looks like:

    <?php
    
    require_once("../../../wp-config.php");
    global $wpdb;
    
    $table_name = $wpdb->prefix."comment_reminder";
    
    $blogid=$_GET['id'];
    
    $wpdb->query( $wpdb->query( "DELETE FROM $table_name WHERE id='$blogid'" ) );
    
    header("location:/wp-admin/options-general.php?page=comment-reminder");
    
    ?>

    I really can’t figure out how to do it else.
    It works perfectly when delete.php is like this.

    Moderator bcworkz

    @bcworkz

    OK, there’s two problems with that, one’s minor, the other is a significant security flaw.

    To fix the minor one, simply replace wp-config.php with wp-load.php. If you look at wp-load.php, all it really does is include wp-config.php, so you were pretty close with what you have. But it also does some important checks, so it’s worth doing it right.

    Left with that easy fix, anyone could type a delete request into their browser and your script will happily delete the data without question. Even worse, you don’t check if the id is a sane value, you are wide open to an SQL injection attack. Through a series of exploits, your site could end up fully compromised, in other words, hacked.

    At the very least, require wp-admin/admin.php instead of wp-load.php or wp-config.php. At least then someone would have to be logged in to delete data, but it could be anyone logged in, even a common user with no capabilities. You still must verify the id value passed is a sane value, such as it must be a positive integer less than 32768.

    You really should also verify the user has the proper capability assigned to do this action. Finally, the link should include a nonce which the delete.php script verifies so you are assured the request came from a validly served page and is not some sort of attack.

    All these layers of security may seem paranoid. Try putting ‘hacked’ into this site’s search box. You’ll get over 15000 results, virtually all support requests for hacked sites. I’ve personally responded to dozens of these, and am frankly rather tired of it. Don’t be another victim.

    Okay, so I will require admin.php instead og wp-config.

    Can you please tell me how to do the rest?
    Im really rabbish at MySQL and so, but I’ve “invented” this plugin for a personal use, but I would also like to share it.

    I have a lot to learn I see.
    But if I get the right guidance, I could learn it myself later on, because then I have a “protocol” to look at.

    You’re talking about a sane value – how do I do that?
    And the “nonce” you’re talking about?

    I can paste my codes here, if you want?
    And also, I will “credit” you in the plugin for your help ๐Ÿ™‚

    Moderator bcworkz

    @bcworkz

    Sane Values: In this case for an ID, just checking that it is indeed an integer in a certain range is enough. For other situations, it depends on the expected data. You want to restrict the value as much as possible without constricting any valid input. Also take a look at Validating Sanitizing and Escaping User Data.

    Nonces: Nonce is a portmanteau of Number used ONCE. Specifically for WordPress, it is a unique, large, hexadecimal number that a server attaches to a form or link. When the server receives a request purportedly from that form or link, it can look at this number to correlate it to which form or link it sent out to begin with, because each nonce is used only once.

    Thus, if the nonce received does not correlate to one that was recently sent, it can safely be assumed the request is invalid and possibly some sort of hack attempt, and the request can be ignored. Acceptance of any request that comes in that would result in a change of the current state of the server or database should be protected with a nonce. Start with WordPress Nonces for more on nonces.

    Once you’ve developed reasonable security measures, I would be happy to review the code and give you some feedback if you desire. Crediting my assistance is hardly necessary, but I would of course be flattered if you chose to do so.

    Hi bcworkz!

    Now I’ve looked around on the internet, to try and find out more about “nonces” and “admin_referrer” – and I’ve come up with a solution.

    This is how my Form looks like now:

    <form method="post" action="options.php">
        <label>Blog URL</label>
        <input type="text" id="cr_url" name="cr_url">
        <label>Blog Name</label>
        <input type="text" id="cr_name" name="cr_name"><br><br>
        <input type="submit" class="button-primary" value="Save info" />
        <?php wp_nonce_field('verify_creminder','creminder_nonce'); ?>
        </form>

    And here is how my delete looks like now:

    <?php
    
    require_once("../../../wp-admin/admin.php");
    global $wpdb;
    
    $table_name = $wpdb->prefix."comment_reminder";
    $blogid=$_GET['id'];
    
    if ( !empty($_POST) && check_admin_referer('verify_creminder','creminder_nonce') )
    {
       $wpdb->query( $wpdb->query( "DELETE FROM $table_name WHERE id='$blogid'" ) );
    }
    
    header("location:/wp-admin/options-general.php?page=comment-reminder");
    
    ?>

    Now the only problem is – i doesen’t delete the URL, as it should.
    I get no errors what so ever – so I really don’t know what’s wrong here. Now I don’t know if it has anything to do with my delete LINK – here it is:

    foreach ($cremindsql as $cremind)
    	{
    		echo '<div class="cr_bloginfo"><strong><a href="/wp-content/plugins/comment-reminder/comment-reminder-delete.php?action=del&id='.$cremind->id .'">Delete</a></strong> - <a href="'.$cremind->blogurl .'" target="_blank">'.$cremind->blogname .'</a><br /></div>';
    	}
    }

    I hope I’m on the right path.

    Thanks
    Aris

    Moderator bcworkz

    @bcworkz

    Aris, you’re on the right path, but you’ve strayed off into the weeds.

    You seem to be confused on what data gets sent to your server when and how. There’s other methods, but for our purposes with WP, we can say all data is sent by browsers by one of two types of requests: GET and POST.

    Links are always GET requests, the only data sent must be in the URL itself, other than some header data, nothing else about the page is sent. In particular, the form data is not sent. The PHP page receiving the request can get parameters in the URL as parts of the $_GET array.

    Form data is sent by what ever method is specified by the method attribute in the form tag. The data sent is the names and values of all form elements within the form tags, nothing else. If the method is GET, each form element’s name becomes an url parameter that is equal to the element’s value. Forms with many fields result in very long URLs so this is often not a good method for forms.

    Forms are better sent with the POST method, in which case the data is sent in a separate data packet than the URL and is retrieved in PHP as the $_POST array. Again, only the names and values of elements between form tags is sent, and there are no URL parameters at all.

    So now you should see that the nonce in your form will never be seen when the delete link GET request is sent. Now that you have a nonce check in place, it will always fail. You need to build your delete link with wp_nonce_url(). Then the nonce check will actually have something to check.

    But don’t get rid of the form nonce! Just as much as you should ensure the delete request is from a proper source, you should ensure the insertion of data request is from a proper source as well. But use a different nonce name to avoid confusion. (I know,.. Arrrgh! More work!)

    I know all this security stuff is a lot of bother for just a simple delete request, especially when you’re learning. Unfortunately, by installing WordPress, you’ve made your site a target for hack attempts. Fortunately, the core WordPress is very secure, but you need to do your part to maintain it when you extend it’s capabilities. Especially if others will be using your code. If you don’t, somebody will eventually find the hole. Once you get this all figured out the first time, writing secure code will become second nature.

    Keep up your worthy efforts. I hope you are finding some enjoyment from this.
    Happy coding,
    bc

    Hi bcworkz!

    Good news!
    I’ve managed to create a nonce, and verify it!
    Or at least I guess I did….

    Heres my “Delete link”:

    global $wpdb;
    $table_name = $wpdb->prefix."comment_reminder";
    $cremindsql = $wpdb->get_results("SELECT id, blogname, blogurl FROM $table_name ORDER BY id DESC");
    $nonce= wp_create_nonce  ('my-nonce');
    	foreach ($cremindsql as $cremind)
    	{
    		echo '<div class="cr_bloginfo"><strong><a href="/wp-content/plugins/comment-reminder/comment-reminder-delete.php?action=del&id='.$cremind->id .'_wpnonce='.$nonce .'">Delete</a></strong> - <a href="'.$cremind->blogurl .'" target="_blank">'.$cremind->blogname .'</a><br /></div>';
    	}
    }

    And my delete.php:

    <?php
    
    require_once("../../../wp-admin/admin.php");
    global $wpdb;
    
    $table_name = $wpdb->prefix."comment_reminder";
    $blogid=$_GET['id'];
    $nonce=$_REQUEST['_wpnonce'];
    if (! wp_verify_nonce($nonce, 'my-nonce') ) die('Security check'); 
    
    $wpdb->query( $wpdb->query( "DELETE FROM $table_name WHERE id='$blogid'" ) );
    
    header("location:/wp-admin/options-general.php?page=comment-reminder");
    
    ?>

    At least now I get the “Security Check” ….
    Thats progress for me.. ๐Ÿ˜›

    Hi bcworkz!

    I figured it out – all by myself.. ๐Ÿ˜›
    I looked into my link, and found out, there was a “&” missing between the ID and the “_wpnonce” – Now, I can actually delete my record, and when I type in the link into the browser, and chance a number in the nonce, I get the “Security Check” error – that means it works!!!

    Now, I’m going to work a bit with the form-nonce ๐Ÿ˜€
    When I get that thingie figured out, I should be able to pass the “WP Plugin security check” ๐Ÿ™‚

    Am I right?
    or do I need to fix something more?

    – Aris

    Moderator bcworkz

    @bcworkz

    Good work Aris! You deserve a little celebration!

    The nonce exchange takes care of the bulk of security issues. You might need to check user capability, unless only users that see the form all have proper capabilities already. Doesn’t hurt to check again though.

    Same goes for a sanity check. If the id comes hard coded from the form, there’s little chance of it being damaging, but doesn’t hurt to check anyway. Sanity checks really are more important for text fields where the user can enter anything. It’s probably complete overkill to check machine generated ids. I mainly mention it for the sake of general completeness.

    I’m jazzed for you figuring this out. Cheers!
    -bc

Viewing 15 replies - 1 through 15 (of 27 total)
  • The topic ‘$wpdb’ is closed to new replies.
Skip to toolbar