Parameterized Query as Procedure Parameter?

Parameterized Query as Procedure Parameter?

Postby mt_1990 » Tue 03 Jun 2014 16:00

I have procedure in PostgreSQL defined as:
Code: Select all
CREATE OR REPLACE FUNCTION CreateCursorC(text, text)
RETURNS text
LANGUAGE c
AS '$libdir/vendo', $function$createcursorc$function$

Execute example:
Code: Select all
SELECT CreateCursorC('cursor_name', 'SELECT a FROM x WHERE a=''text''');

Of course I would like to use parameters. Like this:
Code: Select all
SELECT CreateCursorC($1, 'SELECT a FROM x WHERE a=$2');

Unfortunately it's not working because parameter $2 is in quotes.
Is there a way to accomplished this task?
mt_1990
 
Posts: 3
Joined: Tue 03 Jun 2014 15:45

Re: Parameterized Query as Procedure Parameter?

Postby Pinturiccio » Thu 05 Jun 2014 16:12

mt_1990 wrote:Of course I would like to use parameters. Like this:
Code: Select all
SELECT CreateCursorC($1, 'SELECT a FROM x WHERE a=$2');

There is no possibility to use a parameter in such way. This is the PostgreSQL protocol limitation, not the dotConnect for PostgreSQL one.

You can use one of the following workarounds:
1. Don't use a parameter in a query, but assign a full query text with the necessary variable value to it. Here is the code of the example:
Code: Select all
void func1(string par)
{
   ...
   comm.Parameters.Add("$2", PgSqlType.Text).Value = "SELECT a FROM x WHERE a='" + par + "'";
}

2. If you are going to use only the query, specified in the second parameter, in your function, you can pass only the variable value to the function instead of the query, and then add the variable value to the query inside the function. The value of the second variable must be added to the query inside the function too.
Pinturiccio
Devart Team
 
Posts: 2020
Joined: Wed 02 Nov 2011 09:44

Re: Parameterized Query as Procedure Parameter?

Postby mt_1990 » Thu 05 Jun 2014 21:10

Pinturiccio wrote:1. Don't use a parameter in a query, but assign a full query text with the necessary variable value to it. Here is the code of the example:
Code: Select all
void func1(string par)
{
   ...
   comm.Parameters.Add("$2", PgSqlType.Text).Value = "SELECT a FROM x WHERE a='" + par + "'";
}


This solution is vulnerable to SQL injection.
Is there a way to get string from DbCommand using dotConnect?
Or safe SQL escaping function?
mt_1990
 
Posts: 3
Joined: Tue 03 Jun 2014 15:45

Re: Parameterized Query as Procedure Parameter?

Postby Pinturiccio » Tue 10 Jun 2014 15:25

mt_1990 wrote:Is there a way to get string from DbCommand using dotConnect?

Please describe what exactly do you want to get from DbCommand.
1. If you want to get the query string, the CommandText property allows you to get or the set query text of DbCommand. For more information, please refer to http://www.devart.com/dotconnect/postgresql/docs/?Devart.Data.PostgreSql~Devart.Data.PostgreSql.PgSqlCommand~CommandText.html
2. If your query returns a string, and you want to get it, you can do it in several ways, for example using DataTable or DbDataReader. For more information, please refer to http://www.devart.com/dotconnect/postgresql/docs/?Retrieving.html

mt_1990 wrote:Or safe SQL escaping function?

Please describe what you meant in more details.
Pinturiccio
Devart Team
 
Posts: 2020
Joined: Wed 02 Nov 2011 09:44

Re: Parameterized Query as Procedure Parameter?

Postby mt_1990 » Tue 10 Jun 2014 19:52

Code: Select all
PgSqlCommand command = new PgSqlCommand();
command.CommandText = "SELECT * FROM a WHERE subject like @SUBJECT";
command.Parameters.AddWithValue("@SUBJECT", "String with possible sql injection.");

Is there a secure function which returns SQL query from command?

Expecting result:
Code: Select all
"SELECT * FROM a WHERE subject like 'String with possible sql injection.'";
mt_1990
 
Posts: 3
Joined: Tue 03 Jun 2014 15:45

Re: Parameterized Query as Procedure Parameter?

Postby Pinturiccio » Wed 11 Jun 2014 15:47

mt_1990 wrote:Is there a secure function which returns SQL query from command?

Expecting result:
Code: Select all
"SELECT * FROM a WHERE subject like 'String with possible sql injection.'";

PostgreSQL does not allow getting a query string of such kind. You create the following query:
Code: Select all
SELECT * FROM a WHERE subject like @SUBJECT

After the command is prepared, it looks like the following:
Code: Select all
SELECT * FROM a WHERE subject like $1

The server returns a handle to this statement to the application. When executing the statement again each time only parameter value is sent to the server. It leads to the following:
1. The query with the parameter value, like "SELECT * FROM a WHERE subject like 'String with possible sql injection.'", cannot be retrieved. It doesn't exist in such a form.
2. When sending a parameter value, this value is processed as a string. Even if this string contains an SQL statement, it will not be executed, it will be processed as a string.
Pinturiccio
Devart Team
 
Posts: 2020
Joined: Wed 02 Nov 2011 09:44


Return to dotConnect for PostgreSQL