[function] PostgreSQL 함수 매개 변수로서의 테이블 이름

Program / 글쓴이

Postgres 함수의 매개 변수로 테이블 이름을 전달하고 싶습니다. 이 코드를 시도했습니다.

CREATE OR REPLACE FUNCTION some_f(param character varying) RETURNS integer
AS $$
    BEGIN
    IF EXISTS (select * from quote_ident($1) where quote_ident($1).id=1) THEN
     return 1;
    END IF;
    return 0;
    END;
$$ LANGUAGE plpgsql;

select some_f('table_name');

그리고 나는 이것을 얻었다 :

ERROR:  syntax error at or near "."
LINE 4: ...elect * from quote_ident($1) where quote_ident($1).id=1)...
                                                             ^

********** Error **********

ERROR: syntax error at or near "."

그리고 이것으로 변경했을 때 얻은 오류는 다음과 같습니다 select * from quote_ident($1) tab where tab.id=1.

ERROR:  column tab.id does not exist
LINE 1: ...T EXISTS (select * from quote_ident($1) tab where tab.id...

아마도 내가 얻는 부분이 quote_ident($1)없으면 무언가가 선택되었음을 의미 하기 때문에 작동합니다 . 첫 번째 작업과 두 번째 작업이 동시에 작동하지 않는 이유는 무엇 입니까? 그리고 이것은 어떻게 해결할 수 있습니까?where quote_ident($1).id=11quote_ident($1)

답변

이것은 더욱 단순화되고 개선 될 수 있습니다.

CREATE OR REPLACE FUNCTION some_f(_tbl regclass, OUT result integer)
    LANGUAGE plpgsql AS
$func$
BEGIN
   EXECUTE format('SELECT (EXISTS (SELECT FROM %s WHERE id = 1))::int', _tbl)
   INTO result;
END
$func$;

스키마 규정 이름으로 호출 (아래 참조) :

SELECT some_f('myschema.mytable');  -- would fail with quote_ident()

또는:

SELECT some_f('"my very uncommon table name"');

주요 포인트

  • 사용하여 OUT매개 변수 기능을 단순화 할 수 있습니다. 동적 SQL의 결과를 직접 선택하여 수행 할 수 있습니다. 추가 변수 및 코드가 필요하지 않습니다.

  • EXISTS원하는 것을 정확히 수행합니다. true행이 존재하는지 여부를 얻습니다 false. 이를 수행하는 다양한 방법이 EXISTS있으며 일반적으로 가장 효율적입니다.

  • 정수를 되돌리고 싶은 것 같으므로 boolean결과를에서 EXISTS로 캐스팅하여 integer정확히 얻은 결과 를 얻습니다. 대신 부울 을 반환 합니다.

  • 개체 식별자 유형 regclass을에 대한 입력 유형으로 사용합니다 _tbl. 그것은 모든 것을 수행 quote_ident(_tbl)하거나 format('%I', _tbl)할 것입니다.

  • .. SQL 주입도 방지 합니다.

  • .. 테이블 이름이 유효하지 않거나 존재하지 않거나 현재 사용자에게 표시되지 않는 경우 즉시 실패합니다. ( regclass매개 변수는 기존 테이블 에만 적용됩니다 .)

  • .. 스키마 규정 테이블 이름과 함께 작동합니다. 여기서 일반 quote_ident(_tbl)또는 format(%I)모호성을 해결할 수 없기 때문에 실패합니다. 스키마와 테이블 이름을 별도로 전달하고 이스케이프해야합니다.

  • format()구문을 단순화하고 사용 방법을 보여주기 위해 여전히를 사용 하지만 %s대신 %I. 일반적으로 쿼리는 더 복잡하므로 format()더 많은 도움이됩니다. 간단한 예에서는 다음과 같이 연결할 수 있습니다.

      EXECUTE 'SELECT (EXISTS (SELECT FROM ' || _tbl || ' WHERE id = 1))::int'

  • 목록에 id단일 테이블 만있는 동안 열 을 테이블 한정 할 필요가 없습니다 FROM. 이 예에서는 모호성이 없습니다. (동적) 내부 SQL 명령 EXECUTE에는 별도의 범위가 있으며, 함수 변수 또는 매개 변수는 함수 본문의 일반 SQL 명령과 달리 여기에서 표시되지 않습니다.

다음 은 동적 SQL에 대한 사용자 입력을 항상 적절하게 이스케이프하는 이유입니다 .

DB <> 바이올린 여기 시연 SQL 주입
올드 sqlfiddle

답변

가능하다면 이렇게하지 마십시오.

그것이 답입니다. 그것은 반 패턴입니다. 클라이언트가 데이터를 원하는 테이블을 알고 있으면 SELECT FROM ThatTable. 데이터베이스가 이것이 필요한 방식으로 설계 되었다면 차선책으로 설계된 것 같습니다. 데이터 액세스 계층이 테이블에 값이 있는지 여부를 알아야하는 경우 해당 코드에서 SQL을 작성하기 쉽고이 코드를 데이터베이스로 푸시하는 것은 좋지 않습니다.

나에게 이것은 원하는 층의 번호를 입력 할 수있는 엘리베이터 내부에 장치를 설치하는 것처럼 보입니다. Go 버튼을 누른 후 원하는 층의 올바른 버튼으로 기계식 손을 이동하고 누릅니다. 이로 인해 많은 잠재적 인 문제가 발생합니다.

참고 : 여기에는 조롱 할 의도가 없습니다. 저의 어리석은 엘리베이터의 예는이 기술의 문제를 간결하게 지적 할 수있는 * 내가 상상할 수있는 최고의 장치 *였습니다. 쓸모없는 간접 계층을 추가하여 호출자 공간 (튼튼하고 잘 이해 된 DSL, SQL 사용)에서 테이블 이름 선택을 모호하고 기괴한 서버 측 SQL 코드를 사용하여 하이브리드로 이동합니다.

쿼리 구성 논리를 동적 SQL로 이동하여 책임을 분할하면 코드를 이해하기가 더 어려워집니다. 오류 가능성이있는 사용자 지정 코드의 이름에서 표준적이고 신뢰할 수있는 규칙 (SQL 쿼리가 선택할 항목을 선택하는 방법)을 위반합니다.

다음은이 접근 방식의 몇 가지 잠재적 인 문제에 대한 자세한 내용입니다.

  • 동적 SQL은 프런트 엔드 코드 또는 백 엔드 코드만으로 인식하기 어려운 SQL 삽입 가능성을 제공합니다 (이를 확인하려면 함께 검사해야합니다).

  • 저장 프로 시저 및 함수는 SP / 함수 소유자가 권한을 가지고 있지만 호출자는 권한이없는 리소스에 액세스 할 수 있습니다. 내가 이해하는 한, 특별한주의없이 기본적으로 동적 SQL을 생성하고 실행하는 코드를 사용할 때 데이터베이스는 호출자의 권한에 따라 동적 SQL을 실행합니다. 즉, 권한있는 개체를 전혀 사용할 수 없거나 모든 클라이언트에 공개해야하므로 권한있는 데이터에 대한 잠재적 공격의 표면 영역이 증가합니다. 생성시 SP / 함수를 항상 특정 사용자 (SQL Server에서 EXECUTE AS) 로 실행하도록 설정하면 이 문제를 해결할 수 있지만 상황이 더 복잡해집니다. 이는 동적 SQL을 매우 매력적인 공격 벡터로 만들어 이전 지점에서 언급 한 SQL 주입의 위험을 악화시킵니다.

  • 개발자가 애플리케이션 코드를 수정하거나 버그를 수정하기 위해 수행하는 작업을 이해해야 할 때 실행되는 정확한 SQL 쿼리를 얻는 것이 매우 어렵습니다. SQL 프로파일 러를 사용할 수 있지만 이는 특별한 권한을 필요로하며 프로덕션 시스템에 부정적인 성능 영향을 미칠 수 있습니다. 실행 된 쿼리는 SP에 의해 기록 될 수 있지만 이로 인해 의심스러운 이점 (새 테이블 수용, 오래된 데이터 제거 등)에 대한 복잡성이 증가하고 명확하지 않습니다. 실제로 일부 응용 프로그램은 개발자가 데이터베이스 자격 증명을 갖지 않도록 설계되었으므로 실제로 제출되는 쿼리를 보는 것이 거의 불가능합니다.

  • 존재하지 않는 테이블을 선택하려고 할 때와 같이 오류가 발생하면 데이터베이스에서 “잘못된 개체 이름”줄을 따라 메시지가 표시됩니다. 백엔드에서 SQL을 작성하든 데이터베이스에서 SQL을 작성하든 똑같은 일이 발생하지만, 시스템 문제를 해결하려는 일부 가난한 개발자는 한 단계 더 깊은 곳에서 또 다른 동굴에 들어가야합니다. 문제가 무엇인지 알아 내기 위해 모든 일을하는 경이로운 절차를 파헤 치기 위해 문제가 존재합니다. 로그에는 “GetWidget의 오류”가 표시되지 않고 “OneProcedureToRuleThemAllRunner의 오류”가 표시됩니다. 이 추상화는 일반적으로 시스템을 악화 시킵니다.

매개 변수를 기반으로 테이블 이름을 전환하는 의사 C #의 예 :

string sql = $"SELECT * FROM {EscapeSqlIdentifier(tableName)};"
results = connection.Execute(sql);

이것이 상상할 수있는 모든 가능한 문제를 제거하는 것은 아니지만, 다른 기술로 설명했던 결함은이 예제에 없습니다.

답변

plpgsql 코드 내에서 EXECUTE 문은 테이블 이름이나 열이 변수에서 오는 쿼리에 사용해야합니다. 또한 동적으로 생성 IF EXISTS (<query>)될 때 구성이 허용되지 않습니다 query.

두 가지 문제가 모두 수정 된 함수는 다음과 같습니다.

CREATE OR REPLACE FUNCTION some_f(param character varying) RETURNS integer
AS $$
DECLARE
 v int;
BEGIN
      EXECUTE 'select 1 FROM ' || quote_ident(param) || ' WHERE '
            || quote_ident(param) || '.id = 1' INTO v;
      IF v THEN return 1; ELSE return 0; END IF;
END;
$$ LANGUAGE plpgsql;

답변

첫 번째는 실제로 “작동”하지 않는다는 의미에서 오류를 생성하지 않는 한만 작동합니다.

을 시도 SELECT * FROM quote_ident('table_that_does_not_exist');하면 함수가 1을 반환하는 이유를 알 수 있습니다. select는 quote_ident하나의 행 (변수 $1또는이 특정 경우 table_that_does_not_exist) 이 있는 하나의 열 (이름 )이 있는 테이블을 반환합니다 .

수행하려는 작업에는 동적 SQL이 필요하며, 이는 실제로 quote_*함수가 사용되는 곳입니다.

답변

질문이 테이블이 비어 있는지 여부를 테스트하는 것이라면 (id = 1) 여기에 Erwin의 저장된 proc의 단순화 된 버전이 있습니다. 

CREATE OR REPLACE FUNCTION isEmpty(tableName text, OUT zeroIfEmpty integer) AS
$func$
BEGIN
EXECUTE format('SELECT COALESCE ((SELECT 1 FROM %s LIMIT 1),0)', tableName)
INTO zeroIfEmpty;
END
$func$ LANGUAGE plpgsql;

답변

나는 이것이 오래된 스레드라는 것을 알고 있지만 최근에 동일한 문제를 해결하려고 할 때-내 경우에는 상당히 복잡한 스크립트에 대해 그것을 발견했습니다.

전체 스크립트를 동적 SQL로 바꾸는 것은 이상적이지 않습니다. 지루하고 오류가 발생하기 쉬운 작업이며 매개 변수화 기능을 잃게됩니다. 매개 변수는 SQL에서 상수로 보간되어야하며 성능과 보안에 나쁜 결과를 가져야합니다.

다음은 테이블에서만 선택해야하는 경우 SQL을 그대로 유지할 수있는 간단한 트릭입니다. 동적 SQL을 사용하여 임시보기를 만듭니다.

CREATE OR REPLACE FUNCTION some_f(_tbl varchar) returns integer
AS $$
BEGIN
    drop view if exists myview;
    execute format('create temporary view myview as select * from %s', _tbl);
    -- now you can reference myview in the SQL
    IF EXISTS (select * from myview where myview.id=1) THEN
     return 1;
    END IF;
    return 0;
END;
$$ language plpgsql;

답변

테이블 이름, 열 이름 및 값을 매개 변수로 함수에 동적으로 전달하려는 경우

이 코드 사용

create or replace function total_rows(tbl_name text, column_name text, value int)
returns integer as $total$
declare
total integer;
begin
    EXECUTE format('select count(*) from %s WHERE %s = %s', tbl_name, column_name, value) INTO total;
    return total;
end;
$total$ language plpgsql;


postgres=# select total_rows('tbl_name','column_name',2); --2 is the value