Looking for the correct way to parameterizing my stored procedures to avoid injection attacks. I can pass a parameter in similar fashion like "prefix", but not sure what the correct of handling the @id without doing something like "id=" + id, which leads back to avoiding injection attacks.
var sproc= 'dbs/{db}/colls/{col}/sprocs/GetDocument';
var parameters = [
{
name: '@id',
value: 'AndersenFamily'
}
]
client.executeStoredProcedure(sproc,parameters , function (err, results, responseHeaders) {
console.log('//////////////////////////////////');
if (err) {
console.log('// err');
console.log(err);
}
if (responseHeaders) {
console.log('// responseHeaders');
console.log(responseHeaders);
}
if (results) {
console.log('// results');
console.log(results);
}
console.log('//////////////////////////////////');
});
// STORED PROCEDURE
function GetDocument(prefix) {
var collection = getContext().getCollection();
// Query documents and take 1st item.
var isAccepted = collection.queryDocuments(
collection.getSelfLink(),
'SELECT * FROM root r WHERE r.id=@id',
function (err, doc, options) {
if (err) throw err;
// Check the feed and if empty, set the body to 'no docs found',
// else take 1st element from feed
if (!doc || !doc.length) getContext().getResponse().setBody('no doc found');
else getContext().getResponse().setBody(prefix + JSON.stringify(doc[0]));
});
if (!isAccepted) throw new Error('The query was not accepted by the server.');
}
You were close. A few things:
So, you could do something like this:
var sprocLink = 'dbs/{db}/colls/{col}/sprocs/GetDocument';
client.executeStoredProcedure(sprokLink, "AndersenFamily" , function (err, results, responseHeaders) {
console.log('//////////////////////////////////');
if (err) {
console.log('// err');
console.log(err);
}
if (responseHeaders) {
console.log('// responseHeaders');
console.log(responseHeaders);
}
if (results) {
console.log('// results');
console.log(prefix + JSON.stringify(results));
}
console.log('//////////////////////////////////');
});
Stored Procedure:
function GetDocument(family) {
var collection = getContext().getCollection();
// Query documents and take 1st item.
var parameters = [{name: '@id', value: family}];
var query = 'SELECT * FROM root r WHERE r.id=@id';
var querySpec = {query: query, parameters: parameters};
var isAccepted = collection.queryDocuments(
collection.getSelfLink(),
querySpec,
function (err, doc, options) {
if (err) throw err;
return getContext().getResponse().setBody(doc[0]);
});
}
If I understand your intent, there is no reason to pass prefix
into the sproc. You can more efficiently add it when your are formatting your output upon returning from the sproc as I have shown above. If I've misunderstood your intent and you do want to use it for something inside the sproc, you can use an array ["AndersonFamily", "some prefix"]
or as an object {name: "AndersonFamily", prefix: "some prefix"}
in place of the "AndersonFamily" that I show in my example above.
I left your query as-is because it'll work this way, but if all you really want to do is to get one document by id, then it might be more efficient to just use readDocument()
rather than queryDocuments()
. I would run an experiment to see. If you switch to that approach you'll want to use id-based routing which means that you'll need the database and collection names passed into the sproc.
It won't matter in this case with such a highly selective query, but a robust sproc would deal with continuation tokens and even restarting the sproc execution.
Note, I haven't actually run the code above. I just made text edits so it's very possible I've gotten some syntax wrong, but this answer at least gives you the conceptual understanding to work that out.