Re: Writing NULL values to a MySQL record via DBI

[Anonymous Monk]

What is the schema definition for your table? Also, you should be using placeholders. (read more)

[ureco]

Must admit I'm not entirely sure how to use placeholders in the context of my code - kind of read one way to do MySQL updates and not sure how to apply different methods to the code I've now written - would using placeholder help with the undef / NULL problem?

[Your Mother]

It’s been recommended but perhaps not forcefully enough. SQL work without placeholders is tragically, criminally insecure. I know just getting things working is sometimes a necessary first step but placeholders are not something to file under, Hmmm, interesting, but, Say, I could destroy my company with one line of this code.

See also: Exploits of a mom and bobby-tables.com.

[ureco]

Definitely looking at placeholders now :o) Thankfully this code is executing firmly behind closed doors and not on a public server - but I understand this appears to be a much better and secure way do perform database access tasks. But more reading to be done... every day is a school day :o) Thanks

[Anonymous Monk]

I'll do some of the work for you:

my $mo_raw_sql = "
    REPLACE INTO met_office_raw_data
        SET observation_datetime_utc   = ?,
        observation_datetime_local     = ?
";

my $mo_raw = $dbh->do(
    $mo_raw_sql,
    undef,
    $file_obs_datetime_utc,
    $file_obs_datetime_local,
);

Just fill out the rest and you should be fine, so long as your schema definition allows NULL values.

[ureco]

Just started to search for placeholders and I can see what you are suggesting - thanks. As I understand it (remember I've only read a small amount in the last few minutes) you substitute the ? for the corresponding variable in the statement. In the case above, first ? relates to the $file_obs_datetime_utc and the second ? relates to $file_obs_datetime_local. Does the undef statement above cover all the fields that follow? Just looking to understand fully the code you posted.