• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 246
  • Last Modified:

Problem inserting into a mysql db from a form

Here is the code...

sub mysql {
if ($database eq 'mysql')  {
use DBI;
$dbh = DBI->connect("dbi:mysql:$mysqlname","$mysqlusername","$mysqlpassword") || die("Couldn't connect to database!\n");
$query = "INSERT INTO form values('$FORM{'email'},$FORM{'Input1'},$FORM{'Input2'},$FORM{'Input3'},$FORM{'Input4'},$FORM{'Input5'},$FORM{'Input6'},$FORM{'Input7'},$FORM{'Input8'},$FORM{'Input9'},$FORM{'Input10'}')";
$dbh->do($query);

$dbh->disconnect;
}
}

The script is supposed to put the form contents into a mysql db but doesn't do anything.  Threre is no error msg in the log either.  Let me know if further information is needed.
0
rhindo
Asked:
rhindo
  • 9
  • 9
  • 7
  • +1
2 Solutions
 
ahoffmannCommented:
use prepare() and execute() instead of do().
0
 
rhindoAuthor Commented:
Doesn't seem to make a differance
0
 
kanduraCommented:
use placeholders, and specify the column names. and turn RaiseError on:

    $dbh = DBI->connect("dbi:mysql:$mysqlname", $mysqlusername, $mysqlpassword, {RaiseError=>1, PrintError=>0})
                or die "Couldn't connect to database: $DBI::errstr";

    ### field names are my guesses, and I've only done the first two.
    ### note that the 'undef' is necessary: DBI uses the second argument for attributes (which we don't need here).
    $dbh->do( q{ insert into form (email, input1) values(?,?) }, undef, $FORM{'email'}, $FORM{'Input1'});
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
ahoffmannCommented:
does your script work from command line?
0
 
kanduraCommented:
I'm willing to bet it doesn't. First of all, I suspect he's using a very old cgi parsing library ($FORM{foo}). Second, notice the single quotes in the values() part of the query...
0
 
ahoffmannCommented:
good eyes, kandura, should be:

values($FORM{'email'},$FORM{'Input1'},$FORM{'Input2'},$FORM{'Input3'},$FORM{'Input4'},$FORM{'Input5'},$FORM{'Input6'},$FORM{'Input7'},$FORM{'Input8'},$FORM{'Input9'},$FORM{'Input10'})";
0
 
kanduraCommented:
No, I don't think that's a good solution, for several reasons: firstly, it's safe to assume all those columns are text, so they need to be quoted.

    values('$FORM{'email'}', '$FORM{'Input1'}' ...

(Yuck!)

But then you also need to escape quotes inside the values. And of course handle the risk of sql injection.

My placeholders suggestion cures all of these issues :)
0
 
ahoffmannCommented:
.. well, cause the hashes are simple strings we may use:
   '$FORM{Input1}'
should work 'cause they are already in a double quoted string
0
 
rhindoAuthor Commented:
Here is what I have now.  I am unsure on how to use the "undef" part.  What am I doing wrong?


sub mysql {
  {
use DBI;
$dbh = DBI->connect("dbi:mysql:$mysqlname", $mysqlusername, $mysqlpassword, {RaiseError=>1, PrintError=>0})
                or die "Couldn't connect to database: $DBI::errstr";

$query = "INSERT INTO members (email, input1, input2, input3, input4, input5, input6, input7, input8, input9, input10) values('$FORM{'email'}','$FORM{'Input1'}','$FORM{'Input2'}','$FORM{'Input3'}','$FORM{'Input4'}','$FORM{'Input5'}','$FORM{'Input6'}','$FORM{'Input7'}','$FORM{'Input8'}','$FORM{'Input9'}','$FORM{'Input10'}')";
$dbh->do($query);
$dbh->disconnect;
}
}
0
 
ahoffmannCommented:
> '$FORM{'Input1'}'
this does not work for shure! see my previous comment
0
 
kanduraCommented:


    $dbh->do(
        q{
            insert into members
                   (email, input1, input2, input3, input4, input5, input6, input7, input8, input9, input10)
            values (?,     ?,      ?,      ?,      ?,      ?,      ?,      ?,      ?,      ?,      ?      )
        },
        undef,
        $FORM{'email'},
        $FORM{'Input1'},
        $FORM{'Input2'},
        $FORM{'Input3'},
        $FORM{'Input4'},
        $FORM{'Input5'},
        $FORM{'Input6'},
        $FORM{'Input7'},
        $FORM{'Input8'},
        $FORM{'Input9'},
        $FORM{'Input10'}
    );
0
 
kanduraCommented:
rhindo,
> What am I doing wrong?

You didn't read my previous comments well enough ;-)
0
 
rhindoAuthor Commented:
Kandura,
First off, thanks for spending so  much time on this with me.  Secondly, I now have the following script.  It still does not give any error msg, but never updates the db.  Another strange thing, is that I can change the name of the databse and do not recieve a connection error???  Any ideas?

sub mysql {
  {
use DBI;
$dbh = DBI->connect("dbi:mysql:$mysqlname", $mysqlusername, $mysqlpassword, {RaiseError=>1, PrintError=>0})
                or die "Couldn't connect to database: $DBI::errstr";

$dbh->do(
        q{
            insert into members
                   (email, input1, input2, input3, input4, input5, input6, input7, input8, input9, input10)
            values (?,     ?,      ?,      ?,      ?,      ?,      ?,      ?,      ?,      ?,      ?      )
        },
        undef,
        $FORM{'email'},
        $FORM{'Input1'},
        $FORM{'Input2'},
        $FORM{'Input3'},
        $FORM{'Input4'},
        $FORM{'Input5'},
        $FORM{'Input6'},
        $FORM{'Input7'},
        $FORM{'Input8'},
        $FORM{'Input9'},
        $FORM{'Input10'}
    );
$dbh->disconnect;
}
0
 
manav_mathurCommented:
Not sure, but try this

sub mysql {
  {
use DBI;
$dbh = DBI->connect("dbi:mysql:$mysqlname", $mysqlusername, $mysqlpassword, {RaiseError=>1, PrintError=>0})
                or die "Couldn't connect to database: $DBI::errstr";

$dbh->do(
        q{
            insert into members
                   (email, input1, input2, input3, input4, input5, input6, input7, input8, input9, input10)
            values ('?','?','?','?','?','?','?','?','?','?','?')
        },
        undef,
        $FORM{'email'},
        $FORM{'Input1'},
        $FORM{'Input2'},
        $FORM{'Input3'},
        $FORM{'Input4'},
        $FORM{'Input5'},
        $FORM{'Input6'},
        $FORM{'Input7'},
        $FORM{'Input8'},
        $FORM{'Input9'},
        $FORM{'Input10'}
    );
$dbh->disconnect;
}
0
 
rhindoAuthor Commented:
Thanks, but still nothing
0
 
kanduraCommented:
nothing? you should get a syntax error from these two lines:

> sub mysql {
>   {

At least, I don't see the closing brace for that second {. Take it out.

And please report any error messages you _do_ get.
0
 
rhindoAuthor Commented:
Now, I am getting ?s inserted into every field in the database.  I also gettting the following errmsg in the log.

"[Wed Apr 20 15:40:18 2005] [error] [client 24.73.74.66] Premature end of script headers: /home/virtual/site83/fst/var/www/cgi-bin/WebFormPro.cgi
0
 
rhindoAuthor Commented:
I forgot this part:

DBD::mysql::db do failed: Column 'email' cannot be null at WebFormPro.cgi line 65.
0
 
kanduraCommented:
The question marks should *NOT* be quoted!!!
0
 
rhindoAuthor Commented:
not quoted

here is the code:

{
use DBI;
$dbh = DBI->connect("dbi:mysql:$mysqlname", $mysqlusername, $mysqlpassword, {RaiseError=>1, PrintError=>0})
                or die "Couldn't connect to database: $DBI::errstr";

$dbh->do(
        q{
            insert into members
                   (email, Input1, Input2, Input3, Input4, Input5, Input6, Input7, Input8, Input9, Input10)
            values (?,     ?,      ?,      ?,      ?,      ?,      ?,      ?,      ?,      ?,      ?      )

        },
        undef,
        $FORM{'email'},
        $FORM{'Input1'},
        $FORM{'Input2'},
        $FORM{'Input3'},
        $FORM{'Input4'},
        $FORM{'Input5'},
        $FORM{'Input6'},
        $FORM{'Input7'},
        $FORM{'Input8'},
        $FORM{'Input9'},
        $FORM{'Input10'}
    );
$dbh->disconnect;
}
0
 
kanduraCommented:
it was before:

> Now, I am getting ?s inserted into every field in the database.


so what's the problem now?
0
 
rhindoAuthor Commented:
Same problem...

DBD::mysql::db do failed: Column 'email' cannot be null at WebFormPro.cgi line 65.
[Wed Apr 20 16:18:59 2005] [error] [client 24.73.74.66] Premature end of script headers: /home/virtual/site83/fst/var/www/cgi-bin/WebFormPro.cgi
0
 
kanduraCommented:
so there's no value in $FORM{'email'}. Either you didn't enter anything in the email field, or you're doing the form parsing wrong.
0
 
ahoffmannCommented:
rhindo, make your and our live easy and call your script from command line
'cause you're using CGI it will ask you for parameters, just give it all parameters you would enter into the form, something like:

your.cgi
email=user@somewhere.tld
Input1=whatever
...
...

finish input with ^D (Ctrl-D)
when your get error mesages, please post them verbatim
0
 
rhindoAuthor Commented:
Thanks for all the help guys.  I did it in PHP instead.  I wasted enough time on this.
0
 
ahoffmannCommented:
PHP, dooh, hope you don't care about security (or know how to handle it in PHP;-)
Good Luck.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 9
  • 9
  • 7
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now