0

I'm creating a custom form to update rows in a secondary MYSQL DB, different from the one of wordpress. This form is public and every user can access it. So I've created a plugin and I'm using also wpdb to connect with the database.

How can I do this in a secure way?

I'm using sanitize_text_field() and then wpdb->update(). Is this safe or should I use esc_sql()?

If I can't use wpdb->update() because I need to have some != fields in where clauses, is it ok to use sanitize_text_field(), then wpdb->prepare() and then wpdb->query()?

1

When getting user input to be stored on database, a good way to proceed is:

  1. Data validation: validate the data according whith the data you expect. For example: HTML string, number, email, URL, any text with no HTML, ect. Never trust on user input or client-side validation. You can make here also some sanitization, but it is not substitute of data validation and it is not substitute of data scaping.
  2. SQL escape: no explanation needed here I think. WorPress provide some functions and methods to perform this. esc_sql is the general function to prepare strings to be used in database queries. But is not always needed if you use wpdb class. For example, if using $wpdb->insert and $wpdb->update the data should be not scaped because it will be done for you.

If using $wpdb->query, the best method to scape the query is using $wpdb->prepare method. Additional scape methods are avalable in wpdb class; see this of how to use $wpdb->prepare and this for data escape before database interaction.

0

An example for inserting data to WPDB securely. It's prepared method which prevents the sql injection. First create your form and then insert your data like the following structure. Example:

$insert_value_array = array(
    'input' => '',      // name of your input field
    'textarea' => '',   // field name
    'select' => '',     // field name
    'checkbox' => '',   // field name
);

$form_item = shortcode_atts( $insert_value_array, $_REQUEST );

if(isset($_POST['submit'])){

    global $wpdb;

    $full_table_name = $wpdb->prefix . 'your_table_name';

    // $wpdb->insert( $full_table_name, $form_item); // It's without prepare method

 // Safe data insertion with prepare method ( successful )
    $wpdb->query( $wpdb->prepare( 
                "   INSERT INTO $full_table_name
                    ( column_name_1, column_name_2, column_name_3, column_name_4 )
                    VALUES ( %s, %s, %s, %s ) 
                ", // VALUES depends on your datatype. %s for string, %d for integer
                    $form_item
            ) );

};

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.