sqlite as server queries

classic Classic list List threaded Threaded
27 messages Options
12
Reply | Threaded
Open this post in threaded view
|

sqlite as server queries

Edwin Eyan Moragas
hi group,

i have several small questions for the group any
experiences or thoughts shared would be greatly
appreciated.

1) anybody used sqlite as a sql server? i'm thinking
of say using the embedded sqlite in PHP5 or similar.

2) anybody ever implemented something like a single
process of sqlite doing queries for a lot of networked
clients?

3) how big has your sqlite database grown? have you had any trouble
managing the db? any bad experiences as to stability of
the db file?

i am asking all of these is because i'm seriously considering
sqlite to be used as my sql server for a project.

how i'll implement it looks something like this:

components of app:
embedded web server
sqlite
some scripting language

there will only be one sqlite process which will be forked
when the web server is launched. queries to sqlite will
be sent to the sqlite process via sockets.

i can see that the queries will be sequential. no problems
there. i'm not worried with speed at the moment. i just want
to know if this has been done before and i'd like
to solicit wisdom from the group.

thank you.

./e


--
no sig

-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

a c++ newbie question

Stephen Sutherland
Hi ;
   
  I am trying to treat a string before passing it through my SQL statement into the database.
   
  I know that a  single apostrophe will break the SQL statement.
  So I have to replace them all to double apostrophes.
  Question #1:
  What may I ask is the c or C++ code to accomplish that ?
  Initially I was using this simple scheme.
   
  string str2("stephen's test . Bob's test");
   
  if (  str2.find("'"!= string::npos)
{
     str2.replace(str2.find('"), 1, "''");
  }
  I know this doens't test for multiple single apostrophes.
   
  But are there any other characters that will break the SQL statement ?
   
  Has anyone  created a nice algorithm?
I actually have a situation where the user creates an XML file and the contents of the XML file gets dumped in the database. So there is opportunity for a hacker to create an XML file which has some SQL statements in it like ' DELETE TABLE X ;
   
  So any thoughts or existing code would be great.
   
  Thanks
   
  Stev

       
---------------------------------
Luggage? GPS? Comic books?
Check out fitting  gifts for grads at Yahoo! Search.
Reply | Threaded
Open this post in threaded view
|

Re: a c++ newbie question

Trevor Talbot-2
On 8/5/07, Stephen Sutherland <[hidden email]> wrote:

>   I am trying to treat a string before passing it through my SQL statement into the database.
>
>   I know that a  single apostrophe will break the SQL statement.
>   So I have to replace them all to double apostrophes.

>   But are there any other characters that will break the SQL statement ?

> I actually have a situation where the user creates an XML file and the contents of the XML file gets dumped in the database. So there is opportunity for a hacker to create an XML file which has some SQL statements in it like ' DELETE TABLE X ;
>
>   So any thoughts or existing code would be great.

Don't attempt to treat strings at all.  Instead, always use the
parametric binding API for whatever database you're using.  You
prepare statements like "INSERT INTO table VALUES (?)", and then pass
in the input string as a separate argument for the database engine to
put in place of the "?".  This avoids the entire problem of escaping
special characters, and you don't need to treat your input data
specially.

For sqlite, use sqlite3_prepare_v2() and sqlite3_bind_text().
http://sqlite.org/capi3.html should get you up to speed on the
process, and browse through the other documents on the site for more
information.

-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: a c++ newbie question

Eugene Wee
In reply to this post by Stephen Sutherland
Hi Stev,

Why not just use prepared statements?

Regards,
Eugene Wee

Stephen Sutherland wrote:

> Hi ;
>    
>   I am trying to treat a string before passing it through my SQL statement into the database.
>    
>   I know that a  single apostrophe will break the SQL statement.
>   So I have to replace them all to double apostrophes.
>   Question #1:
>   What may I ask is the c or C++ code to accomplish that ?
>   Initially I was using this simple scheme.
>    
>   string str2("stephen's test . Bob's test");
>    
>   if (  str2.find("'"!= string::npos)
> {
>      str2.replace(str2.find('"), 1, "''");
>   }
>   I know this doens't test for multiple single apostrophes.
>    
>   But are there any other characters that will break the SQL statement ?
>    
>   Has anyone  created a nice algorithm?
> I actually have a situation where the user creates an XML file and the contents of the XML file gets dumped in the database. So there is opportunity for a hacker to create an XML file which has some SQL statements in it like ' DELETE TABLE X ;
>    
>   So any thoughts or existing code would be great.
>    
>   Thanks
>    
>   Stev

-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: a c++ newbie question

Bharath Booshan L
In reply to this post by Stephen Sutherland
Hi Stev,

 Prepared statements are best option, however try sqlite3_mprintf() with
'%q' as format specifier which escapes every '\' character.

Find more info in http://sqlite.org/capi3ref.html.

Bharath Booshan L.


On 8/6/07 11:50 AM, "Stephen Sutherland" <[hidden email]>
wrote:

> Hi ;
>    
>   I am trying to treat a string before passing it through my SQL statement
> into the database.
>    
>   I know that a  single apostrophe will break the SQL statement.
>   So I have to replace them all to double apostrophes.
>   Question #1:
>   What may I ask is the c or C++ code to accomplish that ?
>   Initially I was using this simple scheme.
>    
>   string str2("stephen's test . Bob's test");
>    
>   if (  str2.find("'"!= string::npos)
> {
>      str2.replace(str2.find('"), 1, "''");
>   }
>   I know this doens't test for multiple single apostrophes.
>    
>   But are there any other characters that will break the SQL statement ?
>    
>   Has anyone  created a nice algorithm?
> I actually have a situation where the user creates an XML file and the
> contents of the XML file gets dumped in the database. So there is opportunity
> for a hacker to create an XML file which has some SQL statements in it like '
> DELETE TABLE X ;
>    
>   So any thoughts or existing code would be great.
>    
>   Thanks
>    
>   Stev
>
>        
> ---------------------------------
> Luggage? GPS? Comic books?
> Check out fitting  gifts for grads at Yahoo! Search.



-----------------------------------------------
Robosoft Technologies - Come home to Technology

Disclaimer: This email may contain confidential material. If you were not an intended recipient, please notify the sender and delete all copies. Emails to and from our network may be logged and monitored. This email and its attachments are scanned for virus by our scanners and are believed to be safe. However, no warranty is given that this email is free of malicious content or virus.



-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Best Match Query

RaghavendraK 70574
In reply to this post by Trevor Talbot-2
Hi,

How to form the SQL query(in SQLite) for the following problem below

table:
44
442
4454

Input String: 4429845

Expected output from SQL query: 442

regards
ragha


******************************************************************************************
 This email and its attachments contain confidential information from HUAWEI, which is intended only for the person or entity whose address is listed above. Any use of the information contained herein in any way (including, but not limited to, total or partial disclosure, reproduction, or dissemination) by persons other than the intended recipient(s) is prohibited. If you receive this e-mail in error, please notify the sender by phone or email immediately and delete it!
 *****************************************************************************************

----- Original Message -----
From: Trevor Talbot <[hidden email]>
Date: Monday, August 6, 2007 2:39 pm
Subject: Re: [sqlite] a c++ newbie question

> On 8/5/07, Stephen Sutherland <[hidden email]> wrote:
>
> >   I am trying to treat a string before passing it through my SQL
> statement into the database.
> >
> >   I know that a  single apostrophe will break the SQL statement.
> >   So I have to replace them all to double apostrophes.
>
> >   But are there any other characters that will break the SQL
> statement ?
>
> > I actually have a situation where the user creates an XML file
> and the contents of the XML file gets dumped in the database. So
> there is opportunity for a hacker to create an XML file which has
> some SQL statements in it like ' DELETE TABLE X ;
> >
> >   So any thoughts or existing code would be great.
>
> Don't attempt to treat strings at all.  Instead, always use the
> parametric binding API for whatever database you're using.  You
> prepare statements like "INSERT INTO table VALUES (?)", and then pass
> in the input string as a separate argument for the database engine to
> put in place of the "?".  This avoids the entire problem of escaping
> special characters, and you don't need to treat your input data
> specially.
>
> For sqlite, use sqlite3_prepare_v2() and sqlite3_bind_text().
> http://sqlite.org/capi3.html should get you up to speed on the
> process, and browse through the other documents on the site for more
> information.
>
> -------------------------------------------------------------------
> ----------
> To unsubscribe, send email to [hidden email]
> -------------------------------------------------------------------
> ----------
>
>

-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: Best Match Query

SimonDavies
Hi Ragha,

C:\Joinerysoft\JMS\TestArea>sqlite3  tst.db
SQLite version 3.4.0
Enter ".help" for instructions
sqlite>
sqlite> create table tst( c1 integer, c2 test );
sqlite> insert into tst values( 1, '44' );
sqlite> insert into tst values( 2, '442' );
sqlite> insert into tst values( 3, '4454' );
sqlite>
sqlite> select * from tst where '4429845' > cast( c2 as text ) order
by c2 desc limit 1;
2|442

Maybe not the most efficient way, but seems to give requested result...

Rgds
Simon


On 06/08/07, RaghavendraK 70574 <[hidden email]> wrote:

> Hi,
>
> How to form the SQL query(in SQLite) for the following problem below
>
> table:
> 44
> 442
> 4454
>
> Input String: 4429845
>
> Expected output from SQL query: 442
>
> regards
> ragha
>
>
> ******************************************************************************************
>  This email and its attachments contain confidential information from HUAWEI, which is intended only for the person or entity whose address is listed above. Any use of the information contained herein in any way (including, but not limited to, total or partial disclosure, reproduction, or dissemination) by persons other than the intended recipient(s) is prohibited. If you receive this e-mail in error, please notify the sender by phone or email immediately and delete it!
>  *****************************************************************************************
>
> ----- Original Message -----
> From: Trevor Talbot <[hidden email]>
> Date: Monday, August 6, 2007 2:39 pm
> Subject: Re: [sqlite] a c++ newbie question
>
> > On 8/5/07, Stephen Sutherland <[hidden email]> wrote:
> >
> > >   I am trying to treat a string before passing it through my SQL
> > statement into the database.
> > >
> > >   I know that a  single apostrophe will break the SQL statement.
> > >   So I have to replace them all to double apostrophes.
> >
> > >   But are there any other characters that will break the SQL
> > statement ?
> >
> > > I actually have a situation where the user creates an XML file
> > and the contents of the XML file gets dumped in the database. So
> > there is opportunity for a hacker to create an XML file which has
> > some SQL statements in it like ' DELETE TABLE X ;
> > >
> > >   So any thoughts or existing code would be great.
> >
> > Don't attempt to treat strings at all.  Instead, always use the
> > parametric binding API for whatever database you're using.  You
> > prepare statements like "INSERT INTO table VALUES (?)", and then pass
> > in the input string as a separate argument for the database engine to
> > put in place of the "?".  This avoids the entire problem of escaping
> > special characters, and you don't need to treat your input data
> > specially.
> >
> > For sqlite, use sqlite3_prepare_v2() and sqlite3_bind_text().
> > http://sqlite.org/capi3.html should get you up to speed on the
> > process, and browse through the other documents on the site for more
> > information.
> >
> > -------------------------------------------------------------------
> > ----------
> > To unsubscribe, send email to [hidden email]
> > -------------------------------------------------------------------
> > ----------
> >
> >
>
> -----------------------------------------------------------------------------
> To unsubscribe, send email to [hidden email]
> -----------------------------------------------------------------------------
>
>

-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: Best Match Query

RaghavendraK 70574
Thanks .

I found similar one in the mailing list archive.
create table test (t text);

insert into test values ('9');
insert into test values ('98');
insert into test values ('986');
insert into test values ('9867');

select * from test where '98555'  like t || '%' order by t desc limit 1;

output: 98.


regards
ragha

******************************************************************************************
 This email and its attachments contain confidential information from HUAWEI, which is intended only for the person or entity whose address is listed above. Any use of the information contained herein in any way (including, but not limited to, total or partial disclosure, reproduction, or dissemination) by persons other than the intended recipient(s) is prohibited. If you receive this e-mail in error, please notify the sender by phone or email immediately and delete it!
 *****************************************************************************************

----- Original Message -----
From: Simon Davies <[hidden email]>
Date: Monday, August 6, 2007 8:12 pm
Subject: Re: [sqlite] Best Match Query

> Hi Ragha,
>
> C:\Joinerysoft\JMS\TestArea>sqlite3  tst.db
> SQLite version 3.4.0
> Enter ".help" for instructions
> sqlite>
> sqlite> create table tst( c1 integer, c2 test );
> sqlite> insert into tst values( 1, '44' );
> sqlite> insert into tst values( 2, '442' );
> sqlite> insert into tst values( 3, '4454' );
> sqlite>
> sqlite> select * from tst where '4429845' > cast( c2 as text ) order
> by c2 desc limit 1;
> 2|442
>
> Maybe not the most efficient way, but seems to give requested
> result...
> Rgds
> Simon
>
>
> On 06/08/07, RaghavendraK 70574 <[hidden email]> wrote:
> > Hi,
> >
> > How to form the SQL query(in SQLite) for the following problem below
> >
> > table:
> > 44
> > 442
> > 4454
> >
> > Input String: 4429845
> >
> > Expected output from SQL query: 442
> >
> > regards
> > ragha
> >
> >
> >
> ******************************************************************************************>  This email and its attachments contain confidential information from HUAWEI, which is intended only for the person or entity whose address is listed above. Any use of the information contained herein in any way (including, but not limited to, total or partial disclosure, reproduction, or dissemination) by persons other than the intended recipient(s) is prohibited. If you receive this e-mail in error, please notify the sender by phone or email immediately and delete it!
> >  
> *****************************************************************************************>
> > ----- Original Message -----
> > From: Trevor Talbot <[hidden email]>
> > Date: Monday, August 6, 2007 2:39 pm
> > Subject: Re: [sqlite] a c++ newbie question
> >
> > > On 8/5/07, Stephen Sutherland <[hidden email]> wrote:
> > >
> > > >   I am trying to treat a string before passing it through my SQL
> > > statement into the database.
> > > >
> > > >   I know that a  single apostrophe will break the SQL statement.
> > > >   So I have to replace them all to double apostrophes.
> > >
> > > >   But are there any other characters that will break the SQL
> > > statement ?
> > >
> > > > I actually have a situation where the user creates an XML file
> > > and the contents of the XML file gets dumped in the database. So
> > > there is opportunity for a hacker to create an XML file which has
> > > some SQL statements in it like ' DELETE TABLE X ;
> > > >
> > > >   So any thoughts or existing code would be great.
> > >
> > > Don't attempt to treat strings at all.  Instead, always use the
> > > parametric binding API for whatever database you're using.  You
> > > prepare statements like "INSERT INTO table VALUES (?)", and
> then pass
> > > in the input string as a separate argument for the database
> engine to
> > > put in place of the "?".  This avoids the entire problem of
> escaping> > special characters, and you don't need to treat your
> input data
> > > specially.
> > >
> > > For sqlite, use sqlite3_prepare_v2() and sqlite3_bind_text().
> > > http://sqlite.org/capi3.html should get you up to speed on the
> > > process, and browse through the other documents on the site
> for more
> > > information.
> > >
> > > ---------------------------------------------------------------
> ----
> > > ----------
> > > To unsubscribe, send email to [hidden email]
> > > ---------------------------------------------------------------
> ----
> > > ----------
> > >
> > >
> >
> > -----------------------------------------------------------------
> ------------
> > To unsubscribe, send email to [hidden email]
> > -----------------------------------------------------------------
> ------------
> >
> >
>
> -------------------------------------------------------------------
> ----------
> To unsubscribe, send email to [hidden email]
> -------------------------------------------------------------------
> ----------
>
>

-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: sqlite as server queries

Christian Smith-4
In reply to this post by Edwin Eyan Moragas
Edwin Eyan Moragas uttered:

> hi group,
>
> i have several small questions for the group any
> experiences or thoughts shared would be greatly
> appreciated.
>
> 1) anybody used sqlite as a sql server? i'm thinking
> of say using the embedded sqlite in PHP5 or similar.
>
> 2) anybody ever implemented something like a single
> process of sqlite doing queries for a lot of networked
> clients?


A few people have implemented such a solution. It loses one of the
benefits of SQLite, however, in that SQLite is no longer admin free.


>
> 3) how big has your sqlite database grown? have you had any trouble
> managing the db? any bad experiences as to stability of
> the db file?


Stability of the file? In what sense? Compatibility? Or resistence to
corruption? Or size, perhaps?


>
> i am asking all of these is because i'm seriously considering
> sqlite to be used as my sql server for a project.
>
> how i'll implement it looks something like this:
>
> components of app:
> embedded web server
> sqlite
> some scripting language
>
> there will only be one sqlite process which will be forked
> when the web server is launched. queries to sqlite will
> be sent to the sqlite process via sockets.


You can do this, but you may find it easier to embed SQLite right into
your app, using whatever wrapper language binding your app is written in.
That is how it's designed to be used. It'll also be faster that way as
well, and easier to manage.


>
> i can see that the queries will be sequential. no problems there. i'm
> not worried with speed at the moment. i just want to know if this has
> been done before and i'd like to solicit wisdom from the group.


I have thoughts on wrapping SQLite with FreeTDS, in order to provide
networked access to legacy apps that expect a TDS server to talk to. But
that is more for legacy reasons. You don't have this legacy burden by the
sounds of it, so just embed SQLite.


>
> thank you.
>
> ./e
>


Christian

--
     /"\
     \ /    ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
      X                           - AGAINST MS ATTACHMENTS
     / \

-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: Best Match Query

Jim Dodgen
In reply to this post by RaghavendraK 70574
or with a "like" operator

sqlite> create table x (t);
sqlite> insert into x values (442);
sqlite> insert into x values (44);
sqlite> insert into x values (4454);
 select * from x where 4429845 like t||"%" order by length(t) desc limit 1;
442







Quoting RaghavendraK 70574 <[hidden email]>:

> Thanks .
>
> I found similar one in the mailing list archive.
> create table test (t text);
>
> insert into test values ('9');
> insert into test values ('98');
> insert into test values ('986');
> insert into test values ('9867');
>
> select * from test where '98555'  like t || '%' order by t desc limit 1;
>
> output: 98.
>
>
> regards
> ragha
>
>
*******************************************************************************
***********
>  This email and its attachments contain confidential information from HUAWEI,
> which is intended only for the person or entity whose address is listed
> above. Any use of the information contained herein in any way (including, but
> not limited to, total or partial disclosure, reproduction, or dissemination)
> by persons other than the intended recipient(s) is prohibited. If you receive
> this e-mail in error, please notify the sender by phone or email immediately
> and delete it!
>  
*******************************************************************************
**********

>
> ----- Original Message -----
> From: Simon Davies <[hidden email]>
> Date: Monday, August 6, 2007 8:12 pm
> Subject: Re: [sqlite] Best Match Query
>
> > Hi Ragha,
> >
> > C:\Joinerysoft\JMS\TestArea>sqlite3  tst.db
> > SQLite version 3.4.0
> > Enter ".help" for instructions
> > sqlite>
> > sqlite> create table tst( c1 integer, c2 test );
> > sqlite> insert into tst values( 1, '44' );
> > sqlite> insert into tst values( 2, '442' );
> > sqlite> insert into tst values( 3, '4454' );
> > sqlite>
> > sqlite> select * from tst where '4429845' > cast( c2 as text ) order
> > by c2 desc limit 1;
> > 2|442
> >
> > Maybe not the most efficient way, but seems to give requested
> > result...
> > Rgds
> > Simon
> >
> >
> > On 06/08/07, RaghavendraK 70574 <[hidden email]> wrote:
> > > Hi,
> > >
> > > How to form the SQL query(in SQLite) for the following problem below
> > >
> > > table:
> > > 44
> > > 442
> > > 4454
> > >
> > > Input String: 4429845
> > >
> > > Expected output from SQL query: 442
> > >
> > > regards
> > > ragha
> > >
> > >
> > >
> >
>
*******************************************************************************
***********>

>  This email and its attachments contain confidential information from HUAWEI,
> which is intended only for the person or entity whose address is listed
> above. Any use of the information contained herein in any way (including, but
> not limited to, total or partial disclosure, reproduction, or dissemination)
> by persons other than the intended recipient(s) is prohibited. If you receive
> this e-mail in error, please notify the sender by phone or email immediately
> and delete it!
> > >  
> >
>
*******************************************************************************
**********>

> > > ----- Original Message -----
> > > From: Trevor Talbot <[hidden email]>
> > > Date: Monday, August 6, 2007 2:39 pm
> > > Subject: Re: [sqlite] a c++ newbie question
> > >
> > > > On 8/5/07, Stephen Sutherland <[hidden email]> wrote:
> > > >
> > > > >   I am trying to treat a string before passing it through my SQL
> > > > statement into the database.
> > > > >
> > > > >   I know that a  single apostrophe will break the SQL statement.
> > > > >   So I have to replace them all to double apostrophes.
> > > >
> > > > >   But are there any other characters that will break the SQL
> > > > statement ?
> > > >
> > > > > I actually have a situation where the user creates an XML file
> > > > and the contents of the XML file gets dumped in the database. So
> > > > there is opportunity for a hacker to create an XML file which has
> > > > some SQL statements in it like ' DELETE TABLE X ;
> > > > >
> > > > >   So any thoughts or existing code would be great.
> > > >
> > > > Don't attempt to treat strings at all.  Instead, always use the
> > > > parametric binding API for whatever database you're using.  You
> > > > prepare statements like "INSERT INTO table VALUES (?)", and
> > then pass
> > > > in the input string as a separate argument for the database
> > engine to
> > > > put in place of the "?".  This avoids the entire problem of
> > escaping> > special characters, and you don't need to treat your
> > input data
> > > > specially.
> > > >
> > > > For sqlite, use sqlite3_prepare_v2() and sqlite3_bind_text().
> > > > http://sqlite.org/capi3.html should get you up to speed on the
> > > > process, and browse through the other documents on the site
> > for more
> > > > information.
> > > >
> > > > ---------------------------------------------------------------
> > ----
> > > > ----------
> > > > To unsubscribe, send email to [hidden email]
> > > > ---------------------------------------------------------------
> > ----
> > > > ----------
> > > >
> > > >
> > >
> > > -----------------------------------------------------------------
> > ------------
> > > To unsubscribe, send email to [hidden email]
> > > -----------------------------------------------------------------
> > ------------
> > >
> > >
> >
> > -------------------------------------------------------------------
> > ----------
> > To unsubscribe, send email to [hidden email]
> > -------------------------------------------------------------------
> > ----------
> >
> >
>
> -----------------------------------------------------------------------------
> To unsubscribe, send email to [hidden email]
> -----------------------------------------------------------------------------
>
>






-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: sqlite as server queries

John Stanton-3
In reply to this post by Edwin Eyan Moragas
Edwin Eyan Moragas wrote:

> hi group,
>
> i have several small questions for the group any
> experiences or thoughts shared would be greatly
> appreciated.
>
> 1) anybody used sqlite as a sql server? i'm thinking
> of say using the embedded sqlite in PHP5 or similar.
>
> 2) anybody ever implemented something like a single
> process of sqlite doing queries for a lot of networked
> clients?
>
> 3) how big has your sqlite database grown? have you had any trouble
> managing the db? any bad experiences as to stability of
> the db file?
>
> i am asking all of these is because i'm seriously considering
> sqlite to be used as my sql server for a project.
>
> how i'll implement it looks something like this:
>
> components of app:
> embedded web server
> sqlite
> some scripting language
>
> there will only be one sqlite process which will be forked
> when the web server is launched. queries to sqlite will
> be sent to the sqlite process via sockets.
>
> i can see that the queries will be sequential. no problems
> there. i'm not worried with speed at the moment. i just want
> to know if this has been done before and i'd like
> to solicit wisdom from the group.
>
> thank you.
>
> ./e
>
>
We use a single process server as an Sqlite server.  It works well
because it obeys certain constraints:
   o  Transactions are always short
   o  It has many users and many Sqlite databases, but each database
does not have a large number of users, or more correctly a large traffic
rate.
   o  Row scans are avoided on large tables.

The benefits are the ease of maintenance of multiple databases, each one
being just a file.  Transactions complete typically in less than 500uS.

We use HTTP protocol to access the Sqlite server, and it allocates one
or two threads to each user connection (a browser will try to open two
connections).

If you have large transactions or large numbers of users sharing a
database, consider using something like PostgreSQL, Oracle or DB/2.
Sqlite is inherently single streamed and that imposes a cap on its
ability to handle many simultaneous connections to a single database.

For a scripting language we implemented Javascript to be sympathetic to
application programmers already using JS in their web pages.  Sqlite can
execute Javascript functions and Javascript programs can call Sqlite.

We insulate the server from SQL injection attacks by storing a library
of SQL on the server in the form of RPCs and never sending it from the
client.

Finally, using a server to share Sqlite on a network is more robust than
using file sharing.

-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: a c++ newbie question

Stephen Sutherland
In reply to this post by Trevor Talbot-2
Everyone has suggested the use of prepared statements.
   
  I was actually inserting ~10,000 records with great results using BEGIN TRANSACTION; and  END TRANSACTION;  I was making 1 big string of insert statements and executing them all at once.
  It was extremely fast.
   
  How can I do the same with prepare statements ?
  Is it possible for me to prepare 10,000 in a loop and then surround them with BEGIN TRANSACTCION AND END TRANSACTION ?
   
  Actually I would appreciate a little code sample, if possible .
   
  Assume I have the following:
  char* param1="test1";
  char  param2='y';
  int     param3=1
  for(int x = 0; x < 10,000; x++ )
  {
  // how would it work ?
   
  }
   
  Thanks a lot in advance.
  Stephen
   
   
 

Trevor Talbot <[hidden email]> wrote:
  On 8/5/07, Stephen Sutherland wrote:

> I am trying to treat a string before passing it through my SQL statement into the database.
>
> I know that a single apostrophe will break the SQL statement.
> So I have to replace them all to double apostrophes.

> But are there any other characters that will break the SQL statement ?

> I actually have a situation where the user creates an XML file and the contents of the XML file gets dumped in the database. So there is opportunity for a hacker to create an XML file which has some SQL statements in it like ' DELETE TABLE X ;
>
> So any thoughts or existing code would be great.

Don't attempt to treat strings at all. Instead, always use the
parametric binding API for whatever database you're using. You
prepare statements like "INSERT INTO table VALUES (?)", and then pass
in the input string as a separate argument for the database engine to
put in place of the "?". This avoids the entire problem of escaping
special characters, and you don't need to treat your input data
specially.

For sqlite, use sqlite3_prepare_v2() and sqlite3_bind_text().
http://sqlite.org/capi3.html should get you up to speed on the
process, and browse through the other documents on the site for more
information.

-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------



       
---------------------------------
Building a website is a piece of cake.
Yahoo! Small Business gives you all the tools to get online.
Reply | Threaded
Open this post in threaded view
|

Re: a c++ newbie question

Eugene Wee
>   How can I do the same with prepare statements ?
>   Is it possible for me to prepare 10,000 in a loop and then surround them with BEGIN TRANSACTCION AND END TRANSACTION ?

Yes. As an added benefit the preparation would mean that the SQL
statement does not have to be parsed on each iteration of the loop.

>   Actually I would appreciate a little code sample, if possible .

This is a C++ example that does not do any error checking:

// Assume db is the database handle and stmt is the statement handle.
sqlite3_exec(db, "BEGIN TRANSACTION;", 0, 0, 0);
sqlite3_prepare_v2(db, "INSERT INTO foo (bar) VALUES (:bar);", -1,
&stmt, 0);
for (int i = 0; i < 10000; ++i)
{
     sqlite3_bind_int(stmt, 1, i); // Bind i to the first parameter.
     sqlite3_step(stmt); // Execute the statement.
     sqlite3_reset(stmt);
}
sqlite3_finalize(stmt);
sqlite3_exec(db, "COMMIT TRANSACTION;", 0, 0, 0);

Regards,
Eugene Wee

-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

RE: a c++ newbie question

Downey, Shawn
Does anyone have any experience compiling SQLite to run in the Micrium
operating system on an ARM9 platform?

http://www.micrium.com/

Thank you.

Shawn M. Downey
MPR Associates
10 Maxwell Drive, Suite 204
Clifton Park, New York 12065
518-831-7544 (work)
860-508-5015 (cell)

-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

SQLite on the Micrium OS on an ARM9

Downey, Shawn
In reply to this post by Eugene Wee
Does anyone have any experience compiling SQLite to run in the Micrium
operating system on an ARM9 platform?

http://www.micrium.com/

Thank you.

Shawn M. Downey
MPR Associates
10 Maxwell Drive, Suite 204
Clifton Park, New York 12065
518-831-7544 (work)
860-508-5015 (cell)

-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: sqlite as server queries

Edwin Eyan Moragas
In reply to this post by Christian Smith-4
On 8/6/07, Christian Smith <[hidden email]> wrote:
<snip>
> > 2) anybody ever implemented something like a single
> > process of sqlite doing queries for a lot of networked
> > clients?
>
>
> A few people have implemented such a solution. It loses one of the
> benefits of SQLite, however, in that SQLite is no longer admin free.

how so?

>
>
> >
> > 3) how big has your sqlite database grown? have you had any trouble
> > managing the db? any bad experiences as to stability of
> > the db file?
>
>
> Stability of the file? In what sense? Compatibility? Or resistence to
> corruption? Or size, perhaps?

resistance to corruption in particular. thinking about it, this
may be an OS issue but given that the OS is ok, how
does sqlite handle it?

>
>
> >
> > i am asking all of these is because i'm seriously considering
> > sqlite to be used as my sql server for a project.
> >
> > how i'll implement it looks something like this:
> >
> > components of app:
> > embedded web server
> > sqlite
> > some scripting language
> >
> > there will only be one sqlite process which will be forked
> > when the web server is launched. queries to sqlite will
> > be sent to the sqlite process via sockets.
>
>
> You can do this, but you may find it easier to embed SQLite right into
> your app, using whatever wrapper language binding your app is written in.
> That is how it's designed to be used. It'll also be faster that way as
> well, and easier to manage.

thinking about this some. yeah. maybe you're right.


<snip>


thank you for the response.

./e
--
no sig

-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: sqlite as server queries

Edwin Eyan Moragas
In reply to this post by John Stanton-3
On 8/6/07, John Stanton <[hidden email]> wrote:

> >
> We use a single process server as an Sqlite server.  It works well
> because it obeys certain constraints:
>    o  Transactions are always short
>    o  It has many users and many Sqlite databases, but each database
> does not have a large number of users, or more correctly a large traffic
> rate.
>    o  Row scans are avoided on large tables.
>
> The benefits are the ease of maintenance of multiple databases, each one
> being just a file.  Transactions complete typically in less than 500uS.

sounds good.

>
> We use HTTP protocol to access the Sqlite server, and it allocates one
> or two threads to each user connection (a browser will try to open two
> connections).
>
> If you have large transactions or large numbers of users sharing a
> database, consider using something like PostgreSQL, Oracle or DB/2.
> Sqlite is inherently single streamed and that imposes a cap on its
> ability to handle many simultaneous connections to a single database.

this is a personal project and i really want to use sqlite.
thanks for this. i'm a big pg fan.


<snip>

thank you.

./e
--
no sig

-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: sqlite as server queries

Gilles Ganault
In reply to this post by Christian Smith-4
At 14:08 06/08/2007 +0100, "Edwin Eyan Moragas" wrote:
>2) anybody ever implemented something like a single process of sqlite
>doing queries for a lot of networked clients?

Am working on this, in Classic VB5: Winsock control on the server, Inet
control on the client, the client thinks it's talking to a web server.

http://codecomplete.free.fr/sqlite/sample/

G.


-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: a c++ newbie question - prepared statements.

Stephen Sutherland
In reply to this post by Eugene Wee
Thanks this is great information on sqlite's prepared statements.
   
  I think I have just one more question on this subject.
   
  I need to execute a SQL statement like this:
  "SELECT * FROM tbl WHERE BookID IN ( :arrayNumbers) ;"
   
  My function will be receiving an array like this
   
  void getVariableBookIDs( int arraynumbers[], int alength )
  {
   
       // what's the best technique pass these array of numbers to the query?
       // can I use prepared statements ?
       // I won't be doing this too often - like within a tight loop so a regular sqlite3_exec would work as well.
  }
   
   
  Thanks in Advance.
   
  Stephen
   
   
   
 

Eugene Wee <[hidden email]> wrote:
  > How can I do the same with prepare statements ?
> Is it possible for me to prepare 10,000 in a loop and then surround them with BEGIN TRANSACTCION AND END TRANSACTION ?

Yes. As an added benefit the preparation would mean that the SQL
statement does not have to be parsed on each iteration of the loop.

> Actually I would appreciate a little code sample, if possible .

This is a C++ example that does not do any error checking:

// Assume db is the database handle and stmt is the statement handle.
sqlite3_exec(db, "BEGIN TRANSACTION;", 0, 0, 0);
sqlite3_prepare_v2(db, "INSERT INTO foo (bar) VALUES (:bar);", -1,
&stmt, 0);
for (int i = 0; i < 10000; ++i)
{
sqlite3_bind_int(stmt, 1, i); // Bind i to the first parameter.
sqlite3_step(stmt); // Execute the statement.
sqlite3_reset(stmt);
}
sqlite3_finalize(stmt);
sqlite3_exec(db, "COMMIT TRANSACTION;", 0, 0, 0);

Regards,
Eugene Wee

-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------



       
---------------------------------
Ready for the edge of your seat? Check out tonight's top picks on Yahoo! TV.
Reply | Threaded
Open this post in threaded view
|

Re: sqlite as server queries

Edwin Eyan Moragas
In reply to this post by Gilles Ganault
On 8/7/07, Gilles Ganault <[hidden email]> wrote:
> At 14:08 06/08/2007 +0100, "Edwin Eyan Moragas" wrote:
> >2) anybody ever implemented something like a single process of sqlite
> >doing queries for a lot of networked clients?
>
> Am working on this, in Classic VB5: Winsock control on the server, Inet
> control on the client, the client thinks it's talking to a web server.
>
> http://codecomplete.free.fr/sqlite/sample/
>

G,

thanks a lot

./e

--
no sig

-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

12