Professional Web Applications Themes

Is my DB code bad? - PERL Beginners

Hi all, I have a web page I am working on to delete an entry out of a database. If I take the syntax in the perl script and paste it into a mysql query, and substitute the variable for a value that exists in the database, it will delete the entry. If I run it thru the web page, I dont get an error, it says it worked ok and I get no errors. The remove subroutine calls the removemac subroutine. Here is the code: --- CODE --- sub remove { my $delmac; print "<h3 align=center> Delete MAC </h3>"; ...

  1. #1

    Default Is my DB code bad?

    Hi all,

    I have a web page I am working on to delete an entry out of a database. If I take the syntax in the perl script and paste it into a mysql query, and substitute the variable for a value that exists in the database, it will delete the entry. If I run it thru the web page, I dont get an error, it says it worked ok and I get no errors. The remove subroutine calls the removemac subroutine. Here is the code:

    --- CODE ---

    sub remove {
    my $delmac;

    print "<h3 align=center> Delete MAC </h3>";

    print "<h4 align=center> Careful! Make sure you know what you are doing!
    </h4>";

    return <<FRM;
    <center>
    <form action="$self" method="post">
    <input type="hidden" name="action" value="removemac">
    MAC Address to remove: <input type="text" name="delmac">
    <input type="submit" value="Delete MAC">
    </form>
    </center>
    FRM

    }

    sub removemac {
    my $user = param('delmac');

    print $user;

    my $query = "DELETE FROM passwd WHERE user_name=$user LIMIT 1";

    my $sth = $dbh->prepare($query) || &db_err("Cannot prepare $query <P> \n
    :" . $dbh->errstr . "\n");

    $sth->execute || &db_err("Cannot execute $query <P> \n:" . $dbh->errstr
    .. "\n");

    $sth->finish;

    print "<h3 align=center> Deleted customer! </h3>";

    print "<center<a href\"" . $self . "?action=\"> Go Back </a>";

    }

    --- CODE ---

    Any help is appreciated.

    Thanks,

    Dave Kettmann
    NetLogic
    636-561-0680
    Dave Guest

  2. #2

    Default Re: Is my DB code bad?

    > Hi all, 
    database. If I take the syntax in the perl script and paste it into a
    mysql query, and substitute the variable for a value that exists in the
    database, it will delete the entry. If I run it thru the web page, I
    dont get an error, it says it worked ok and I get no errors. The remove
    subroutine calls the removemac subroutine. Here is the code: 

    You didn't really state what the problem is, I assume the entry is not
    actually deleted? Generally when we get no errors we are happy :-).
     

    What is $delmac doing? Where does $self below come from? I suspect you
    have broken encapsulation, which can be on purpose (I suppose).
     
    are doing! 

    You said in your description that 'remove' calls 'removemac'... why?
    and more importantly *where*??
     

    Many DBs require you to quote non-integer values, is $user an integer
    value, you have asked for MAC addresses (are there colons in the
    value?). The above is better written using DBI's binding capability.
    Switch C<$user> to C<?> and add $user as an argument to C<execute> below.
     
    $query <P> \n 
    $dbh->errstr 

    Not necessarily, as you are finding. You should probably check that a
    row has been deleted.
     

    http://danconia.org

    Wiggins Guest

  3. #3

    Default Re: Is my DB code bad?

    From: "Dave Kettmann" <net> 

    Wow. Where's this so that I can delete your database? ;-)

    Let me ask you something. what do you think happens if someone enters
    something like this into the delmac inputbox:

    1; delete from passwd;

    Well quite likely you get your table wiped out.
    You need to be carefull with stuff you put into SQL!


    1) You need to validate the data you get from the outside world.
    2) you need to enclose strings in singlequotes in SQL:

    DELETE FROM passwd WHERE user_name='$user' LIMIT 1

    3) You need to make sure special characters (if your validation
    allowed them are properly quoted/escaped:

    my $query = "DELETE FROM passwd WHERE user_name='"
    . $dbh->quote($user) . "' LIMIT 1";

    but even better is to not interpolate the data into the query at all
    and use placeholders:

    my $query = "DELETE FROM passwd WHERE user_name = ? LIMIT 1";
    my $sth = $dbh->prepare($query)
    || &db_err("Cannot prepare $query <P> \n:" . $dbh->errstr . "\n");

    $sth->execute($user)
    || &db_err("Cannot execute $query <P> \n:" . $dbh->errstr . "\n");

    Jenda
    ===== cz === http://Jenda.Krynicky.cz =====
    When it comes to wine, women and song, wizards are allowed
    to get drunk and croon as much as they like.
    -- Terry Pratchett in Sourcery

    Jenda Guest

  4. #4

    Default RE: Is my DB code bad?

    First off, Thanks to Jenda and Wiggins for their quick response. I have found the answer to my question in Jenda's help (the missing "'"'s)

    Sorry for not being more specific earlier it has been a hectic day here :)

    This page is on a internal server that 3 people have access to. (myself and 2 others) Making this ultra secure is not a big deal at this point. The way this is built is there is one cgi script (radius.cgi) that all this is in, and they are reference by subroutine via the 'action' parameter. The $self variable is a variable that points back to this script (so $self?action=remove would get me to http://<insert_url_here>/radius.cgi?action=remove ) Is easier to use a variable like that than to type out the other. There may be better ways to do that but, hey, this works and im a perl beginner :).

    That being said (even though it may not have been necessary), can I get a link to a good description of the object oriented syntax if one exists? I know I can do a perldoc and find what it does and how to use it, but the variable names confuse me. (i.e.: $sth etc) I'm sure I could understand it alot better if someone (or a webpage) could explain it to me, the doentation just tells you how to use it, but I need to know what it means :).

    Thanks ahead of time.

    Dave Kettmann
    NetLogic
    636-561-0680

     
    > it into a 
    > the web page, 
    > know what you 
    > value="removemac"> MAC 
    >
    > Wow. Where's this so that I can delete your database? ;-)
    >
    > Let me ask you something. what do you think happens if someone enters
    > something like this into the delmac inputbox:
    >
    > 1; delete from passwd;
    >
    > Well quite likely you get your table wiped out.
    > You need to be carefull with stuff you put into SQL!
    >
    >
    > 1) You need to validate the data you get from the outside world.
    > 2) you need to enclose strings in singlequotes in SQL:
    >
    > DELETE FROM passwd WHERE user_name='$user' LIMIT 1
    >
    > 3) You need to make sure special characters (if your validation
    > allowed them are properly quoted/escaped:
    >
    > my $query = "DELETE FROM passwd WHERE user_name='"
    > . $dbh->quote($user) . "' LIMIT 1";
    >
    > but even better is to not interpolate the data into the query at all
    > and use placeholders:
    >
    > my $query = "DELETE FROM passwd WHERE user_name = ? LIMIT 1";
    > my $sth = $dbh->prepare($query)
    > || &db_err("Cannot prepare $query <P> \n:" .
    > $dbh->errstr . "\n");
    >
    > $sth->execute($user)
    > || &db_err("Cannot execute $query <P> \n:" .
    > $dbh->errstr . "\n");
    >
    > Jenda
    > ===== cz === http://Jenda.Krynicky.cz =====
    > When it comes to wine, women and song, wizards are allowed
    > to get drunk and croon as much as they like.
    > -- Terry Pratchett in Sourcery
    >
    >
    > --
    > To unsubscribe, e-mail: org
    > For additional commands, e-mail: org
    > <http://learn.perl.org/> <http://learn.perl.org/first-response>
    >
    >
    > [/ref]
    Dave Guest

  5. #5

    Default RE : Is my DB code bad?


     
    have 
    here :) 
    (myself 
    point. 
    ways to 
    get a 
    exists? I 
    the 
    understand it 

    Just a hint about dbi variables naming:
    $sth : st(statement) h(handler)
    $dbh : db(database) h(handler)

    Give a look to Fig4-3: DBI handles
    at http://www.oreilly.com/catalog/perldbi/chapter/ch04.html
    you will get a good overview about those handlers.

    José.

    Jose Guest

  6. #6

    Default Re: Is my DB code bad?

    On Wed, 1 Sep 2004 15:42:16 -0500, Dave Kettmann <net> wrote: 

    I strongly suggest you take Jenda's advice about using placeholders
    instead. Say the value for $user is:
    ' OR user_name LIKE '%'--

    My SQL may be a little off, but in general, this or something like it
    could then change your delete statement to delete everything from your
    table. Even if only a few people are accessing it, there is always
    the possibility that one of them will enter a single quote into the
    text box and cause unexpected behavior.

    I'll stop my rant here. Use this information as you wish.

    -David
    David Guest

Similar Threads

  1. Why doesn't the Code Completion occur in FlexBuilder IDEwhen source code is in an external file?
    By DuffyVector123 in forum Macromedia Flex General Discussion
    Replies: 0
    Last Post: May 25th, 01:39 PM
  2. Custom control fires event but ignores some code in the code behind file
    By recoil@community.nospam in forum ASP.NET Building Controls
    Replies: 0
    Last Post: March 8th, 05:17 PM
  3. Replies: 0
    Last Post: February 9th, 05:26 PM
  4. [PHP] Wrapping code inside [code][/code] tags.
    By Php in forum PHP Development
    Replies: 2
    Last Post: October 6th, 09:34 AM
  5. Replies: 0
    Last Post: July 11th, 06:27 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139