5
2

I'm going to show an example with Jena, but I'd love to hear about other frameworks and other languages.

In SQL-based applications we often make queries that have parameters. Some SQL drivers support numbered or names in parameters, but most of us sometimes resort to 'Dynamic SQL' where we interpolate variables in SQL queries. Recently I wrote a Query like this in Jena...

        Query rawResultQuery=QueryFactory.create(
                "PREFIX sw: <http://skunkworks.example.com/redacted#> "
                + "SELECT ?a ?b ?c ?d "
                + "{"
                + "   ?rawHit sw:key <"+someKey+"> ."
                + "   ?rawHit sw:a ?a . "
                + "   ?rawHit sw:b ?b ."
                + "   ?rawHit sw:c ?c ."
                + "   ?rawHit sw:d ?d ."
                + "} ORDER BY DESC(d)");

Note that someKey is a Resource, so there might be some resistance to SPARQL injection, although one could easily want to interpolate a string or a number.

I've seen quite a bit of trouble caused by SQL injections in the last ten years and I'd like to avoid that problem for my next ten writing SPARQL. What's the best way I can do this?

asked 18 Oct '11, 13:32

database_animal's gravatar image

database_animal ♦
8.4k1612
accept rate: 15%

The certain value of variable someKey must be the same as the conception of the ontology, isn't it? In which case, the SPARQL-based query with variable will be the same as the SQL-based query,keyword matching.

(19 Oct '11, 07:43) W.T Zhou W.T%20Zhou's gravatar image

In Sesame, you can do this by providing bindings on the prepared query object:

// parse the query string into a prepared query object
String queryString = "SELECT * WHERE {?X ?P ?Y .}";
TupleQuery query = conn.prepareTupleQuery(QueryLanguage.SPARQL, queryString);

// bind variable ?X to the URI 'foo:bar' 
URI foobar = conn.getValueFactory().createURI("foo:bar");
query.setBinding("X", foobar);
permanent link

answered 18 Oct '11, 15:54

Jeen%20Broekstra's gravatar image

Jeen Broekstra ♦
11.6k412
accept rate: 37%

As Andy noted since joining the Jena project I also added a ParameterizedSparqlString class to Jena which will aid users in building and parameterizing queries.

Example usage as follows (adapting from the example shown in the question):

ParameterizedSparqlString queryStr = new SparqlParameterizedString();
queryStr.setNSPrefix("sw", "http://skunkworks.example.com/redacted#");
queryStr.append("SELECT ?a ?b ?c ?d");
queryStr.append("{");
queryStr.append("   ?rawHit sw:key");
queryStr.appendNode(someKey);
queryStr.append(".");
queryStr.append("  ?rawHit sw:a ?a .");
queryStr.append("  ?rawHit sw:b ?b .");
queryStr.append("  ?rawHit sw:c ?c . ");
queryStr.append("  ?rawHit sw:d ?d .");
queryStr.append("} ORDER BY DESC(d)");

Query q = queryStr.asQuery();

It has various methods that can be used to treat it similar to a StringBuilder for just appending raw text, it also has a variety of appendNode() and appendLiteral() style methods for inserting appropriately escaped and formatted values into the query string.

You can also use it to parameterize a query, so you can use methods like setParam(), setIri() and setLiteral() to indicate that a given variable should be replaced with some constant when the query string is converted into a query.

permanent link

answered 24 Aug '12, 12:13

Rob%20Vesse's gravatar image

Rob Vesse ♦
14.0k1715
accept rate: 29%

I don't know how Jena deals with this, but some SPARQL implementations allow you to pre-bind particular variables in a query. So for example, you'd write your query like this:

PREFIX sw: <http://skunkworks.example.com/redacted#>
SELECT ?a ?b ?c ?d 
{
  ?rawHit sw:key ?key .
  ?rawHit sw:a ?a .
  ?rawHit sw:b ?b .
  ?rawHit sw:c ?c .
  ?rawHit sw:d ?d .
}
ORDER BY DESC(?d)

Then you'd pre-bind the variable ?key to whatever URI/literal you like before executing the query.

EDIT: which and how?

  • Jena's ARQ appears to have setInitialBindings on the com.hp.hpl.jena.query.QueryExecution interface, though not all implementations of the interface support it. For example, a query execution to a remote SPARQL protocol endpoint will not.
  • RDF::Query allows a named argument bind to be passed to the prepare or execute methods of a query. This is a hashref (i.e. a reference to an associative array) binding variables to values.
permanent link

answered 18 Oct '11, 14:29

tobyink's gravatar image

tobyink ♦
5.2k312
accept rate: 26%

edited 18 Oct '11, 15:44

which and how?

(18 Oct '11, 15:32) database_animal ♦ database_animal's gravatar image
1

Jena does - see QueryExecutionFactory.create(...., initialBinding)

(18 Oct '11, 15:33) AndyS ♦ AndyS's gravatar image

FYI SPIN has a mechanism called SPIN templates for the purpose of formalizing queries that take arguments (pre-bound variables). In the Jena implementation of the SPIN API, we use setInitialBindings(). The same mechanism is used in SPIN functions.

permanent link

answered 18 Oct '11, 19:53

Holger%20Knublauch's gravatar image

Holger Knubl...
1.7k137
accept rate: 15%

There is more than one way to skin a cat. For your particular example, there's an easy way to avoid injection:

    Node someKeyNode = Node.createURI(someKey);
    Query rawResultQuery=QueryFactory.create(
            "PREFIX sw: <http://skunkworks.example.com/redacted#> "
            + "SELECT ?a ?b ?c ?d "
            + "{"
            + "   ?rawHit sw:key " + FmtUtils.stringForNode(someKeyNode) + " ."
            + "   ?rawHit sw:a ?a . "
            + "   ?rawHit sw:b ?b ."
            + "   ?rawHit sw:c ?c ."
            + "   ?rawHit sw:d ?d ."
            + "} ORDER BY DESC(d)");
permanent link

answered 18 Oct '11, 15:56

tobyink's gravatar image

tobyink ♦
5.2k312
accept rate: 26%

Talis' Kasabi data platform has extended the idea of introducing further variable bindings into a standardised HTTP interface. I like this idea (although their name, catchy as is it, 'SPARQL stored procedures' is a bit of a false analogy): http://www.slideshare.net/ldodds/creating-apis-over-rdf

permanent link

answered 18 Oct '11, 18:05

Barry%20Norton's gravatar image

Barry Norton
1.4k19
accept rate: 19%

dotNetRDF (disclaimer - I'm the lead developer) has an explicit SparqlParameterizedString class which is designed for exactly this. It works similarily to a SqlCommand for those familiar with the ADO.Net style of programming e.g.

//Create the Parameterized String
SparqlParameterizedString queryString = new SparqlParameterizedString();
queryString.Namespaces.AddNamespace("ex", new Uri("http://example.org/ns#"));
queryString.CommandText = "SELECT * WHERE { ?s ex:property @value }";

//Inject a Value for the parameter
queryString.SetUri("value", new Uri("http://example.org/value"));

//When we call ToString() we get the full command text with namespaces appended as PREFIX
//declarations and any parameters replaced with their declared values
Console.WriteLine(queryString.ToString());

You can also inject values for normal SPARQL variables as well as ADO.Net style parameters so you can create a query where you might inject a value in place of a variable or you might use it as is.

permanent link

answered 19 Oct '11, 05:01

Rob%20Vesse's gravatar image

Rob Vesse ♦
14.0k1715
accept rate: 29%

edited 19 Oct '11, 10:33

Nice! Mind if I nick your idea for a possible Sesame extension?

(19 Oct '11, 17:49) Jeen Broekstra ♦ Jeen%20Broekstra's gravatar image

No go right ahead

(20 Oct '11, 04:24) Rob Vesse ♦ Rob%20Vesse's gravatar image
2

Rob has added SparqlParameterizedString to Apache Jena.

(24 Aug '12, 10:19) AndyS ♦ AndyS's gravatar image

For rdflib in Python (adapted from the Intro to SPARQL docs from rdflib 4.0-dev):

initNs = { "foaf": FOAF }
q = prepareQuery(
    'SELECT ?s WHERE { ?person foaf:knows ?s .}',
    initNs=initNs) 
g = rdflib.Graph()
g.load("foaf.rdf")
tim = rdflib.URIRef("http://www.w3.org/People/Berners-Lee/card#i")
for row in g.query(q, initBindings={'person': tim}):
    print(row)

seeAlso: rdflib-sparql Prepared Queries

permanent link

answered 10 May '13, 17:56

westurner's gravatar image

westurner
613
accept rate: 0%

edited 10 May '13, 18:11

SPARQL injection is similar to SQL injection ("CWE-89: Improper Neutralization of Special Elements used in an SQL Command ('SQL Injection')"). To my knowledge there is not yet a CWE code for SPARQL.

permanent link

answered 09 Sep '13, 07:33

westurner's gravatar image

westurner
613
accept rate: 0%

edited 09 Sep '13, 07:40

One feature of SPARQL is that query and update are separate languages - that reduces (does not remove) injection attacks because you can pass an expected query through the query parser and any update operations will be syntax errors. You can only turn a query into another query.

permanent link

answered 18 Oct '11, 15:35

AndyS's gravatar image

AndyS ♦
13.8k37
accept rate: 33%

3

that's bad enough...

(18 Oct '11, 16:03) database_animal ♦ database_animal's gravatar image
2

For example change the query to select all user names and passwords ;) And see where else they are used.

(20 Oct '11, 04:46) Jerven ♦ Jerven's gravatar image
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×1,328
×628
×16
×8
×1

question asked: 18 Oct '11, 13:32

question was seen: 5,371 times

last updated: 09 Sep '13, 07:40