in reply to Re: Writing NULL values to a MySQL record via DBI
in thread Writing NULL values to a MySQL record via DBI

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?
  • Comment on Re^2: Writing NULL values to a MySQL record via DBI

Replies are listed 'Best First'.
Re^3: Writing NULL values to a MySQL record via DBI
by Your Mother (Archbishop) on Feb 26, 2015 at 22:45 UTC

    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]

        Although it is behind closed doors today, there's no telling what will be tomorrow. It also helps by mostly forcing type definition of the parameters. Further, by preparing the statement, it make it more efficient when running it multiple times.

        It's really easy to do. Just use placeholders, define them with the appropriate statements, and pass them when executing the query.

[reply]
Re^3: Writing NULL values to a MySQL record via DBI
by Anonymous Monk on Feb 26, 2015 at 22:34 UTC
    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]
        Please read the documentation. The undef is very necessary here and refers only to the value for the second argument being passed to the method do. Explaining exactly what that argument is for is difficult, at best. Otherwise you are very very close to understanding how this works.
[reply]

In Section Seekers of Perl Wisdom