in reply to Writing NULL values to a MySQL record via DBI

What is the schema definition for your table? Also, you should be using placeholders. (read more)
  • Comment on Re: Writing NULL values to a MySQL record via DBI

Replies are listed 'Best First'.
Re^2: Writing NULL values to a MySQL record via DBI
by ureco (Acolyte) on Feb 26, 2015 at 22:26 UTC
    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?
[reply]

      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.

[reply]
        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
[reply]
      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,
);

      [download]
      Just fill out the rest and you should be fine, so long as your schema definition allows NULL values.
[reply]
[d/l]
        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.
[reply]

In Section Seekers of Perl Wisdom