Re: [sqlite] Can a sys admin control temporary file location without changing the source code?

[Tom Browder]

On Mon, Jul 11, 2011 at 01:18, H. Phil Duby  wrote:
> On Sat, Jul 9, 2011 at 23:22, Stephan Beal  wrote:
>
>> On Sun, Jul 10, 2011 at 4:25 AM, Tom Browder 
>> wrote:
>>
>> > sqlite db files and making sure that directory is writable by my web
>> > server.  I make sure that the directory is not used for anything else
>> > in order to help secure my web site.
>> >
>>
>> Another tip for such uses:
>>
>> .htaccess:
>>
>>   (or however your db is named)
>>        Order allow,deny
>>        Deny from all
>> 
>>
>> so that people who know the db is there can't fetch it over http.
>>
>
> You should be able to put the folder for the database file(s) outside of the
> path available to access by url.  'above' or 'beside' the home / root
> folder.  That way there is no way for someone to access it directly through
> a browser.  Only code running on the server can access it.

And that is exactly what I did.

Best regards,

-Tom

P.S.  So far sqlite is performing admirably on my site--so much easier
for certain uses.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can a sys admin control temporary file location without changing the source code?

[H. Phil Duby]

On Sat, Jul 9, 2011 at 23:22, Stephan Beal  wrote:

> On Sun, Jul 10, 2011 at 4:25 AM, Tom Browder 
> wrote:
>
> > sqlite db files and making sure that directory is writable by my web
> > server.  I make sure that the directory is not used for anything else
> > in order to help secure my web site.
> >
>
> Another tip for such uses:
>
> .htaccess:
>
>   (or however your db is named)
>Order allow,deny
>Deny from all
> 
>
> so that people who know the db is there can't fetch it over http.
>

You should be able to put the folder for the database file(s) outside of the
path available to access by url.  'above' or 'beside' the home / root
folder.  That way there is no way for someone to access it directly through
a browser.  Only code running on the server can access it.

>
> --
> - stephan beal
> http://wanderinghorse.net/home/stephan/
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can a sys admin control temporary file location without changing the source code?

[Stephan Beal]

On Sun, Jul 10, 2011 at 4:25 AM, Tom Browder  wrote:

> sqlite db files and making sure that directory is writable by my web
> server.  I make sure that the directory is not used for anything else
> in order to help secure my web site.
>

Another tip for such uses:

.htaccess:

  (or however your db is named)
Order allow,deny
Deny from all


so that people who know the db is there can't fetch it over http.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can a sys admin control temporary file location without changing the source code?

[Tom Browder]

On Thu, Jun 30, 2011 at 15:02, Stephan Beal  wrote:
> On Thu, Jun 30, 2011 at 9:52 PM, Tom Browder  wrote:
>
>> But I think the journal file is the problem...as I understand it, it
>> has to be on disk, doesn't it?

The answer seems to be yes.

I have solved my problem by defining an explicit directory for my
sqlite db files and making sure that directory is writable by my web
server.  I make sure that the directory is not used for anything else
in order to help secure my web site.

Best regards,

-Tom
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can a sys admin control temporary file location without changing the source code?

[Stephan Beal]

On Thu, Jun 30, 2011 at 9:52 PM, Tom Browder  wrote:

> But I think the journal file is the problem...as I understand it, it
> has to be on disk, doesn't it?
>

Now that you mention it, that might be:

http://www.sqlite.org/compile.html

i interpretted SQLITE_TEMP_STORE=3 as using memory for the journal, but i
was assuming the journal is considered a temp file.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can a sys admin control temporary file location without changing the source code?

[Tom Browder]

On Thu, Jun 30, 2011 at 14:44, Stephan Beal  wrote:
> On Thu, Jun 30, 2011 at 9:06 PM, Tom Browder  wrote:
...
>> Thanks, Richard (and Simon), I think I can solve my web access problem
>> by giving the db file its own directory.
...
> You can also try disabling the temp files (telling it to use memory
> instead).

But I think the journal file is the problem...as I understand it, it
has to be on disk, doesn't it?

-Tom

Thomas M. Browder, Jr.
Niceville, Florida
USA



>
> --
> - stephan beal
> http://wanderinghorse.net/home/stephan/
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can a sys admin control temporary file location without changing the source code?

[Stephan Beal]

On Thu, Jun 30, 2011 at 9:06 PM, Tom Browder  wrote:

> Thanks, Richard (and Simon), I think I can solve my web access problem
> by giving the db file its own directory.
>

You can also try disabling the temp files (telling it to use memory
instead).

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can a sys admin control temporary file location without changing the source code?

[Tom Browder]

On Thu, Jun 30, 2011 at 13:42, Richard Hipp  wrote:
> On Thu, Jun 30, 2011 at 2:30 PM, Tom Browder  wrote:
...
>>  From my limited testing on my web server [temporary files] are
>> written in the same directory as the db file, but I would like them to
>> be written to a separate directory.
...
> See http://www.sqlite.org/tempfiles.html for a list of the various kinds of
> temporary files used by SQLite.  Some of them (ex: the rollback journal)
> must be in the same directory as the database file in order to ensure
> recovery after a crash.

Thanks, Richard (and Simon), I think I can solve my web access problem
by giving the db file its own directory.

Best regards,

-Tom

P.S.  Sqlite seems to be handling my modest multiple-virtual-host
Apache2 web server just fine--as expected.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can a sys admin control temporary file location without changing the source code?

[Simon Slavin]

On 30 Jun 2011, at 7:30pm, Tom Browder wrote:

> I've not looked at the code yet, but is there any way, without
> changing the code and recompiling, to control where the temporary
> files are created?  From my limited testing on my web server they are
> written in the same directory as the db file, but I would like them to
> be written to a separate directory.

This won't work.  All SQLite applications have to know where to find those 
files.  Because if your app crashes, any SQLite application which opens the 
database file must know where to find the journals to restore this file to an 
uncorrupted state.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can a sys admin control temporary file location without changing the source code?

[Richard Hipp]

On Thu, Jun 30, 2011 at 2:30 PM, Tom Browder  wrote:

>  From my limited testing on my web server [temporary files] are
> written in the same directory as the db file, but I would like them to
> be written to a separate directory.
>

See http://www.sqlite.org/tempfiles.html for a list of the various kinds of
temporary files used by SQLite.  Some of them (ex: the rollback journal)
must be in the same directory as the database file in order to ensure
recovery after a crash.

On unix, SQLite consults the TMPDIR environment variable to get the
directory for temporary files that are not in the same directory as the
database.  There are additional fallbacks if TMPDIR is not set.  On windows,
the GetTempPathW() interface is used to get the directory name.  Either
technique allows you, the sysop, to adjust the temporary directory location
without recompiling.


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Can a sys admin control temporary file location without changing the source code?

[Tom Browder]

I've seen various threads in the users' list archives about the
subject of temporary file location with no definitive answer, and I've
seen the use of the pragma temp_store_directory is deprecated so that
solution is out.

I've not looked at the code yet, but is there any way, without
changing the code and recompiling, to control where the temporary
files are created?  From my limited testing on my web server they are
written in the same directory as the db file, but I would like them to
be written to a separate directory.

I have experimented with setting the TMP and TMPDIR variables but
haven't seen that work.

Is there any good solution without code changing for a sys admin?

Thanks and best regards,

-Tom

Thomas M. Browder, Jr.
Niceville, Florida
USA
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users