How to use parameterized queries in SQLite.Net

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

How to use parameterized queries in SQLite.Net

Rob Richardson-3
Hello again.

Since my attempt to find the official answer for myself has hit a snag, I'll just ask here.

The examples I've seen for parameterized queries used with the SQLiteCommand class have shown named parameters, and the names usually begin with an "@" character.  Is that character required for named parameters?  Is that the correct leading character?  Is it required to include that leading character in the name given to the SQLiteParameter object?

I'm used to using the System.Data.ODBC classes, which do not support named parameters, but they do support unnamed parameters, represented by question marks.  The order in which the parameters are attached to the command object determines the association between the parameter object and the query parameter.  Unnamed parameters would be easier for me to work with than named ones.  Does SQlite.Net support unnamed parameters?

Thank you.

RobR


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to use parameterized queries in SQLite.Net

Rob Richardson-3
To answer my own question:  this works:

            using (SQLiteCommand command = m_conn.CreateCommand())
            {
                command.CommandType = CommandType.Text;
                command.CommandText = "INSERT INTO trend_data (tag_key, value, value_timestamp) VALUES (?, ?, ?)";
                SQLiteParameter param;
                param = new SQLiteParameter();
                param.Value = 2;
                command.Parameters.Add(param);
                param = new SQLiteParameter();
                param.Value = 234.56;
                command.Parameters.Add(param);
                param = new SQLiteParameter();
                param.Value = DateTime.Now;
                command.Parameters.Add(param);
                rowsAffected = command.ExecuteNonQuery();
            }

RobR

-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Rob Richardson
Sent: Monday, March 13, 2017 2:23 PM
To: General Discussion of SQLite Database ([hidden email])
Subject: [sqlite] How to use parameterized queries in SQLite.Net

Hello again.

Since my attempt to find the official answer for myself has hit a snag, I'll just ask here.

The examples I've seen for parameterized queries used with the SQLiteCommand class have shown named parameters, and the names usually begin with an "@" character.  Is that character required for named parameters?  Is that the correct leading character?  Is it required to include that leading character in the name given to the SQLiteParameter object?

I'm used to using the System.Data.ODBC classes, which do not support named parameters, but they do support unnamed parameters, represented by question marks.  The order in which the parameters are attached to the command object determines the association between the parameter object and the query parameter.  Unnamed parameters would be easier for me to work with than named ones.  Does SQlite.Net support unnamed parameters?

Thank you.

RobR


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to use parameterized queries in SQLite.Net

J. King-3
On March 13, 2017 4:15:57 PM EDT, Rob Richardson <[hidden email]> wrote:

>To answer my own question:  this works:
>
>            using (SQLiteCommand command = m_conn.CreateCommand())
>            {
>                command.CommandType = CommandType.Text;
>command.CommandText = "INSERT INTO trend_data (tag_key, value,
>value_timestamp) VALUES (?, ?, ?)";
>                SQLiteParameter param;
>                param = new SQLiteParameter();
>                param.Value = 2;
>                command.Parameters.Add(param);
>                param = new SQLiteParameter();
>                param.Value = 234.56;
>                command.Parameters.Add(param);
>                param = new SQLiteParameter();
>                param.Value = DateTime.Now;
>                command.Parameters.Add(param);
>                rowsAffected = command.ExecuteNonQuery();
>            }
>
>RobR
>
>-----Original Message-----
>From: sqlite-users
>[mailto:[hidden email]] On Behalf Of Rob
>Richardson
>Sent: Monday, March 13, 2017 2:23 PM
>To: General Discussion of SQLite Database
>([hidden email])
>Subject: [sqlite] How to use parameterized queries in SQLite.Net
>
>Hello again.
>
>Since my attempt to find the official answer for myself has hit a snag,
>I'll just ask here.
>
>The examples I've seen for parameterized queries used with the
>SQLiteCommand class have shown named parameters, and the names usually
>begin with an "@" character.  Is that character required for named
>parameters?  Is that the correct leading character?  Is it required to
>include that leading character in the name given to the SQLiteParameter
>object?
>
>I'm used to using the System.Data.ODBC classes, which do not support
>named parameters, but they do support unnamed parameters, represented
>by question marks.  The order in which the parameters are attached to
>the command object determines the association between the parameter
>object and the query parameter.  Unnamed parameters would be easier for
>me to work with than named ones.  Does SQlite.Net support unnamed
>parameters?
>
>Thank you.
>
>RobR
>
>
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

In case it's useful, see here for all your options:
<http://sqlite.org/lang_expr.html#varparam>
--
J. King
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to use parameterized queries in SQLite.Net

Chris Locke-3
In reply to this post by Rob Richardson-3
From a newbie's point of view, how is this better (if doing it in 'hard
coded' format like below) than writing this code:

command.CommandText = string.format("INSERT INTO trend_data (tag_key,
value, value_timestamp) VALUES ({0}, {1}, {2})",2,234.56,now);

I can sort of understand it if its in a subroutine, and I appreciate the
example given was just an example, but whats the advantage of parametized
queries?

Sorry if diverting the topic somewhat....


Thanks,
Chris

I

On Mon, Mar 13, 2017 at 8:15 PM, Rob Richardson <[hidden email]>
wrote:

> To answer my own question:  this works:
>
>             using (SQLiteCommand command = m_conn.CreateCommand())
>             {
>                 command.CommandType = CommandType.Text;
>                 command.CommandText = "INSERT INTO trend_data (tag_key,
> value, value_timestamp) VALUES (?, ?, ?)";
>                 SQLiteParameter param;
>                 param = new SQLiteParameter();
>                 param.Value = 2;
>                 command.Parameters.Add(param);
>                 param = new SQLiteParameter();
>                 param.Value = 234.56;
>                 command.Parameters.Add(param);
>                 param = new SQLiteParameter();
>                 param.Value = DateTime.Now;
>                 command.Parameters.Add(param);
>                 rowsAffected = command.ExecuteNonQuery();
>             }
>
> RobR
>
> -----Original Message-----
> From: sqlite-users [mailto:[hidden email]]
> On Behalf Of Rob Richardson
> Sent: Monday, March 13, 2017 2:23 PM
> To: General Discussion of SQLite Database (sqlite-users@mailinglists.
> sqlite.org)
> Subject: [sqlite] How to use parameterized queries in SQLite.Net
>
> Hello again.
>
> Since my attempt to find the official answer for myself has hit a snag,
> I'll just ask here.
>
> The examples I've seen for parameterized queries used with the
> SQLiteCommand class have shown named parameters, and the names usually
> begin with an "@" character.  Is that character required for named
> parameters?  Is that the correct leading character?  Is it required to
> include that leading character in the name given to the SQLiteParameter
> object?
>
> I'm used to using the System.Data.ODBC classes, which do not support named
> parameters, but they do support unnamed parameters, represented by question
> marks.  The order in which the parameters are attached to the command
> object determines the association between the parameter object and the
> query parameter.  Unnamed parameters would be easier for me to work with
> than named ones.  Does SQlite.Net support unnamed parameters?
>
> Thank you.
>
> RobR
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to use parameterized queries in SQLite.Net

Hick Gunter
A parameterized query enables you to run a fixed query with arbitrary data that is unknown during compile time, multiple times (once for each set of parameters), without re-preparing the statement (which is costly) in between.

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Chris Locke
Gesendet: Dienstag, 14. März 2017 07:53
An: SQLite mailing list <[hidden email]>
Betreff: Re: [sqlite] How to use parameterized queries in SQLite.Net

From a newbie's point of view, how is this better (if doing it in 'hard coded' format like below) than writing this code:

command.CommandText = string.format("INSERT INTO trend_data (tag_key, value, value_timestamp) VALUES ({0}, {1}, {2})",2,234.56,now);

I can sort of understand it if its in a subroutine, and I appreciate the example given was just an example, but whats the advantage of parametized queries?

Sorry if diverting the topic somewhat....


Thanks,
Chris

I

On Mon, Mar 13, 2017 at 8:15 PM, Rob Richardson <[hidden email]>
wrote:

> To answer my own question:  this works:
>
>             using (SQLiteCommand command = m_conn.CreateCommand())
>             {
>                 command.CommandType = CommandType.Text;
>                 command.CommandText = "INSERT INTO trend_data
> (tag_key, value, value_timestamp) VALUES (?, ?, ?)";
>                 SQLiteParameter param;
>                 param = new SQLiteParameter();
>                 param.Value = 2;
>                 command.Parameters.Add(param);
>                 param = new SQLiteParameter();
>                 param.Value = 234.56;
>                 command.Parameters.Add(param);
>                 param = new SQLiteParameter();
>                 param.Value = DateTime.Now;
>                 command.Parameters.Add(param);
>                 rowsAffected = command.ExecuteNonQuery();
>             }
>
> RobR
>
> -----Original Message-----
> From: sqlite-users
> [mailto:[hidden email]]
> On Behalf Of Rob Richardson
> Sent: Monday, March 13, 2017 2:23 PM
> To: General Discussion of SQLite Database (sqlite-users@mailinglists.
> sqlite.org)
> Subject: [sqlite] How to use parameterized queries in SQLite.Net
>
> Hello again.
>
> Since my attempt to find the official answer for myself has hit a
> snag, I'll just ask here.
>
> The examples I've seen for parameterized queries used with the
> SQLiteCommand class have shown named parameters, and the names usually
> begin with an "@" character.  Is that character required for named
> parameters?  Is that the correct leading character?  Is it required to
> include that leading character in the name given to the
> SQLiteParameter object?
>
> I'm used to using the System.Data.ODBC classes, which do not support
> named parameters, but they do support unnamed parameters, represented
> by question marks.  The order in which the parameters are attached to
> the command object determines the association between the parameter
> object and the query parameter.  Unnamed parameters would be easier
> for me to work with than named ones.  Does SQlite.Net support unnamed parameters?
>
> Thank you.
>
> RobR
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: [hidden email]

This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to use parameterized queries in SQLite.Net

Clemens Ladisch
In reply to this post by Chris Locke-3
Chris Locke wrote:
> From a newbie's point of view, how is this better (if doing it in 'hard
> coded' format like below) than writing this code:
>
> command.CommandText = string.format("INSERT INTO trend_data (tag_key,
> value, value_timestamp) VALUES ({0}, {1}, {2})",2,234.56,now);

Using parameters is not too much of an improvement in a case like this.

But when you have strings (or values that _could_ be strings because you
don't completely control their source), you have to format them
correctly (many people forget escaping quotes), and you run the risk of
SQL injections: <http://bobby-tables.com/>.

And when you already have to use parameters for any query with strings,
it's a good habit to use them everywhere.


Handling parameters is excessively complex in .NET.  It might be a good
idea to write a helper format that is as simple as format():

  db.execute("INSERT INTO tab VALUES (?, ?, ?)", 123, name, now);


Regards,
Clemens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to use parameterized queries in SQLite.Net

Graham Holden
In reply to this post by Rob Richardson-3
The main reason you should parameterise queries is to protect against "SQL injection".  "Hardcoded" as below doesn't make much difference, but if the data being used comes in any way from an "untrusted" source, then this is particularly important.
If, instead of "234.56" below a malicious user could arrange to pass something like "2, '14/3/2017'); drop trend_data" then horrible things might happen!
Using parameters stops this, because no (SQL) parsing of the parameter value happens.
Graham. 


Sent from my Samsung Galaxy S7 - powered by Three
-------- Original message --------From: Chris Locke <[hidden email]> Date: 14/03/2017  06:52  (GMT+00:00) To: SQLite mailing list <[hidden email]> Subject: Re: [sqlite] How to use parameterized queries in SQLite.Net
From a newbie's point of view, how is this better (if doing it in 'hard
coded' format like below) than writing this code:

command.CommandText = string.format("INSERT INTO trend_data (tag_key,
value, value_timestamp) VALUES ({0}, {1}, {2})",2,234.56,now);

I can sort of understand it if its in a subroutine, and I appreciate the
example given was just an example, but whats the advantage of parametized
queries?

Sorry if diverting the topic somewhat....
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to use parameterized queries in SQLite.Net

Keith Medcalf
In reply to this post by Chris Locke-3

Cannot resist the classic response as to why one should use parameters rather than inline substitution:

https://xkcd.com/327/


> -----Original Message-----
> From: sqlite-users [mailto:[hidden email]]
> On Behalf Of Chris Locke
> Sent: Tuesday, 14 March, 2017 00:53
> To: SQLite mailing list
> Subject: Re: [sqlite] How to use parameterized queries in SQLite.Net
>
> From a newbie's point of view, how is this better (if doing it in 'hard
> coded' format like below) than writing this code:
>
> command.CommandText = string.format("INSERT INTO trend_data (tag_key,
> value, value_timestamp) VALUES ({0}, {1}, {2})",2,234.56,now);
>
> I can sort of understand it if its in a subroutine, and I appreciate the
> example given was just an example, but whats the advantage of parametized
> queries?
>
> Sorry if diverting the topic somewhat....
>
>
> Thanks,
> Chris
>
> I
>
> On Mon, Mar 13, 2017 at 8:15 PM, Rob Richardson <[hidden email]>
> wrote:
>
> > To answer my own question:  this works:
> >
> >             using (SQLiteCommand command = m_conn.CreateCommand())
> >             {
> >                 command.CommandType = CommandType.Text;
> >                 command.CommandText = "INSERT INTO trend_data (tag_key,
> > value, value_timestamp) VALUES (?, ?, ?)";
> >                 SQLiteParameter param;
> >                 param = new SQLiteParameter();
> >                 param.Value = 2;
> >                 command.Parameters.Add(param);
> >                 param = new SQLiteParameter();
> >                 param.Value = 234.56;
> >                 command.Parameters.Add(param);
> >                 param = new SQLiteParameter();
> >                 param.Value = DateTime.Now;
> >                 command.Parameters.Add(param);
> >                 rowsAffected = command.ExecuteNonQuery();
> >             }
> >
> > RobR
> >
> > -----Original Message-----
> > From: sqlite-users [mailto:[hidden email]]
> > On Behalf Of Rob Richardson
> > Sent: Monday, March 13, 2017 2:23 PM
> > To: General Discussion of SQLite Database (sqlite-users@mailinglists.
> > sqlite.org)
> > Subject: [sqlite] How to use parameterized queries in SQLite.Net
> >
> > Hello again.
> >
> > Since my attempt to find the official answer for myself has hit a snag,
> > I'll just ask here.
> >
> > The examples I've seen for parameterized queries used with the
> > SQLiteCommand class have shown named parameters, and the names usually
> > begin with an "@" character.  Is that character required for named
> > parameters?  Is that the correct leading character?  Is it required to
> > include that leading character in the name given to the SQLiteParameter
> > object?
> >
> > I'm used to using the System.Data.ODBC classes, which do not support
> named
> > parameters, but they do support unnamed parameters, represented by
> question
> > marks.  The order in which the parameters are attached to the command
> > object determines the association between the parameter object and the
> > query parameter.  Unnamed parameters would be easier for me to work with
> > than named ones.  Does SQlite.Net support unnamed parameters?
> >
> > Thank you.
> >
> > RobR
> >
> >
> > _______________________________________________
> > sqlite-users mailing list
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> > _______________________________________________
> > sqlite-users mailing list
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users