How not to execute Dynamic sql usaar immediate

Dúvidas, dicas e truques de PL/SQL. Aqui também vão assuntos relacionados a pacotes, triggers, funções, Java-Stored Procedures, etc

Poston Fri, 20 Nov 2009 8:42 am

I need to execute a dynamic sql but the customer prefers that the execute immediate ausado sej has another way to do? a deletion in the dynamic sql? I used the ref course for other queries, but to do the delete I don't know how to do ... does anyone have any idea???

Location: São José - SC

Poston Fri, 20 Nov 2009 12:05 pm

For queries have the REFCURSOR and EXECUTE IMMEDIATE to other actions, otherwise desconheco to dynamically treat ....
Location: Recife

Poston Fri, 20 Nov 2009 12:06 pm

Has such a workaround that can activate the spool, causing the result to be spooled sql queries generating a .SQL file with the instructions of the deletions.
Location: Recife

Poston Fri, 20 Nov 2009 9:53 pm

To select can use cursor variable (sys ref_cursor) but to delete you have to be in the immediate run.

The big problem of delete with execute immediate is that you have to do it by concatenating strings, and this opens a hole in security for SQL injection vulnerabilities, mainly using procedures, because they by default use privileges of the OWNER of the procedure (the user who acompilou), and which is usually superprivilegiado.

The way is to validate all that is past and concatenated to ensure that there is no trash or a malicious string inside. Is dullsville even, or using various IFs or using the aid of DBMS_ASSERT package.
Alternatively, depending on what you want to do, use invoker's rights by placing the AUTHID clause CURRENT_USER in your procedure. This will ensure that the user running the procedure run it with their own privileges, but sometimes you need to use definer's rights (default behavior).

Take a look at the link below, where Tom Kyte explains how to defend against SQL Injection. As he himself says:
\"It is not dynamic sql that is the issue. It is \"the construction\" of this sql that is the problem. 11_QUESTION_ID: 23863706595353
Location: Gaúcho no Rio de Janeiro - RJ

"The scars exist to remind us that the past was real"
Campanha: Como fazer uma pergunta e obter uma resposta. ... tions.html

OCA & OCP Developer — OCE SQL Expert — OCS Data Warehousing Specialist

  • See also
    Last Post

Return to PL/SQL

Who is online

Users browsing this forum: No registered users and 4 guests