Figure out how to make auth_method_report and grade_reports as prepared queries

[claudijd]

https://github.com/mozilla/ssh_scan_api/blob/adc3e3a2c1309d897754d0cc8d6751aa716666b5/lib/ssh_scan_api/database/postgres.rb#L134

There are a couple reasons I haven't changed this over yet:

1. These statements are effectively hardcoded to methods that do no accept parameters
2. There is some weirdness with doing prepared statements inside a literal like this (similar to https://bitbucket.org/ged/ruby-pg/issues/216/cant-use-parameters-in-create-view, and https://github.com/pgjdbc/pgjdbc/issues/575)
3. I tried briefly and couldn't figure it out

This is just so I don't forget about it. I welcome being educated on the subject on what I should be doing.

[claudijd]

For clarity, I'm aware of how to do prepared statements, it's more around getting postgres to recognize the parameter I'm passing it.

This happens when I use parameterized statements, like in:

https://github.com/mozilla/ssh_scan_api/commit/2a443fb642cf384dda1edecd5ba7438e637983a1#diff-e5da646f888cccfe31e3f88e92528f98R39

 PG::ProtocolViolation:
   ERROR:  bind message supplies 1 parameters, but prepared statement "count_auth_method" requires 0
 # ./lib/ssh_scan_api/database/postgres.rb:137:in `exec_prepared'
 # ./lib/ssh_scan_api/database/postgres.rb:137:in `block in auth_method_report'
 # ./lib/ssh_scan_api/database/postgres.rb:136:in `each'
 # ./lib/ssh_scan_api/database/postgres.rb:136:in `auth_method_report'
 # ./spec/ssh_scan_api/database/postgres_spec.rb:302:in `block (2 levels) in <top (required)>'

[presidentbeef]

I'm not a Postgres expert, but why do you need the triple quotes?

Can you just do

'\"$1\"' -> $1

or if you need the double quotes, use #inspect?

@client.exec_prepared("count_auth_method", [auth_method.inspect])

[claudijd]

@presidentbeef thx for the reply, I did attempt simply removing the quotes, but in my attempts I recall the $1 not being equivalent because it's looking for a specific syntax to recognize the param. That in fact one of the ways I tried and failed. I have not attempted the inspect part, that's the next thing to try once I deploy the postgres port today. Thanks, much appreciated!

[claudijd]

I've decided to go a different route here and focus on app functionality vs. active reporting by the API routes. I'll just get the info direct from the DB and in a separate code-base, to keep API from getting muddy and too multi-purpose.