How to make below code safe from sql injection ... I am working in wordpress. global $wpdb;
if($wpdb->insert(
'votes',
array(
'votes' => $votes,
'competition' => $competition,
'uid' => $uid
)
) == false) wp_die('Database Insertion failed'); else echo 'Database insertion successful<p />';
The insert method of $wpdb already scapes the data taking care of SQL injection. What you should worry about is about data sanitization and validation.
For exmpla:
Sanitization
What type of data do you accept in $votes? A integer value? If so, be sure $votes contains a integer value. You cuold do it, for exmaple, using intval function from PHP. This is sanitization.
$votes = intval( $votes );
What type of data do you accept in $competition? Any string with no HTML, tabs or line breaks, ? If so, be sure $competition contains a string value, strip HTML tags and remove tabs and line break; you could do it, for exmaple, using sanitize_text_field function from WordPress. This is also sanitization.
$competition = sanitize_text_field( $competition );
Data validation
What value do you accept in $votes? From 0 to 100, including both? If so, you could check the value of $votes:
if( $votes >= 0 && $votes <= 100 ) {
// Value is correct
} else {
// Value is not correct. Maybe abort the process?
}
Do you have a list of valid competitions? Check the value of $competition against it:
$valid_competitions = array( 'competition1', 'competition2', 'competition3' );
if( in_array( $competition, $valid_competitions ) ) {
// Value is correct
} else {
// Value is not correct. Maybe abort the process?
}
Do the same process with $uid.
Once you have santize and validate your variables, you are ready to pass them to $wpdb->insert method like this (with the third parameter performs a basic sanitization, you can avoid previous sanitization if this is enough for your situation):
$wpdb->insert(
'votes',
array(
'votes' => $votes,
'competition' => $competition,
'uid' => $uid
),
array(
'%d', // integer data type for $votes
'%s', // string data type for $competition
'%d', // integer data type for $uid
)
);
0 <= $votes <= 100 compares a boolean to an integer, rather than doing the expected two integer comparisons. Expect lots of bugs with code like that.
if( $votes >= 0 && $votes <= 100 ) is better and/or correct? Fixed.
wpdb::insert already protects against SQL injection, it's a wrapper for wpdb::prepare
For insert(), you can pass a third "formats" argument for extra sanitization:
$wpdb->insert(
'votes',
array(
'votes' => $votes,
'competition' => $competition,
'uid' => $uid
),
array(
'%s', // $votes will be parsed as a string
'%s', // $competition will be parsed as a string
'%d', // $uid will be parsed as an integer
)
);
