[PDB-4628] Fields in group_by queries aren't properly quoted/escaped - Puppet Jira Server

XML

Word

Printable

Details

Type: Bug
Status: Resolved
Priority: Normal
Resolution: Fixed
Affects Version/s: None
Fix Version/s: PDB 7.5.2, PDB 6.19.0
Component/s: PuppetDB
Labels:
- query-engine-improvements

Team:
PuppetDB

CS Priority:
Reviewed

QA Risk Assessment:
Needs Assessment

Description

I'm trying to write a PuppetDB query using "group by" semantics where i was trying to group by a fact value and think i found a bug where the "group by" fields aren't properly escaped / quoted.

AST:

    ['from', 'inventory',

     ['extract',

      [['function', 'count'], 'facts.wsus_target_group', 'certname'],

      ['=', 'facts.osfamily', 'windows'],

      ['group_by', 'facts.wsus_target_group', 'certname'],

],

PQL

"inventory[certname, facts.wsus_target_group] {facts.osfamily = 'windows' group by facts.wsus_target_group,certname }"

Trying to run these queries i get an error from the API:

<html>

<head>

<meta http-equiv="Content-Type" content="text/html;charset=utf-8"/>

<title>Error 500 Server Error</title>

</head>

<body><h2>HTTP ERROR 500</h2>

<p>Problem accessing /pdb/query/v4. Reason:

<pre>    Server Error</pre></p><h3>Caused by:</h3><pre>javax.servlet.ServletException: org.postgresql.util.PSQLException: ERROR: missing FROM-clause entry for table &quot;facts&quot;

  Position: 614

        at org.eclipse.jetty.server.handler.HandlerCollection.handle(HandlerCollection.java:168)

        at org.eclipse.jetty.server.handler.gzip.GzipHandler.handle(GzipHandler.java:753)

        at org.eclipse.jetty.server.handler.RequestLogHandler.handle(RequestLogHandler.java:56)

        at com.puppetlabs.trapperkeeper.services.webserver.jetty9.utils.MDCRequestLogHandler.handle(MDCRequestLogHandler.java:36)

        at org.eclipse.jetty.server.handler.StatisticsHandler.handle(StatisticsHandler.java:174)

        at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:132)

        at org.eclipse.jetty.server.Server.handle(Server.java:505)

        at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:370)

        at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:267)

        at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:305)

        at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:103)

        at org.eclipse.jetty.io.ssl.SslConnection$DecryptedEndPoint.onFillable(SslConnection.java:427)

        at org.eclipse.jetty.io.ssl.SslConnection.onFillable(SslConnection.java:321)

        at org.eclipse.jetty.io.ssl.SslConnection$2.succeeded(SslConnection.java:159)

        at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:103)

        at org.eclipse.jetty.io.ChannelEndPoint$2.run(ChannelEndPoint.java:117)

        at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.runTask(EatWhatYouKill.java:333)

        at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.doProduce(EatWhatYouKill.java:310)

        at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.tryProduce(EatWhatYouKill.java:168)

        at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.run(EatWhatYouKill.java:126)

        at org.eclipse.jetty.util.thread.ReservedThreadExecutor$ReservedThread.run(ReservedThreadExecutor.java:366)

        at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:698)

        at org.eclipse.jetty.util.thread.QueuedThreadPool$Runner.run(QueuedThreadPool.java:804)

        at java.lang.Thread.run(Thread.java:748)

Caused by: org.postgresql.util.PSQLException: ERROR: missing FROM-clause entry for table &quot;facts&quot;

  Position: 614

        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2433)

        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2178)

        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:306)

        at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441)

        at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)

        at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:155)

        at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:118)

        at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)

        at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java)

        at puppetlabs.puppetdb.jdbc$call_with_array_converted_query_rows$fn__22073.invoke(jdbc.clj:297)

        at clojure.java.jdbc$db_transaction_STAR_.invokeStatic(jdbc.clj:814)

        at clojure.java.jdbc$db_transaction_STAR_.invoke(jdbc.clj:741)

        at puppetlabs.puppetdb.jdbc$call_with_array_converted_query_rows.invokeStatic(jdbc.clj:289)

        at puppetlabs.puppetdb.jdbc$call_with_array_converted_query_rows.invoke(jdbc.clj:272)

        at puppetlabs.puppetdb.jdbc$call_with_array_converted_query_rows.invokeStatic(jdbc.clj:285)

        at puppetlabs.puppetdb.jdbc$call_with_array_converted_query_rows.invoke(jdbc.clj:272)

        at puppetlabs.puppetdb.query_eng$fn__30503$produce_streaming_body__30508$fn__30509$fn__30512$fn__30514$fn__30515.invoke(query_eng.clj:221)

        at puppetlabs.puppetdb.jdbc$with_transacted_connection_fn$fn__22266$fn__22267.invoke(jdbc.clj:514)

        at clojure.java.jdbc$db_transaction_STAR_.invokeStatic(jdbc.clj:771)

        at clojure.java.jdbc$db_transaction_STAR_.invoke(jdbc.clj:741)

        at puppetlabs.puppetdb.jdbc$with_transacted_connection_fn$fn__22266.invoke(jdbc.clj:513)

        at puppetlabs.puppetdb.jdbc$fn__22244$retry_sql_STAR___22249$fn__22250$fn__22251.invoke(jdbc.clj:485)

        at puppetlabs.puppetdb.jdbc$fn__22244$retry_sql_STAR___22249$fn__22250.invoke(jdbc.clj:484)

        at puppetlabs.puppetdb.jdbc$fn__22244$retry_sql_STAR___22249.invoke(jdbc.clj:475)

        at puppetlabs.puppetdb.jdbc$with_transacted_connection_fn.invokeStatic(jdbc.clj:511)

        at puppetlabs.puppetdb.jdbc$with_transacted_connection_fn.invoke(jdbc.clj:500)

        at puppetlabs.puppetdb.query_eng$fn__30503$produce_streaming_body__30508$fn__30509$fn__30512$fn__30514.invoke(query_eng.clj:220)

        at ring.util.io$piped_input_stream$fn__190.invoke(io.clj:28)

        at clojure.core$binding_conveyor_fn$fn__5754.invoke(core.clj:2030)

        at clojure.lang.AFn.call(AFn.java:18)

        at java.util.concurrent.FutureTask.run(FutureTask.java:266)

        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)

        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)

        ... 1 more

</pre>

<h3>Caused by:</h3><pre>org.postgresql.util.PSQLException: ERROR: missing FROM-clause entry for table &quot;facts&quot;

  Position: 614

        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2433)

        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2178)

        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:306)

        at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441)

        at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)

        at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:155)

        at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:118)

        at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)

        at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java)

        at puppetlabs.puppetdb.jdbc$call_with_array_converted_query_rows$fn__22073.invoke(jdbc.clj:297)

        at clojure.java.jdbc$db_transaction_STAR_.invokeStatic(jdbc.clj:814)

        at clojure.java.jdbc$db_transaction_STAR_.invoke(jdbc.clj:741)

        at puppetlabs.puppetdb.jdbc$call_with_array_converted_query_rows.invokeStatic(jdbc.clj:289)

        at puppetlabs.puppetdb.jdbc$call_with_array_converted_query_rows.invoke(jdbc.clj:272)

        at puppetlabs.puppetdb.jdbc$call_with_array_converted_query_rows.invokeStatic(jdbc.clj:285)

        at puppetlabs.puppetdb.jdbc$call_with_array_converted_query_rows.invoke(jdbc.clj:272)

        at puppetlabs.puppetdb.query_eng$fn__30503$produce_streaming_body__30508$fn__30509$fn__30512$fn__30514$fn__30515.invoke(query_eng.clj:221)

        at puppetlabs.puppetdb.jdbc$with_transacted_connection_fn$fn__22266$fn__22267.invoke(jdbc.clj:514)

        at clojure.java.jdbc$db_transaction_STAR_.invokeStatic(jdbc.clj:771)

        at clojure.java.jdbc$db_transaction_STAR_.invoke(jdbc.clj:741)

        at puppetlabs.puppetdb.jdbc$with_transacted_connection_fn$fn__22266.invoke(jdbc.clj:513)

        at puppetlabs.puppetdb.jdbc$fn__22244$retry_sql_STAR___22249$fn__22250$fn__22251.invoke(jdbc.clj:485)

        at puppetlabs.puppetdb.jdbc$fn__22244$retry_sql_STAR___22249$fn__22250.invoke(jdbc.clj:484)

        at puppetlabs.puppetdb.jdbc$fn__22244$retry_sql_STAR___22249.invoke(jdbc.clj:475)

        at puppetlabs.puppetdb.jdbc$with_transacted_connection_fn.invokeStatic(jdbc.clj:511)

        at puppetlabs.puppetdb.jdbc$with_transacted_connection_fn.invoke(jdbc.clj:500)

        at puppetlabs.puppetdb.query_eng$fn__30503$produce_streaming_body__30508$fn__30509$fn__30512$fn__30514.invoke(query_eng.clj:220)

        at ring.util.io$piped_input_stream$fn__190.invoke(io.clj:28)

        at clojure.core$binding_conveyor_fn$fn__5754.invoke(core.clj:2030)

        at clojure.lang.AFn.call(AFn.java:18)

        at java.util.concurrent.FutureTask.run(FutureTask.java:266)

        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)

        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)

        at java.lang.Thread.run(Thread.java:748)

</pre></body>

</html>

Looking at the postgres logs i see the following error:

< 2020-01-20 08:57:40.569 EST > ERROR:  missing FROM-clause entry for table "facts" at character 614

< 2020-01-20 08:57:40.569 EST > STATEMENT:  WITH inactive_nodes AS (SELECT certname FROM certnames WHERE (deactivated IS NOT NULL OR expired IS NOT NULL)) SELECT certnames.certname AS certname, (fs.stable||fs.volatile)->'wsus_target_group' AS "facts.wsus_target_group" FROM factsets fs LEFT JOIN environments ON fs.environment_id = environments.id LEFT JOIN producers ON fs.producer_id = producers.id LEFT JOIN certnames ON fs.certname = certnames.certname WHERE ((fs.stable||fs.volatile) @> $1 AND NOT ((certnames.certname) in (SELECT certname FROM  ( (SELECT inactive_nodes.certname AS certname FROM inactive_nodes) )  sub))) GROUP BY certnames.certname, facts.wsus_target_group

-------------------

I noticed that in the Postgres query that `facts` isn't actually the name of a table instead its the name of a field and so `facts.wsus_target_group` is parsed by Postgres thinking that `facts` is a table and we're trying to extract `wsus_target_group` field from that.

I was able to force inject some quotes in the AST query and got it to work:

    ['from', 'inventory',

     ['extract',

      [['function', 'count'], 'facts.wsus_target_group', 'certname'],

      ['=', 'facts.osfamily', 'windows'],

      ['group_by', '\"facts.wsus_target_group\"', 'certname'],

],

I'm not sure the exact right fix, but those extra quotes helped my query return without an error.

Attachments

Activity

People

Assignee:: Unassigned

Reporter:: Nick Maludy

Votes:: 0 Vote for this issue

Watchers:: 4 Start watching this issue

Dates

Created:: 2020/01/20 6:04 AM

Updated:: 2023/02/10 11:49 AM

Resolved:: 2021/08/23 1:59 PM