PERL Beginners - 'unknown column' error

Author

'unknown column' error

Huub

2006-12-20, 9:58 pm

Hi,

I'm trying to insert a record in a MySQL database using Perl. I already
wrote a (working) Perl script to make queries, but now I get this problem.

This is the relevant part of the code:

$plaats = "Oostvoorne";
$straat = "Middellandweg";
$teller = 3;
$complex = 5;

$opdracht = "insert into woningen (complex,plaats,straat,huisnr) values
($complex,$plaats,$straat,$teller)";
$sth = $dbh->prepare($opdracht);
$sth->execute or die "Connection Error: $DBI::errstr\n";

This is the errormessage:

DBD::mysql::st execute failed: Unknown column 'Oostvoorne' in 'field
list' at /home/Huub/workspace/VulWoningen/VulWoningen.pl line 31.
Connection Error: Unknown column 'Oostvoorne' in 'field list'

It says 'Unknown column'. That is right: It's no column, it's data to be
inserted into the column. When I use the query directly in MySQL,
there's no problem. So what is going wrong here?

Thanks for helping.

Huub

Jeff Pang

2006-12-20, 9:58 pm

>$opdracht = "insert into woningen (complex,plaats,straat,huisnr) values
>($complex,$plaats,$straat,$teller)";

Above is wrong.You maybe want the statement of:

$opdracht = "insert into woningen (complex,plaats,straat,huisnr) values ('$complex','$plaats',$straat,$teller)";

When your column type isn't int style,then you need to use '$value' for insert.(Note for the single quotes pair).

--
Books below translated by me to Chinese.
Practical mod_perl: http://home.earthlink.net/~pangj/mod_perl/
Squid the Definitive Guide: http://home.earthlink.net/~pangj/squid/

Paul Lalli

2006-12-20, 9:58 pm

Huub wrote:
> I'm trying to insert a record in a MySQL database using Perl. I already
> wrote a (working) Perl script to make queries, but now I get this problem.
>
> This is the relevant part of the code:
>
> $plaats = "Oostvoorne";
> $straat = "Middellandweg";
> $teller = 3;
> $complex = 5;
>
> $opdracht = "insert into woningen (complex,plaats,straat,huisnr) values
> ($complex,$plaats,$straat,$teller)";
> $sth = $dbh->prepare($opdracht);
> $sth->execute or die "Connection Error: $DBI::errstr\n";
>
> This is the errormessage:
>
> DBD::mysql::st execute failed: Unknown column 'Oostvoorne' in 'field
> list' at /home/Huub/workspace/VulWoningen/VulWoningen.pl line 31.
> Connection Error: Unknown column 'Oostvoorne' in 'field list'
>
> It says 'Unknown column'. That is right: It's no column, it's data to be
> inserted into the column. When I use the query directly in MySQL,
> there's no problem.

That's just flat out not correct. You did not use the query above in
MySQL, because if you had, you would have gotten the same error. The
query you used directly in MySQL properly quoted the string data
values.

You have two options. Either put the quotes into your query, as
another responder suggested, or take advantage of placeholders:

$opdracht = "insert into woningen (complex,plaats,straat,huisnr) values
(?, ?, ?, ?)";
$sth = $dbh->prepare($opdracht);
$sth-> execute($complex,$plaats,$straat,$teller
)
or die "Connection Error: $DBI::errstr\n";

When you use placeholders, DBI will automatically figure out for you
which of the values need to be quoted, and will quote those for you.

> So what is going wrong here?

Next time, try printing out the SQL you've generated, and then copy and
paste that SQL into the MySQL command-line client. You'll see you get
the same error. That should help you debug your problem.

Paul Lalli

D. Bolliger

2006-12-20, 9:58 pm

Huub am Mittwoch, 20. Dezember 2006 14:47:
> Hi,

Hello

> I'm trying to insert a record in a MySQL database using Perl. I already
> wrote a (working) Perl script to make queries, but now I get this problem.
>
> This is the relevant part of the code:
>
> $plaats = "Oostvoorne";
> $straat = "Middellandweg";
> $teller = 3;
> $complex = 5;
>
> $opdracht = "insert into woningen (complex,plaats,straat,huisnr) values
> ($complex,$plaats,$straat,$teller)";

warn "<$opdracht>";

would show what is going wrong :-)

> $sth = $dbh->prepare($opdracht);
> $sth->execute or die "Connection Error: $DBI::errstr\n";
>
> This is the errormessage:
>
> DBD::mysql::st execute failed: Unknown column 'Oostvoorne' in 'field
> list' at /home/Huub/workspace/VulWoningen/VulWoningen.pl line 31.
> Connection Error: Unknown column 'Oostvoorne' in 'field list'
>
> It says 'Unknown column'. That is right: It's no column, it's data to be
> inserted into the column. When I use the query directly in MySQL,
> there's no problem. So what is going wrong here?

That's because the (string) values do not appear quoted in the insert
statement and thus are interpreted as field names;
the quotes in "Oostvoorne" are on the perl level.
You would have to write it like "'Oostvoorne'" or better q('Oostvoorne').

The better way is imho to use the quote() method for values,

and the best to use ? placeholders and give the values to the execute()
method - which prevents sql injections on a "basic level".

See perldoc DBI

Hope this helps!

Dani

Greg Sabino Mullane

2006-12-20, 9:58 pm

On Wed, 2006-12-20 at 14:47 +0100, Huub wrote:

> $opdracht = "insert into woningen (complex,plaats,straat,huisnr) values
> ($complex,$plaats,$straat,$teller)";
> $sth = $dbh->prepare($opdracht);
> $sth->execute or die "Connection Error: $DBI::errstr\n";
> ...
> It says 'Unknown column'. That is right: It's no column, it's data to be
> inserted into the column. When I use the query directly in MySQL,
> there's no problem. So what is going wrong here?

Non-numeric values need to be quoted properly for the database to
understand what you are trying to do. DBI provides an excellent way to
do the quoting automatically by using placeholders. Your queries are
better written as:

$opdracht = "INSERT INTO wonigen (complex,plaats,straat,huisnr) VALUES
(?,?,?,?)";
$sth = $dbh->prepare($opdracht);
$sth-> execute($complex,$plaats,$straat,$teller
)
or die "Connection Error: $DBI::errstr\n";

Not only will this free you from worrying about how to quote the
variables, but it allows you to re-use the same statement with different
variables by passing different arguments to execute again.

--
Greg Sabino Mullane greg@endpoint.com
End Point Corporation

Dr.Ruud

2006-12-20, 9:58 pm

Huub schreef:

> DBD::mysql::st execute failed: Unknown column 'Oostvoorne' in 'field
> list' at /home/Huub/workspace/VulWoningen/VulWoningen.pl line 31.
> Connection Error: Unknown column 'Oostvoorne' in 'field list'
>
> It says 'Unknown column'. That is right: It's no column, it's data to
> be inserted into the column. When I use the query directly in MySQL,
> there's no problem. So what is going wrong here?

Look into 'prepare'.

--
Affijn, Ruud

"Gewoon is een tijger."

Sponsored Links