Create on-the-fly query 1

[ssmith001]

I have a need to create a query that will look like this:

SELECT DISTINCT a.item
FROM stsc.sku a
, stsc.item b
, stsc.loc c
WHERE a.p_abc = 'A'
AND b.p_stkclass IN ('010', '010A')
AND a.item = b.item
AND a.loc = c.loc
AND c.p_dlrgrp = 'MHC'

but the problem is that I could have many values for p_stkclass and many values for p_dlrgrp. Each time the user runs the code they can enter as many parameters as they want and I need to build, run, and return the results. Is there a way to dynamically build this query on the fly?

 

[KevinADC]

make your query string a variable and inside the query string substitute variables when and where needed:

my $query = qq~SELECT DISTINCT a.item
FROM stsc.sku a
   , stsc.item b
   , stsc.loc c
WHERE a.p_abc = 'A'
AND b.p_stkclass IN ([b]$foo[/b])
AND a.item = b.item
AND a.loc = c.loc
AND c.p_dlrgrp = '[b]$bar[/b]'~;

then use $query whereever you need in your script.

 

[stevexff]

Both $foo and $bar provide an opportunity for SQL injection. Maybe do it in two steps - build up a statement with the correct number of ? placeholders, prepare it, then execute it passing in the values as parameters. This requires a bit more work, but will protect you from script kiddies.

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[KevinADC]

good points by steveff

 

[ssmith001]

Pardon me, but I'm not sure I understand the difference between your two approaches. Kevin, in your approach, I basically read the desired parameters into a list and build a string called "foo" using your example. Then I call the query feeding it "foo" as the input. Is this correct? I don't understand approach 2.

 

[stevexff]

The difference is that in the first example a malicious person could supply "); DROP TABLE p_dlrgrp;" or something similar as a 'search parameter', and it would get built into the statement string and executed.

In the second, the structure of the statement is fully under your control, and the worst that can happen is that you get a 'not found' back from your SQL call.

Google 'SQL injection' for about a zillion reasons why you should protect yourself in this way.

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]