On Jan 11, 2008 6:47 PM, Chris Charley <[EMAIL PROTECTED]> wrote: > > See this colimn by Randal L. Schwartz. It descibes injection attacks. > http://www.stonehenge.com/merlyn/UnixReview/col58.html > > Thanks, I read the page, alongside the wikipedia page and I think I understand what an sql injection is. My code follows. The first section is for a text suggestion drop-down menu - it produces a selection based on the current user input in a text box and runs a DBI query to get suggestions for a user. The details are passed by a javascript routine to catalyst.
sub _build_suggestion_list { my ($self, $c) = @_; my $table_and_col_name = $c->req->params->{column_name}; my $current_text = $c->req->params->{text_input}; my @well_column_names = @{_get_column_names($c,"T1")}; my @gi_column_names = @{_get_column_names($c,"T2")}; my %genuine_columns = (); for (@gi_column_names) { $genuine_columns{${$_}[0]} = 1; } for (@well_column_names) { $genuine_columns{${$_}[0]} = 1; } #I dont think I need the {1,} if ($current_text =~ /\:{1,}|\--{1,}|\#{1,}|\;|insert|drop|create|^$/ig) { my $tc = "<ul><li>Invalid Input (: ; --; #)</li></ul>"; $c->res->body($tc); } else { if ( exists $genuine_columns{$table_and_col_name}) { my ($table, $column) = split /\./, $table_and_col_name; my $model = $c->model(q(DB)); my $dbh = $model->storage->dbh; my $select = ("select distinct $table\.$column from $table where $table\.$column LIKE \'\%$current_text\%\'"); my $sql_exe = $dbh->prepare($select); $sql_exe->execute(); my $results = $sql_exe->fetchall_arrayref(); my $options = "<ul>"; for (@$results) { $options .= "<li>${$_}[0]</li>"; } $options .= "</ul>"; $c->res->body($options); } else { my $tc = "<ul><li>The column names are invalid - please see admin</li></ul>"; $c->res->body($tc); } } } The next section contains the query constructor. This takes all the user input from multiple rows (users can create as many as they want). I have limited the options but this, i think, doesn't help because someone could craft their own post and submit that. The query is run in another subroutine which returns the data to the template toolkit end of things. sub _query : Local { my ( $self, $c ) = @_; my $href_params = ($c->request->parameters()); delete $$href_params{'button'}; my @well_column_names = @{_get_column_names($c,"T1")}; my @gi_column_names = @{_get_column_names($c,"T2")}; my %genuine_columns = (); for (@gi_column_names) { $genuine_columns{${$_}[0]} = 1; } for (@well_column_names) { $genuine_columns{${$_}[0]} = 1; } #Are the columns the ones that they should be? @{$$href_params{'select_these'}} = grep { $genuine_columns{$_} } @{$$href_params{'select_these'}}; my $select = ""; if ( ref($$href_params{'select_these'}) =~ 'ARRAY' or ref($$href_params{'select_these'}) =~ 'REF') { if ( @{$$href_params{'select_these'}} == 0 ) { $select = " T2.y ";} else { $select = join(" ,", @{$$href_params{'select_these'}}); } } else { if ($$href_params{'select_these'} eq "") { $select = "T1.x"; } else { if ( exists $genuine_columns{$$href_params{'select_these'}}) { $select = $$href_params{'select_these'}; } else { $select = "T1.x"; } } } #Two blocks - one for where there are multiple user conditions and the other for singles my $conditional = ""; if ( ref($$href_params{'conditional_type'}) =~ 'ARRAY' or ref($$href_params{'conditional_type'}) =~ 'REF') { for (my $i = 0; $i < @{$$href_params{'conditional_type'}}; $i++) { $conditional .= ${$$href_params{'conditional_type'}}[$i] . ' '; $conditional .= ${$$href_params{'select_column_name'}}[$i] . ' '; #Check for foul play if (${$$href_params{'user_text'}}[$i] =~ /\;|:|--|#|insert|drop|create|\'|\"|null/ig ) { ${$$href_params{'user_text'}}[$i] = "null"; #This can't be 'null' } if ( ${$$href_params{'case_option'}}[$i] =~ /^like$/ig ) { $conditional .= "${$$href_params{'case_option'}}[$i] '\%${$$href_params{'user_text'}}[$i]\%'" . ' '; } else { $conditional .= ${$$href_params{'case_option'}}[$i] . ' '; $conditional .= ${$$href_params{'user_text'}}[$i] . ' '; } } } else { if ($$href_params{'user_text'} =~ /\;|:|--|#|insert|drop|create|\'|\"|null/ig ) { $$href_params{'user_text'} = "null"; } if ($$href_params{'case_option'} =~ /like/ig) { $$href_params{'user_text'} = "'\%$$href_params{'user_text'}\%' "; } $conditional = $$href_params{'conditional_type'} . " " . $$href_params{'select_column_name'} . " " . $$href_params{'case_option'} . " " . $$href_params{'user_text'}; } _construct_query($self, $c, \$select, \$conditional); #pass to Catalyst stash + template } > > -- > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > http://learn.perl.org/ > > >