Re: How to iterate through database tables
On Tue, Feb 3, 2009 at 5:47 AM, Deviloper wrote:
>
> But some bad guy could showed up and force the poor developer not to use
> perl-vars in SQL-Statements for security reasons.
>
> Is ist possible to use tablenames like normal bind-variables?
> Is there a better way to solve this problem?
Using perl vars is ok as long as you untaint them (you are using
taint checking in programs that use untrusted user input, right?).
This can be as easy as:
unless ( $table =~ /^(\w+)$/ ) {
die "Bad table: $table";
}
$table = $1;
If only "trusted" users are using the program (e.g. in non-suid
command line utilities), then I don't worry so much about pasting
variables into SQL.
Re: How to iterate through database tables
I think his issue is getting list to the server side without concatenating strings. You can't use bind variables as table names or column names. You may be able to write a stored procedure which takes a table name as a variable, does the string concatenation on the server side, and uses Oracle's "execute immediate" to effectively "eval" the SQL there.. Scott Dale wrote: On Tue, Feb 3, 2009 at 8:47 AM, Deviloper wrote: Hello there! lets assume that one has a list of tables @db_tables. (For Example one per Month) Lets assume one wants to find some data in the tables. (For Example sold Items.) select sales from $db_tables[0]; # gives all sold items in January You could use a UNION in the database to combine the common data (you can create a view and use that for your select): CREATE VIEW v_combined_months AS SELECT 'January' AS "Month",sales FROM jan_sales UNION ALL SELECT 'February' AS "Month",sales FROM feb_sales UNION ALL SELECT 'March' AS "Month",sales FROM march_sales [...]
Re: How to iterate through database tables
On Tue, Feb 3, 2009 at 8:47 AM, Deviloper wrote: > Hello there! > > lets assume that one has a list of tables @db_tables. (For Example one per > Month) > Lets assume one wants to find some data in the tables. (For Example sold > Items.) > > select sales from $db_tables[0]; # gives all sold items in January > You could use a UNION in the database to combine the common data (you can create a view and use that for your select): CREATE VIEW v_combined_months AS SELECT 'January' AS "Month",sales FROM jan_sales UNION ALL SELECT 'February' AS "Month",sales FROM feb_sales UNION ALL SELECT 'March' AS "Month",sales FROM march_sales [...] -- pDale
