CREATE NEW USER

start of the data

about to open the DB
SQL ERROR: Bad table or column name '\?QI0?' has chars not alphanumeri
+c or underscore! DBD::AnyData::db prepare failed: SQL ERROR: Bad tabl
+e or column name '\?QI0?' has chars not alphanumeric or underscore! 

result = 

about to insert the new record
[download]

#!/usr/bin/perl -w

# ---------------------------------------------
# create-user.cgi   - Create by Lance Wicks
#                       e-judo.sourceforge.net
# This is free open source software! Released under GPL
#
# Description:
# This script adds a new user to the users database
#
# History:
# ========
# 20 December 2003, Lance Wicks - Created initial file.
#


use strict; # force strict programming controls
use CGI qw(:standard); # use the CGI.PM module
use lib './MyLib'; # use the modules in MyLib, this is the DBD::Anydat
+a used for database activities
use DBI; # This calls the DBI module, which along with the line above 
+allows us to do database activities

print header(), start_html("e-Judo Test Area"), h1("CREATE NEW USER");
+ # This line uses CGI.PM to to create the webpage

if (param()){  # If there is a parameter(or parameters) then validate,
+ else show the login screen.
# the following lines are excecuted if paramaters HAVE been entered
             print p("start of the data");
             my $ID = param("ID"); # the next few lines alocate the in
+fo from the completed form to variables.
             my $first_name = param("first_name");
             my $surname = param("surname");
             my $date_of_birth = param("date_of_borth");
             my $email = param("email");
             my $password = param("ejudopass");
             # now give values to the other user data field variables
             my $active = "NO";   # the user is not immediately active
+, we will email them first.
             my $last_login = "now"; # set the last login time to now
             my $create_date = "now"; # set the date we created this u
+ser to now as well
             my $earnings = 0; # They have not earned any thing yet so
+ set it to Zero
             my $cash = 50; # This is their cash on hand, lets give th
+em 50 credits by default
             my $judoka_limit = 1; # They can create 1 Judoka
             my $sensei_limit = 0; # They can NOT create a sensei (YET
+)
             my $dojo_limit = 0; # They can not create a Dojo
             my $team_limit = 0; # They can not create a team
             my $rank = "Novice"; # They are a novice user to start wi
+th

             # Add in some data validation here!!!!

             # Next connect to the database and check if they exist al
+ready.
             print p("about to open the DB");
             my $dbh = DBI->connect('dbi:AnyData(RaiseError=>1):'); # 
+tell DBI we want to use the Anydata module in ./MyLibs
             $dbh->func( 'users', 'CSV', 'data/users.csv', 'ad_catalog
+'); # Connect to the users.csv data file
             my $sth = $dbh->prepare("SELECT id FROM users WHERE id = 
+'$ID'"); # select the ID field from the database for the ID enetered 
+by the user
             $sth->execute(); # excecute the select command above
             my @result = $sth->fetchrow_array; # this line takes the 
+results of the select and puts it in the array called RESULTS
             $dbh->disconnect(); # we are done with the datbase for no
+w, so disconnect from it (MAY NOT BE NECESSARY)
             print p("result = @result");
             if (@result) {    #if the result array is in existence (i
+e we found the username) then...
                                    print p("Sorry this ID is in use a
+lready");
                                    exit;
             } else {
                        print p("about to insert the new record");
                        # if the user does not exist then add them!
                        # so connect to the database and insert a reco
+rd
                        my $dbh = DBI->connect('dbi:AnyData(RaiseError
+=>1):'); # tell DBI we want to use the Anydata module in ./MyLibs
                        $dbh->func( 'users', 'CSV', 'data/users.csv', 
+'ad_catalog'); # Connect to the users.csv data file
                          my $sth = $dbh->prepare('INSERT INTO users V
+ALUES ( \"
                          $ID,
                           $first_name,
                            $surname,
                             $date_of_birth,
                              $email,
                               $password,
                                $active,
                                 $last_login,
                                  $create_date,
                                   $earnings,
                                    $cash,
                                     $judoka_limit,
                                      $sensei_limit,
                                       $dojo_limit,
                                        $team_limit,
                                         $rank \" ) ');
                          $sth->execute(); # excecute the command abov
+e
 #                         $dbh->commit();
                          $dbh->disconnect();
                          print p("Inserted Record");
             }



} else {
# The following lines are excecuted only if no parameters have been en
+tered. (Ie when you first arrive)
           print hr, start_form;          # create a form using CGI.PM
        print p("User ID: ", textfield("ID"), " - This is the ID you w
+ill use to login to the system");        # what username do they want
+.
        print p("First Name: ", textfield("first_name"), " - This is y
+our REAL name");        # what is their real name
        print p("Surname: ", textfield("surname"), " - This is your RE
+AL last name");        # what is their real name
        print p("Date of Birth: ", textfield("date_of_birth"), " - (DD
+/MM/YYYY) This is your date of birth, used if you ever need to prove 
+who you are");
        print p("Email address: ", textfield("email"), " - the email a
+ddress you would like to use.");
           print p("Password: ", password_field("ejudopass"), " - Choo
+se a password to use on the system");
           print end_form, hr;                                        
+# end the form
}
print end_html; # this closes the web page properly
[download]

Hi Lance, I'd recommend that you separate the CGI stuff from the DBI stuff so that you can debug them separately. Make a get_data_from_CGI() method and get_data_from_constants() method that both return a list of values, then feed that list to a do_db_stuff_with_values() mehtod. Make sure it works with the constants ( e.g. $ID = '999' ), then you'll know your DBI stuff is working and you can then check it from CGI input.

My strong suspicion is that you're not getting a $ID value from the form and that you are essentially trying to insert '' into the database which (because of a bug) produces the odd error message you got.

The other thing that is tripping you up is the quoting in SQL. A SQL insert looks something like this: INSERT INTO foo (first_col,second_col) VALUES ('string',7). Notice that string has single quotes around it and the number does not. It works the same way with variables - INSERT INTO foo (first_col,second_col) VALUES ( '$string', $number ). Your INSERT statement has no quotes around anything. The best solution is to never have to care about the quotes: USE PLACEHOLDERS:

    my $sth = $dbh->prepare("
        INSERT INTO foo (first_col,second_col) VALUES (?,?)
    ");
    $sth->execute( $string, $number );
[download]

No quotes, no muss, no fuss. See the DBI docs on placeholders.

Another good way to catch problems is to always (when debugging) print out the SQL before executing it:

    my $DEBUG = 1; # comment out when not debugging
    my $sql = "INSERT INTO foo (first_col,second_col) VALUES (?,?)";
    my @params = ($string,$number);
    print "$sql\n[@params]\n" if $DEBUG;
    my $sth = $dbh->prepare( $sql );
    $sth->execute( @params );
[download]

DBI::trace() will also show you that same information, eventually you might want to check it out (see the DBI docs).

Hopefully all that is enough for you to be able to debug your script. If not, holler.

Hi Jeff

Cheers for another useful reply.
As is obvious I am a newbie PERL coder, so my work is a bit ropey. Never used DBI or done stuff with SQL really, so the sum of it all is just plain scary!

Anyway, I shall take what you have kindly written and see what sort of mess I can create. Watch this space!

Lance

P.s. If you think my code at the moment is bad, you should have seen it before I started from scratch! :-)

Kia Kaha, Kia Toa, Kia Manawanui!
Be Strong, Be Brave, Be perservering!

Just a small addition to this.
I have added a line that shows me the parameters returned from param()

It just lists the fields from the CGI form as opposed to the values.

If someone can help on this I'd appreciate it.

Lance