CODEWITHSUNDEEP
javadatabasegoogle-bigquerysql-injection
Elad Benda
Elad Benda

Reputation: 36664

How to safely inject parameter into string DB query java?

I have this bigQuery example code:

List<TableRow> rows =
                executeQuery(
                        "SELECT TOP(corpus, 10) as title, COUNT(*) as unique_words Where country = 'USA' " +
                                + "FROM [publicdata:samples.shakespeare]",
                        bigquery,
                        PROJECT_ID);

If i want to safely inject the country to that string

how can i do that?

I want to avoid the risk of sql injection and this is risky:

public void foo(String countryParam) {
    List<TableRow> rows =
                    executeQuery(
                            "SELECT TOP(corpus, 10) as title, COUNT(*) as unique_words Where country = '"+countryParam+"' " +
                                    + "FROM [publicdata:samples.shakespeare]",
                            bigquery,
                            PROJECT_ID);
}

update

couldn't find a clear example to Elliott Brossard suggestion:

public List<String> getVenuesForBrand(BrandChangeDataUi brandChangeDataUi) throws IOException {
    QueryParameter param = new QueryParameter();
    param.setName("country");
    param.setParameterValue(new QueryParameterValue().setValue("USA"));
    param.setParameterType(new QueryParameterType().setType("string"));

    List<QueryParameter> params =  new ArrayList<>();
    params.add(param);

    JobConfigurationQuery jobConfigurationQuery = new JobConfigurationQuery();
    jobConfigurationQuery.setQueryParameters(params);

    jobConfigurationQuery.setQuery( "SELECT TOP(corpus, 10) as title, COUNT(*) as unique_words Where country = 'USA' " +
                                    + "FROM [publicdata:samples.shakespeare]");



    List<TableRow> rows =
            executeQuery(
                    jobConfigurationQuery.toString(),
                    bigquery,
                    PROJECT_ID);

    printResults(rows);

    return null;
}

Upvotes: 1

Views: 218

Answers (1)

Elliott Brossard
Elliott Brossard

Reputation: 33745

Take a look at queryParameters under the jobs.query reference. For the Java API, they are documented as part of JobConfigurationQuery. Note that query parameters are available only using standard SQL.

Upvotes: 1

Related Questions