mORMot Open Source

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"?!

tbo

Member

Registered: 2015-04-20

Posts: 353

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

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.

mpv

Member

From: Ukraine

Registered: 2012-03-24

Posts: 1,571

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)

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);

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

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.