1

We have a function that executes dynamic SQL (I know, SQL injection, the "Bobby Tables" attack, but that's a separate issue). It takes two parameters, interpolates the first into the second, and returns a boolean.

When we execute it like this:

    SELECT util.dynsql('M','SELECT EXISTS (SELECT 1 FROM x WHERE y = INPUT_VALUE)');

then it works fine. It puts the 'M' in where the INPUT_VALUE is, finds it in table x, and returns true.

If we execute it like this:

    SELECT util.dynsql('M','SELECT EXISTS (SELECT 1 FROM x WHERE y = INPUT_VALUE)')
    FROM some_table limit 1;

then it returns false. some_table can be any table, the thing is that when there's a FROM, it doesn't work.

Any suggestions why? And no, sorry I can't post the code of the dynsql function.

If the second parameter does not contain a SELECT, if it's a comparison or regex, then it works. It seems that a SELECT in dynamic SQL doesn't work in a query from a table, but does work in a query that isn't from a table.

This is on Greenplum, which doesn't have parameterized dynamic SQL, that was introduced in PostgreSQL 8.4 and Greenplum is build on Postgres 8.2.

closed as off-topic by dezso, Max Vernon, RLF, mustaccio, Philᵀᴹ Sep 30 '15 at 11:38

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – dezso, Max Vernon, RLF, mustaccio, Philᵀᴹ
If this question can be reworded to fit the rules in the help center, please edit the question.

  • 5
    without seeing util.dynsql it is going to be next to impossible to know what is happening. – Max Vernon Sep 29 '15 at 15:03
  • 3
    I agree with @MaxVernon. On the other hand, if that mysterious function works as you describe, there are chances it doesn't have to be dynamic at all. I'm voting to close this as too localized. – dezso Sep 29 '15 at 20:26
  • I disagree. If someone happened to know that what I am trying to do is impossible in Greenplum, then it would be obvious from my question. – PhilHibbs Sep 30 '15 at 10:46
  • Can someone clarify the "too localized" for me - is this because Greenplum is not popular here? – PhilHibbs Sep 30 '15 at 14:13
  • 3
    I believe the community voted to close this as too localized because the answer would seem to depend on the operation of a function they had no insight to. So, it was more about the clarity of the question than the popularity or otherwise of Greenplum. – Paul White Oct 1 '15 at 3:19
1

It is failing because the function is executing in the segment* that the row is in, and the segments do not have access to the wider database so it can't select out.

* A segment is a node in a distributed database, like a single server in a cluster of servers. The data is spread out over multiple servers, and in a select from a table, a single server runs the function, but that server doesn't have access to the other servers.

Not the answer you're looking for? Browse other questions tagged or ask your own question.