Forum Stats

  • 3,851,566 Users
  • 2,263,999 Discussions
  • 7,904,781 Comments

Discussions

Update statement based on item values

Doolius
Doolius Member Posts: 115
edited May 7, 2014 9:41PM in APEX Discussions

I am trying to create a page where a user can enter/select values and click submit, and a table is updated with the selected values.

I have 6 items on my page:

TABLE_NAME (popup LOV -4 different table names-)

COLUMN1 (select list based on the table_name value -displays the column names based on what table is selected-)

VALUE1 (textbox)

COLUMN2 (select list based on the table_name value -displays the column names based on what table is selected-)

OPERATOR (operators such as: =, !=, in, not in, >,<,etc)

VALUE2(textbox)

When the user clicks "update" I want the following script to run:

UPDATE :TABLE_NAME
SET :COLUMN1 := :VALUE1
WHERE :COLUMN2 :OPERATOR :VALUE2

I created a DA with the above code but it doesn't compile.

Is there a way to do this?

APEX: 4.0.2

THEME: SAND

SERVER: Oracle-Application-Server-10g/10.1.3.1.0 Oracle-HTTP-Server

Thanks,

Steven

Answers

  • TexasApexDeveloper
    TexasApexDeveloper Member Posts: 7,966 Gold Crown

    You could build an update string and then run it via a dynamic action and execute immediate...

    Elephants wear tu-tus so they can hide in pine trees. Did you ever see an elephant in a pine tree? No? Well then, you know it works.

    Thank you,

    Tony Miller
    LuvMuffin Software
    Ruckersville, VA

  • fac586
    fac586 Senior Technical Architect Member Posts: 21,197 Red Diamond
    Doolius wrote:
    
    I am trying to create a page where a user can enter/select values and click submit, and a table is updated with the selected values.
    I have 6 items on my page:
    TABLE_NAME (popup LOV -4 different table names-)
    COLUMN1 (select list based on the table_name value -displays the column names based on what table is selected-)
    VALUE1 (textbox)
    COLUMN2 (select list based on the table_name value -displays the column names based on what table is selected-)
    OPERATOR (operators such as: =, !=, in, not in, >,<,etc)
    VALUE2(textbox)
    When the user clicks "update" I want the following script to run:
    
    1. UPDATE :TABLE_NAME 
    2. SET :COLUMN1 := :VALUE1 
    3. WHERE :COLUMN2 :OPERATOR :VALUE2 
    UPDATE :TABLE_NAME
    SET :COLUMN1 := :VALUE1
    WHERE :COLUMN2 :OPERATOR :VALUE2
    I created a DA with the above code but it doesn't compile.
    Is there a way to do this?
    
    APEX: 4.0.2
    THEME: SAND
    SERVER: Oracle-Application-Server-10g/10.1.3.1.0 Oracle-HTTP-Server
    

    Eeek! This is the express service to SQL Injection Central!

    What validation do you have in place to prevent this gaping hole into your database being exploited?

    Strongly recommend that you reconsider this approach as it is a security nightmare.

  • Mike Kutz
    Mike Kutz Member Posts: 6,199 Silver Crown

    From a database standpoint, what you want to do is a horrible idea.  You can try asking over in to see what they think.

    You're better off teaching people how to write proper UPDATE statements.

    actual information

    Things like TABLE NAME, COLUMN NAME, and OPERATOR can not be 'bind' variables.

    (again, this is a database thing, not an apex thing)

    If you still want to do that, what you desire is called "dynamic sql".

    You'll need to use DBMS_ASSERT to ensure Bobby Tables doesn't screw you over (ie SQL Injection)

    Then, you'll want to use DBMS_SQL so that you can 'parse' the actual SQL.. (not just 'BIND' it)

    Again, you're better off asking people in the forum as what you want to do is a database thing, not an APEX thing.

    MK

    ps - for your own safety, I'm not telling you how to do it.

    Mike Kutz
  • Doolius
    Doolius Member Posts: 115

    Thanks for the info.

    As far as the SQL Injection goes, I know this is a bad way to do it.

    I was hoping that because most likely the only person who would be using/able to access this page would be the Project Manager, so it's less likely that he will want to/have the knowledge to screw up the database because it would screw up the whole project.

    I think I'll be better off making this page like the other update pages that have javascript validation before the table is updated. I just thought this might be a quick/easy way for the PM to update the records he wants.

    Steven

This discussion has been closed.