[sqlite] temp directory?
Artur Reilin
My host have sqlite support vor sqlite 2.8.17 and i can use it. But i have
to give chMod 0777 to the directory where i use sqlite. Not only on the
directory, that contains the sqlite database. let me show it:
/index.php (uses sqlite database)
/data/data.sqlite (sqlite database)
If i give chMod 0777 to /data/ and the files in this folder, it wouldn't
work and get me an "malformed database - cannot create temp tables".
I need to gibe chMod to the directory which contains the index.php file.
That's a big security risk and i don't want to give chMod to the main
directory.
Is this normal and can i change this?
with best wishes
Artur Reilin
sqlite.yuedream.de
_______________________________________________
sqlite-users mailing list
sqlite...@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Simon Slavin
On 19 Nov 2009, at 8:05am, Artur Reilin wrote:
> My host have sqlite support vor sqlite 2.8.17 and i can use it. But i have
> to give chMod 0777 to the directory where i use sqlite. Not only on the
> directory, that contains the sqlite database. let me show it:
>
> /index.php (uses sqlite database)
> /data/data.sqlite (sqlite database)
>
> If i give chMod 0777 to /data/ and the files in this folder, it wouldn't
> work and get me an "malformed database - cannot create temp tables".
>
> I need to gibe chMod to the directory which contains the index.php file.
> That's a big security risk and i don't want to give chMod to the main
> directory.
>
> Is this normal and can i change this?
You do not actually need 0777. Your problem is that the user which is creating and using the database is the user that runs Apache, not yourself. So find out which user Apache runs under. This might be perhaps www or _www or _apache. Make sure that that user has the rights over this directory. Then Apache (running the .php script) which have enough access to use the database file.
Simon.
Artur Reilin
>
> On 19 Nov 2009, at 8:05am, Artur Reilin wrote:
>
>> My host have sqlite support vor sqlite 2.8.17 and i can use it. But i
>> have
>> to give chMod 0777 to the directory where i use sqlite. Not only on the
>> directory, that contains the sqlite database. let me show it:
>>
>> /index.php (uses sqlite database)
>> /data/data.sqlite (sqlite database)
>>
>> If i give chMod 0777 to /data/ and the files in this folder, it wouldn't
>> work and get me an "malformed database - cannot create temp tables".
>>
>> I need to gibe chMod to the directory which contains the index.php file.
>> That's a big security risk and i don't want to give chMod to the main
>> directory.
>>
>> Is this normal and can i change this?
>
> You do not actually need 0777. Your problem is that the user which is
> creating and using the database is the user that runs Apache, not
> yourself. So find out which user Apache runs under. This might be
> perhaps www or _www or _apache. Make sure that that user has the rights
> over this directory. Then Apache (running the .php script) which have
> enough access to use the database file.
>
> Simon.
The user which runs php (or has the highest rights) is called nobody. The
support said me, that i need to set the directory to this user, but it
also don't work. I get the same error as before. I can try it again, but
the last time it didn't work.
I also wondering why my oop version of my script didn't work, but the
prozedural one does. They have pdo drivers like mine xampp server, but
they don't have the sqlite3 drivers. Perhaps this also happens because of
the malformed database error...
With best wishes
Artur Reilin
sqlite.yuedream.de
Simon Slavin
On 19 Nov 2009, at 10:05am, Artur Reilin wrote:
> The user which runs php (or has the highest rights) is called nobody.
Okay. That suggests something strange is happening. Perhaps instead of making sure that the right 'user' has access to the database directory you can use the 'group' that 'nobody' is in, and give that group privilages.
> The
> support said me, that i need to set the directory to this user, but it
> also don't work. I get the same error as before. I can try it again, but
> the last time it didn't work.
I do not know much more about this. If you need someone else to help you could you list your operating system, and whether you really are doing this in a .php script run by a web server ?
> I also wondering why my oop version of my script didn't work, but the
> prozedural one does. They have pdo drivers like mine xampp server, but
> they don't have the sqlite3 drivers.
Which set of PHP commands are you using to access the SQLite database ?
> Perhaps this also happens because of
> the malformed database error...
Can you create a new blank database which is not malformed ?
Simon.
Artur Reilin
> making sure that the right 'user' has access to the database directory you
> can use the 'group' that 'nobody' is in, and give that group privilages.
I changed it to the group 'nobody', but no i have an empty result. I don't
get an error, but it doesn't show anything. I need to set chMod to 777 and
user and group 'nobody'. strange thing...
> Which set of PHP commands are you using to access the SQLite database ?
to access the database i use new SQLITEDATABASE and the query. I only read
the informations from the database.
> Can you create a new blank database which is not malformed ?
The database isn't malformed and it work right, but it seems not to work
if i use oop.
> Simon.
with besth wishes
Artur Reilin
sqlite.yuedream.de
Kris Groves
created. This is done with pragma temp_store_directory. I think you
can also set this in compilation.
Artur Reilin
Kris Groves
created in memory. I say most, because even though you set temp_store
to memory, there is still one temporary file(statement journal) which
will still need to use the temp_store_directory.
I suppose another possibility is to set the temp environment variable.
I have just noticed that your version of sqlite is way old... so I'm
very unsure if anything I've suggested is of any use. I know the
environment variable did not work in 3.5.9, but does in 3.6 and up.
Artur Reilin
My hoster:
pdo_sqlitePDO Driver for SQLite 3.x enabled
PECL Module version 1.0.1 $Id: pdo_sqlite.c,v 1.10.2.6.2.4 2008/12/31
11:17:42 sebastian Exp $
SQLite Library 3.3.8
SQLiteSQLite support enabled
PECL Module version 2.0-dev $Id: sqlite.c,v 1.166.2.13.2.12 2008/12/31
11:17:44 sebastian Exp $
SQLite Library 2.8.17
SQLite Encoding UTF-8
My home server:
pdo_sqlitePDO Driver for SQLite 3.x enabled
SQLite Library 3.6.20
SQLiteSQLite support enabled
PECL Module version 2.0-dev $Id: sqlite.c,v 1.166.2.13.2.9.2.22
2009/06/25 09:38:04 johannes Exp $
SQLite Library 2.8.17
SQLite Encoding iso8859
sqlite3SQLite3 support enabled
SQLite3 module version 0.7-dev
SQLite Library 3.6.16
---------------
Seems that there are missing the sqlite3 library, but i use 2.8.17,
because the sqlite 3 version don't work on both systems.
Okay, should i ask my hoster if they there is an way, to set the default
path to tmp dir from the server? This should normaly so, or?
with best wishes
Artur
---------------
Kris Groves
server, however, as I said in the previous post, I am not sure if it
will work.
Probably the safest and most robust way would be to have your code call
the pragma temp_store_directory directly after opening the database.
How your code knows what directory to call the pragma with could be done
with an environment variable or whatever you prefer.
Artur Reilin
> You can try setting a temp or tmp or tempdir environment variable on the
> server, however, as I said in the previous post, I am not sure if it
> will work.
>
> Probably the safest and most robust way would be to have your code call
> the pragma temp_store_directory directly after opening the database.
> How your code knows what directory to call the pragma with could be done
> with an environment variable or whatever you prefer.
>
$db = @sqlite_open('../www/zero.sqlite');
sqlite_exec('pragma temp_store=1; pragma
temp_store_directory="'.$_SERVER['TMP'].'"',$db);
like this?
Kris Groves
would say that seems correct as long as "'.$_SERVER['TMP'].'" resolves
to a directory in which the user under which the application is running,
has read and write access.
Artur Reilin
but my hoster doesn't has this. Only "open_basedir" has the option for the
"temp directory". But i don't know, if this would work. At least i didn't
know if my code with the pragma's work. Because i cannot see it or is
there an option, that i can see if my code works? Explain Operator?
with best wishes
Artur
Artur Reilin
sqlite.yuedream.de
Robert Citek
sqlite.org site and haven't found any mention of an environment
variable. What environment variable can I set to change the default
value for the temporary directory?
Regards,
- Robert
On Fri, Nov 20, 2009 at 5:04 AM, Kris Groves <kris....@mmlab.de> wrote:
> I suppose another possibility is to set the temp environment variable.
>
> I have just noticed that your version of sqlite is way old... so I'm
> very unsure if anything I've suggested is of any use. I know the
> environment variable did not work in 3.5.9, but does in 3.6 and up.
Jay A. Kreibich
> You mention a temp environment variable. I've googled through the
> sqlite.org site and haven't found any mention of an environment
> variable. What environment variable can I set to change the default
> value for the temporary directory?
http://sqlite.org/pragma.html#pragma_temp_store_directory
It is a PRAGMA, not an env var. Also see "PRAGMA temp_store".
-j
--
Jay A. Kreibich < J A Y @ K R E I B I.C H >
"Our opponent is an alien starship packed with atomic bombs. We have
a protractor." "I'll go home and see if I can scrounge up a ruler
and a piece of string." --from Anathem by Neal Stephenson
Artur Reilin
of apache. But it doesn't work. But that's not the thing of sqlite3, then
the thing that I use sqlite 2.8.17 databases and there the variable
doesn't work.
The Pragma temp_store and temp_store_directory works only with sqlite3.
- Artur -
-----------------
Am 07.01.2010, 16:42 Uhr, schrieb Jay A. Kreibich <j...@kreibi.ch>:
> On Thu, Jan 07, 2010 at 10:35:21AM -0500, Robert Citek scratched on the
> wall:
>> You mention a temp environment variable. I've googled through the
>> sqlite.org site and haven't found any mention of an environment
>> variable. What environment variable can I set to change the default
>> value for the temporary directory?
>
> http://sqlite.org/pragma.html#pragma_temp_store_directory
>
> It is a PRAGMA, not an env var. Also see "PRAGMA temp_store".
>
> -j
>
--
Robert Citek
> On Thu, Jan 07, 2010 at 10:35:21AM -0500, Robert Citek scratched on the wall:
>> You mention a temp environment variable. I've googled through the
>> sqlite.org site and haven't found any mention of an environment
>> variable. What environment variable can I set to change the default
>> value for the temporary directory?
>
> http://sqlite.org/pragma.html#pragma_temp_store_directory
>
> It is a PRAGMA, not an env var. Also see "PRAGMA temp_store".
Yes, I was aware of the pragma. I was hoping for an environment
variable so that I don't have to write pragmas in my code.
Regards,
- Robert
Kris Groves
the way I thought they should I came across a function in the sqlite
code called getTempname. In certain situations(i.e. the
temp_store_directory is not set), this function will get a tempname
based on an environment variable.
It seems as though there are different implementations of getTempname
depending on OS.
in one implenetation we have the following :
if( sqlite3_temp_directory ){
zTempPath = sqlite3_temp_directory;
}else{
if( DosScanEnv( (PSZ)"TEMP", &zTempPath ) ){
if( DosScanEnv( (PSZ)"TMP", &zTempPath ) ){
if( DosScanEnv( (PSZ)"TMPDIR", &zTempPath ) ){
in the other we have :
azDirs[0] = sqlite3_temp_directory;
if (NULL == azDirs[1]) {
azDirs[1] = getenv("TMPDIR");
}
So it seems as if TMPDIR will work in two different OSes.
Robert Citek
> So it seems as if TMPDIR will work in two different OSes.
What would be an easy test to verify if setting TMPDIR works or not?
I did this, which shows that TMPDIR is indeed being used, but I think
this test is a bit cumbersome:
$ sqlite3 /dev/null
SQLite version 3.6.10
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create temp table foo (bar int) ;
sqlite>
[1]+ Stopped sqlite3 /dev/null
$ ls -l /proc/$(jobs -p %1)/fd
total 0
lrwx------ 1 rwcitek rwcitek 64 2010-01-07 13:29 0 -> /dev/pts/0
lrwx------ 1 rwcitek rwcitek 64 2010-01-07 13:29 1 -> /dev/pts/0
lrwx------ 1 rwcitek rwcitek 64 2010-01-07 13:29 2 -> /dev/pts/0
lrwx------ 1 rwcitek rwcitek 64 2010-01-07 13:29 3 -> /dev/null
lrwx------ 1 rwcitek rwcitek 64 2010-01-07 13:29 4 ->
/var/tmp/etilqs_4GCNtDifceoskIh (deleted)
lrwx------ 1 rwcitek rwcitek 64 2010-01-07 13:29 5 ->
/var/tmp/etilqs_dy4Trta7FQrYQT8 (deleted)
$ fg
sqlite3 /dev/null
.q
$ TMPDIR=/dev/shm/ sqlite3 /dev/null
SQLite version 3.6.10
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create temp table foo (bar int) ;
sqlite>
[1]+ Stopped TMPDIR=/dev/shm/ sqlite3 /dev/null
$ ls -l /proc/$(jobs -p %1)/fd
total 0
lrwx------ 1 rwcitek rwcitek 64 2010-01-07 13:33 0 -> /dev/pts/0
lrwx------ 1 rwcitek rwcitek 64 2010-01-07 13:33 1 -> /dev/pts/0
lrwx------ 1 rwcitek rwcitek 64 2010-01-07 13:33 2 -> /dev/pts/0
lrwx------ 1 rwcitek rwcitek 64 2010-01-07 13:33 3 -> /dev/null
lrwx------ 1 rwcitek rwcitek 64 2010-01-07 13:33 4 ->
/dev/shm/etilqs_MOz4R9xoAMB1i6U (deleted)
lrwx------ 1 rwcitek rwcitek 64 2010-01-07 13:33 5 ->
/dev/shm/etilqs_IXXkIEReSwGcwL5 (deleted)
$ fg
TMPDIR=/dev/shm/ sqlite3 /dev/null
.q
Kris Groves
using pragma temp_store_directory; returns nothing if it is not
explicitly set. In this case you know that sqlite will then "search for
best option... It would be great if the pragma returned what it is
going to use even if it wasn't explicitly set.
I know that for example, in linux, if temp_store_directory is not set,
then it checks for the common temp environment variables, failing that,
it then tries some standard hard coded directories ('/tmp','/var/tmp'
...) If all that turns up nothing then it finally settles on the
current directory.
You run into problems when the directory it uses is not writable for the
user the process is running under.
FYI, in a previous posting, I discovered that even if you set temp_store
to memory only, you still need a temp_store_directory that is writable.
I can't recall specifically, but there is one temp file that is always
on disk regardless of settings.
Kinda makes the whole memory only temp_store useless.
Dan Kennedy
On Jan 8, 2010, at 2:45 AM, Kris Groves wrote:
> FYI, in a previous posting, I discovered that even if you set
> temp_store
> to memory only, you still need a temp_store_directory that is
> writable.
> I can't recall specifically, but there is one temp file that is
> always
> on disk regardless of settings.
> Kinda makes the whole memory only temp_store useless.
Hopefully this was fixed for 3.6.14:
http://www.sqlite.org/src/info/38f8c5a14cd221af9e115a0fea689f2ff39e30e5
If you test it and find that the problem still exists, please send
a "BUG:" message to the list. Thanks.
Dan.