Re: [Virtuoso-users] Using PREFIX in a prepared Statement

[Kingsley Idehen]

On 10/24/16 12:00 PM, Davis, Daniel (NIH/NLM) [C] wrote:
>
> Kingsley,
>
>  
>
> I’m not sure exactly how this helps me.   Let me be clearer on the
> problem.   Because this application runs within a federal government
> site, I’m required to submit my source code to static analysis.  
> Although the data is read-only, and the database user has only
> read-only access, there are still general guidelines.
>
> I’m inquiring in the sense therefore that I want to figure out the
> boundaries of how Virtuoso JDBC can enable me to pass these
> guidelines, even though I think I can get an exemption by labeling the
> SQL injection complaint a false positive.
>
>  
>
> What I’m talking about is doing this through a JDBC interface, and
> rather than using a query of the form:
>
>  
>
>         String queryFormat= "SPARQL"
>
>             + " define input:inference
> \"http://id.nlm.nih.gov/mesh/vocab\"";
>
>             + " PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>"
>
>             + " PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>"
>
>             + " PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>"
>
>             + " PREFIX owl: <http://www.w3.org/2002/07/owl#>"
>
>             + " PREFIX meshv: <http://id.nlm.nih.gov/mesh/vocab#>"
>
>             + " PREFIX mesh: <http://id.nlm.nih.gov/mesh/>"
>
>             + " SELECT ?l"
>
>             + " FROM <http://id.nlm.nih.gov/mesh>"
>
>             + " WHERE { mesh:%s %s ?l }";
>
>         String query= String./format/(queryFormat, id, prop);
>
>         log.info(query);
>
>         Statement stmt= connection.createStatement();
>
>         ResultSet rset= stmt.executeQuery(query);
>
>  
>
> I would rather do this using a prepared statement, maybe still
> formatting in the property so that the query plan can benefit from the
> predicate index:
>
>  
>
>         String queryFormat= "SPARQL"
>
>             + " define input:inference
> \"http://id.nlm.nih.gov/mesh/vocab\"";
>
>             + " PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>"
>
>             + " PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>"
>
>             + " PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>"
>
>             + " PREFIX owl: <http://www.w3.org/2002/07/owl#>"
>
>             + " PREFIX meshv: <http://id.nlm.nih.gov/mesh/vocab#>"
>
>             + " PREFIX mesh: <http://id.nlm.nih.gov/mesh/>"
>
>             + " SELECT ?l"
>
>             + " FROM <http://id.nlm.nih.gov/mesh>"
>
>             + " WHERE { ?? %s ?l }";
>
>         String query= String./format/(queryFormat, prop);
>
>         log.info(query);
>
>         PreparedStatement stmt= connection.prepareStatement(query);
>
>         stmt.setString(1, "http://id.nlm.nih.gov/mesh/"+id);
>
>        
>
> So far, this general approach of using a prepared statement is not
> working for me.   I may struggle through it, but is there anyway for
> me to preserve the convenience of prefixes:
>
>  
>
> … WHERE { mesh:?? %s ?l };
>
>  
>
> And then later bind the query argument?
>

Understood. Problem is that we need to publish more examples re., use of
SPARQL via ODBC and JDBC connections.

To our issue:

It will be something like:

     String queryFormat = "SPARQL"
         + " define input:inference \"http://id.nlm.nih.gov/mesh/vocab\"";
         + " PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>"
         + " PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>"
         + " PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>"
         + " PREFIX owl: <http://www.w3.org/2002/07/owl#>"
         + " PREFIX meshv: <http://id.nlm.nih.gov/mesh/vocab#>"
         + " PREFIX mesh: <http://id.nlm.nih.gov/mesh/>"
         + " SELECT ?l"
         + " FROM <http://id.nlm.nih.gov/mesh>"
         + " *WHERE { `iri(??)` `iri(??)` ?l }*";

//     String query = String.format(queryFormat, id, prop);
//     log.info(query);

     PreparedStatement ps = connection.prepareStatement(query);
     ps.setString(1, "http://id.nlm.nih.gov/mesh/"+id);
     ps.setString(2, prop);
     ResultSet rset = ps.executeQuery();

Note: You have to quote URIs without < >
Example:
       String prop = "http://testhost.org/myprop";;

We'll get some docs out too, as I stated earlier.

-- 
Regards,

Kingsley Idehen       
Founder & CEO 
OpenLink Software   (Home Page: http://www.openlinksw.com)

Weblogs (Blogs):
Legacy Blog: http://www.openlinksw.com/blog/~kidehen/
Blogspot Blog: http://kidehen.blogspot.com
Medium Blog: https://medium.com/@kidehen

Profile Pages:
Pinterest: https://www.pinterest.com/kidehen/
Quora: https://www.quora.com/profile/Kingsley-Uyi-Idehen
Twitter: https://twitter.com/kidehen
Google+: https://plus.google.com/+KingsleyIdehen/about
LinkedIn: http://www.linkedin.com/in/kidehen

Web Identities (WebID):
Personal: http://kingsley.idehen.net/dataspace/person/kidehen#this
        : 
http://id.myopenlink.net/DAV/home/KingsleyUyiIdehen/Public/kingsley.ttl#this

smime.p7s
Description: S/MIME Cryptographic Signature

------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most 
engaging tech sites, SlashDot.org! http://sdm.link/slashdot
_______________________________________________
Virtuoso-users mailing list
Virtuoso-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/virtuoso-users