Cloud Datastore API - php - GqlQuery - "SELECT * FROM kind_name WHERE __key__"

Question

I have an application on GAE:php runtime and I'm using Google Cloud Datastore API to connect to the Datastore from my PHP app.

My question:

How can I query my database using keys, something similar to "SELECT * FROM table_name WHERE id <= 1410611039" in Sql.

My code:(I've seen people are using this query-string syntax and it works for them)

$gql_query = new Google_Service_Datastore_GqlQuery();
$gql_query->setQueryString("SELECT * FROM notification WHERE __key__ = KEY('notification', 1410611039)");

Some part of the error I get is Fatal error: Uncaught exception ... (400) Disallowed literal: KEY...

My Datastore console view:

Everything is fine until I append WHERE __key__ = KEY('notification', 1410611039) to the query_string.

Answer 1

Keys are considered literals in Cloud Datastore GQL and require special handling.

If users will be providing values at runtime, we recommend using argument binding. This helps prevent malicious behavior such as injection attacks.

There are two ways to do this; both start with a key value:

$key_path_element = new Google_Service_Datastore_KeyPathElement();
$key_path_element->setKind('notification');
$key_path_element->setId(1410611039);

$key = new Google_Service_Datastore_Key();
$key.setPath([$key_path_element]);

$key_value = new Google_Service_Datastore_Value();
$key_value->setKeyValue($key);

$key_value can then be used either as a named argument:

$gql_query = new Google_Service_Datastore_GqlQuery();
$gql_query->setQueryString("SELECT * FROM notification WHERE __key__ = $theKey");

$name_arg = new Google_Service_Datastore_GqlQueryArg();
$name_arg->setName("theKey");
$name_arg->setValue($key_value);

$gql_query->setNameArgs([$name_arg]);

or as a positional argument:

$gql_query = new Google_Service_Datastore_GqlQuery();
$gql_query->setQueryString("SELECT * FROM notification WHERE __key__ = @1");

$number_arg = new Google_Service_Datastore_GqlQueryArg();
$number_arg->setValue($key_value);

$gql_query->setNumberArgs([$number_arg]);

If no user-provided input is being added to the query, another option is to explicitly allow literals in the request:

$gql_query = new Google_Service_Datastore_GqlQuery();
$gql_query->setQueryString("SELECT * FROM notification WHERE __key__ = KEY('notification', 1410611039)");
$gql_query->setAllowLiteral(true);

Here are some additional details on argument binding and here is the full GQL reference.

Answer 2

I had the same issue using the Java Datastore API. The solution in Java would look like this:

datastore = DatastoreOptions.builder()
.authCredentials(AuthCredentials.createApplicationDefaults())
.build()
.service();
String id = "asdf123";
Query<Entity> query = Query.gqlQueryBuilder(
    Query.ResultType.ENTITY, 
    "SELECT * FROM Task WHERE id = '"+id+"'")
    .allowLiteral(true)
    .build();
QueryResults<Entity> result = datastore.run(query);
while(result.hasNext()){
    Entity temp = result.next();
    //(...)
}