2

I am using big-query in my project and preparing one query based on some users input which are taken as parameter in query.

How we can do this in a best manner?

For java jdbc we have and so we use prepared-statement.

Is their some thing like prepared-statement for big-query.

or can you provide the best way to escape this user input for adding it in big-query's sql query.

Phoenix
  • 1,045
  • 1
  • 14
  • 22
murtaza.webdev
  • 3,523
  • 4
  • 22
  • 32
  • Why do you need to escape user input in the first place? It sounds like you are using BigQuery as a traditional RDBMS. You could check [this](https://www.owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet#Defense_Option_3:_Escaping_All_User_Supplied_Input) on how to escape user inputs. – Paul Liang Feb 04 '15 at 12:02
  • while making sql queries we required some user inputs that filters the result according to the best they want. and so i want that inputs not create any issue. just for that – murtaza.webdev Feb 04 '15 at 12:09
  • for now i am using StringEscapeUtils.escapeJavaScript. it is having the same rule as of googleBigquery – murtaza.webdev Feb 04 '15 at 12:10

2 Answers2

1

BigQuery now supports parameterized queries. You can use identifiers like @param_name and positional parameters with ?.

Java example from the docs:

String query =
    "SELECT word, word_count\n"
        + "FROM `bigquery-public-data.samples.shakespeare`\n"
        + "WHERE corpus = @corpus\n"
        + "AND word_count >= @min_word_count\n"
        + "ORDER BY word_count DESC";
// Note: Standard SQL is required to use query parameters.
QueryJobConfiguration queryConfig =
    QueryJobConfiguration.newBuilder(query)
        .addNamedParameter("corpus", QueryParameterValue.string(corpus))
        .addNamedParameter("min_word_count", QueryParameterValue.int64(minWordCount))
        .build();

https://cloud.google.com/bigquery/docs/parameterized-queries

Felipe Hoffa
  • 54,922
  • 16
  • 151
  • 325
0

BigQuery does not support prepared statements (or anything similar), so the most efficient solution is probably to just break the query string into fragments and concatenate them together with the user supplied values. The fastest way to concatenate strings seems to be just using the '+' operator, from going through these old posts:

Community
  • 1
  • 1
Adam
  • 5,697
  • 1
  • 20
  • 52