update a mysql-db with date

[Captain Beefart]

Hi,
I have created a database in mysql that have a field in dtetime format.
In perl I have the string "20040728165500" to update this field in the db.
I'm using the module mysql.

Part of the code:
my $sql_statement="INSERT INTO cme (date, name) VALUES ($datetime, $name)" ;
print "$sql_statement\n";
my $sth = $dbh->query($sql_statement);

The output:
INSERT INTO cme (date, name) VALUES (20040729134000, Beefart)
Can't call method "prepare" on an undefined value
at /usr/lib/perl5/vendor_perl/5.8.2/i686-linux/Mysql.pm line 169.

Anyone that can tell me how to do it?
I've been searching for more than a week now.

 

[Brian McCauley]

my $sql_statement="INSERT INTO cme (date, name) VALUES ($datetime, $name)" ;
print "$sql_statement\n";
my $sth = $dbh->query($sql_statement);

The output:
INSERT INTO cme (date, name) VALUES (20040729134000, Beefart)

That doesn't look like valid SQL to me. Not, of course, that this has
anything to do with Perl of course.

You should quote the strings in you SQL or better still use placeholders.

Can't call method "prepare" on an undefined value
at /usr/lib/perl5/vendor_perl/5.8.2/i686-linux/Mysql.pm line 169.

Please post a minimal but complete script that reproduces the
symptoms.

This and much other valuable advice can be found in the posting
guidelines.

--
\\ ( )
. _\\__[oo
.__/ \\ /\@
. l___\\
# ll l\\
###LL LL\\

 

[Captain Beefart]

That doesn't look like valid SQL to me. Not, of course, that this has
anything to do with Perl of course.

You should quote the strings in you SQL or better still use placeholders.


Please post a minimal but complete script that reproduces the
symptoms.

This and much other valuable advice can be found in the posting
guidelines.

Sorry, I found out my mistake by myself, The problem occured already when I
tried to open the database. The opening command was in error.
But it didn't give me any message that this command failed. And as I was
unsure about the dateformat from the beginning and as the error gave me
some indication through the prepare method and that there where some
undefined value, I only could think about this date. Stupid of course
but...
It working now and by the way, you thought it didn't look like valid SQL,
hmm, I think it is and it's working. What was it that you didn't like?

Thank you anyway.
/CB

 

[Brian McCauley]

It working now and by the way, you thought it didn't look like valid SQL,

SQL doesn't have bareword strings so you need quotes around Beefart.
You probably need them around 20040729134000 too.

hmm, I think it is and it's working. What was it that you didn't like?

This may mean mysql does have bareword strings. Using bareword
strings, except interactively is a bad idea. That's why we always
recommend that you switch them off in Perl. The same goes for SQL.
It may be handy to have bareword strings in a command line tool but
you shouldn't be using them in programatically generated SQL.

--
\\ ( )
. _\\__[oo
.__/ \\ /\@
. l___\\
# ll l\\
###LL LL\\

 

[Matt Garrish]

SQL,

SQL doesn't have bareword strings so you need quotes around Beefart.
You probably need them around 20040729134000 too.


This may mean mysql does have bareword strings. Using bareword
strings, except interactively is a bad idea. That's why we always
recommend that you switch them off in Perl. The same goes for SQL.

MySQL does not allow bareword strings either on the command line or via DBI
(the string would be treated as the name of a column). If the date field is
a bigint (for example, since I didn't see it specified anywhere what types
he's using) the date he's entering wouldn't cause an error, but the unquoted
string should kill the insert statement. My guess, however, since he wasn't
checking whether the open succeeded is that he's not checking whether the
insert succeeds.

The query() call in his code would also suggest that he's not using the DBI
module, but more likely the Mysql module to access the database, and I'm not
sure if it supports placeholders (one more reason to switch). To the OP,
this doesn't make it any less important to properly quote your variables. At
the very least you should be using the built in quote method. Even if your
code is only run by you, it will be more portable should you decide to
change databases at some future point. And if the variables come from an
untrusted source, it will stop someone from running malicious code on your
database.

Matt