database - Insert NULL value using prepare()

admin管理员组

文章数量:1300261

I have similars queries

/* Query */
global $wpdb;
$tablename = $wpdb->prefix . 'data';
$sql = $wpdb->prepare(
    "
    UPDATE $tablename
    SET
        `date` = %s,
    WHERE
        id= %d
    ",
    $_POST['date'] == '' ? "NULL": $_POST['date'],
    $_POST['id']
);
$wpdb->query($sql);

This will results in:

UPDATE `date` SET 'NULL' WHERE `id` = $_POST['id'] 

so prepare() is adding single quotes to NULL and the query sets the field to NULL string not NULL value. The only fix for me is to take the variable outside of the prepare() function like this:

/* Query */
global $wpdb;
$tablename = $wpdb->prefix . 'data';

/* Here I declare the variabile outside of the prepare() */
$date = $output['date'] == '' ? "NULL" : $_POST['date'];
$sql = $wpdb->prepare(
    "
    UPDATE $tablename
    SET
        `date` = $date,
    WHERE
        id = %d
    ",
    $_POST['id']
);
$wpdb->query($sql);

I have similars queries

/* Query */
global $wpdb;
$tablename = $wpdb->prefix . 'data';
$sql = $wpdb->prepare(
    "
    UPDATE $tablename
    SET
        `date` = %s,
    WHERE
        id= %d
    ",
    $_POST['date'] == '' ? "NULL": $_POST['date'],
    $_POST['id']
);
$wpdb->query($sql);

This will results in:

UPDATE `date` SET 'NULL' WHERE `id` = $_POST['id'] 

so prepare() is adding single quotes to NULL and the query sets the field to NULL string not NULL value. The only fix for me is to take the variable outside of the prepare() function like this:

/* Query */
global $wpdb;
$tablename = $wpdb->prefix . 'data';

/* Here I declare the variabile outside of the prepare() */
$date = $output['date'] == '' ? "NULL" : $_POST['date'];
$sql = $wpdb->prepare(
    "
    UPDATE $tablename
    SET
        `date` = $date,
    WHERE
        id = %d
    ",
    $_POST['id']
);
$wpdb->query($sql);

• database
• query
• wpdb
• security

Share Improve this question asked Nov 20, 2020 at 8:23 silvered.dragonsilvered.dragon 11344 bronze badges

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

the quick solution I found is to str_replace empty value.

/* Query */
global $wpdb;
$tablename = $wpdb->prefix . 'data';
$sql = $wpdb->prepare(
    "
    UPDATE $tablename
    SET
        `date` = %s,
    WHERE
        id= %d
    ",
    $_POST['date'],
    $_POST['id']
);
// SQL = UPDATE prefix_data SET `date` = '' WHERE id = 1


$sql = str_replace("''",'NULL', $sql);
// SQL = UPDATE prefix_data SET `date` = NULL WHERE id = 1

$wpdb->query($sql);

本文标签： databaseInsert NULL value using prepare()