Parameterized Query as Procedure Parameter?

[mt_1990]

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?

[Pinturiccio]

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.

[mt_1990]

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?

[Pinturiccio]

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/postgr ... dText.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/postgr ... eving.html

Or safe SQL escaping function?

Please describe what you meant in more details.

[mt_1990]

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.'";

[Pinturiccio]

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.