#1 2023-02-19 17:17:26

senad
Member
Registered: 2023-02-08
Posts: 5

Prepare expected 1 parameters in request, found 0

Hi,
Whats wrong with this SQL query?
I have simple test Table (FIREBIRD):

CREATE TABLE NEW_TABLE (
    ID        INTEGER NOT NULL,
    DESCRIPT  VARCHAR(100)
);

i'm getting an error when trying to insert single quotes and question marks in DESCRIPT

Props.ExecuteInlined('insert into NEW_TABLE (ID, DESCRIPT) values (4, ' + QuotedStr('I''m confused?') + ');', False);

But, there is no parameters in this simple query.

Problem seems to be in "mormot.db.sql.ReplaceParamsByNames"?!

Offline

#2 2023-02-19 20:14:44

tbo
Member
Registered: 2015-04-20
Posts: 336

Re: Prepare expected 1 parameters in request, found 0

Try it with this:

uses
  mormot.core.json
...
ExecuteInlined(FormatUtf8('insert into NEW_TABLE (%, %) values (?, ?)', ['ID', 'DESCRIPT'], [4, 'I''m confused?']), False);

With best regards
Thomas

Offline

#3 2023-02-20 10:16:07

senad
Member
Registered: 2023-02-08
Posts: 5

Re: Prepare expected 1 parameters in request, found 0

Thank you for your suggestion, but I'm getting sql-query from other system.
I just want to execute queries.

Offline

#4 2023-02-20 11:38:07

mpv
Member
From: Ukraine
Registered: 2012-03-24
Posts: 1,550
Website

Re: Prepare expected 1 parameters in request, found 0

Problem is what db layer expect `?` is a parameter placeholder.
For ExecuteInlined use inlined parameters syntax  :(paramValue):

But to to prevent SQL injection and speed up excution better to execute query with parameters:

stmt := conn.NewStatementPrepared('insert into NEW_TABLE (ID, DESCRIPT) values (?, ?)', false, true)
stmt.Bind(1, 4);
stmt.Bind(2, 'I''m confused?');
stmt.ExecutePrepared;

Last edited by mpv (2023-02-20 11:38:58)

Offline

#5 2023-02-20 12:47:46

senad
Member
Registered: 2023-02-08
Posts: 5

Re: Prepare expected 1 parameters in request, found 0

Problem is not in `?`, problem is if DESCRIPT contains single quotes ' and question marks ?
Example 1 - success:

Props.ExecuteInlined('insert into NEW_TABLE (ID, DESCRIPT) values (4, ' + QuotedStr('I''m confused') + ');', False);

Example 2 - success:

Props.ExecuteInlined('insert into NEW_TABLE (ID, DESCRIPT) values (4, ' + QuotedStr('Im confused?') + ');', False);

Example 3 - error:

Props.ExecuteInlined('insert into NEW_TABLE (ID, DESCRIPT) values (4, ' + QuotedStr('I''m confused?') + ');', False);

Offline

#6 2023-02-20 13:05:57

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,280
Website

Re: Prepare expected 1 parameters in request, found 0

ExecuteInlined() is to execute INLINED statements, i.e. statements with proper

:(...):

place holders, in which ? parameters are detected, parsed, and bound as values.

Use Props.Execute() for your case.
And use inline parameters, don't put the value within the SQL statement: it is unsafe, and subject to SQL injection.
It is the 101 of SQL security.

Offline

Board footer

Powered by FluxBB