Re: [sqlite] Coding standard

[Arthur Blondel]

I'm using amalgamation version 3.30.1. and check with CodeSonar
Attached the Security issues.


On Wed, Dec 11, 2019 at 4:57 PM Richard Hipp  wrote:

> On 12/11/19, Arthur Blondel  wrote:
> > Hello,
> > Running the CERT coding standard
> > <https://en.wikipedia.org/wiki/CERT_C_Coding_Standard> on the sqlite
> code I
> > get up to 32000 warnings, most of them are security issues.
>
> These are all likely to be false-positives.  But if you will send me
> the list, I will have a look.
>
>
> > Does anyone had this problem? The CERT standard is a must for my project
> > and I wonder if there is a solution.
> > Thanks
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Coding standard

[Arthur Blondel]

Hello,
Running the CERT coding standard
 on the sqlite code I
get up to 32000 warnings, most of them are security issues.
Does anyone had this problem? The CERT standard is a must for my project
and I wonder if there is a solution.
Thanks
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Remove row to insert new one on a full database

[Arthur Blondel]

I have enough disk space. I just limit the database file size artificially
for testing purpose as you can see.
There is no problem of privilege and there is nothing else than the code I
sent. No other access to the DB.
I'm using sqlite 3.16.2

On Fri, Apr 5, 2019 at 3:59 PM Chris Locke  wrote:

> Arthur - are you running SQLite in parallel runs?
> If you access the database file using the sqlite3 command-line tool, and
> try to execute the same SQL commands, do you get the same error ?
>
> SQLite makes a temporary 'journal' file while it's working.  I think that,
> on your platform, by default it will be in the same directory as the
> database file.  Does your application have enough privileges to create new
> files in that directory ?
> What version of SQLite are you using?  It might be an old version.
> Also, removing rows doesn't necessarily remove space in the database file.
> If you're running out of disk space, it could be you need to vacuum your
> database file.  How large is the database?  How much disk space do you have
> left?
>
>
> Thanks,
> Chris
>
> On Fri, Apr 5, 2019 at 1:46 PM Arthur Blondel 
> wrote:
>
> > OK, I wasn't clear.
> > I'm limited in space so when the DB is full (when sqlite3_exec() returns
> > SQLITE_FULL when I try to insert a new row), I remove the oldest row and
> > retry to insert the new one.
> > The data is always the same. That's why removing one row should be enough
> > to insert a new one.
> > My problem is that some times I need to remove many rows to add one new
> > one.
> > This is basically my code:
> >
> > main()
> > {
> > sqlite3* db;
> > int rc;
> > char *err_msg = 0;
> > int counter;
> > bool full = false;
> > int id;
> >
> > /* --- Create DB --- */
> > rc = sqlite3_open("db_file.db", );
> > printf("1. rc = %d\n", rc);
> >
> > rc = sqlite3_exec(db,
> >  "CREATE TABLE IF NOT EXISTS data_table"
> >  "(id INTEGER PRIMARY KEY AUTOINCREMENT, col1 INTEGER,
> col2
> > INTEGER, col3 INTEGER)",
> >  0, 0, _msg);
> > printf("2. rc = %d\n", rc);
> >
> > /* --- Limit database size to 50 K --- */
> > rc = sqlite3_exec(db, "PRAGMA page_size=1024", 0, 0, _msg);
> > printf("3. rc = %d\n", rc);
> > rc = sqlite3_exec(db, "PRAGMA max_page_count=50", 0, 0, _msg);
> > printf("4. rc = %d\n", rc);
> > rc = sqlite3_exec(db, "VACUUM", 0, 0, _msg);  // resize file
> > printf("5. rc = %d\n", rc);
> >
> >
> > /* --- Fill DB --- */
> > for (int i = 0 ; i < 5000 ; i++) {
> > counter = 0;
> > do {
> > rc = sqlite3_exec(db,
> >   "INSERT INTO data_table"
> >   "(col1, col2, col3) VALUES(1, 2, 3)",
> >   0, 0, _msg);
> >
> > if (rc == SQLITE_FULL) {
> > if (!full) {
> > printf("%d - DB full\n", id);
> > full = true;
> > }
> > counter++;
> > // delete oldest row
> > int stat = sqlite3_exec(db,
> >  "DELETE FROM data_table WHERE id IN "
> >  "(SELECT id FROM data_table ORDER BY id
> LIMIT
> > 1)",
> >  0, 0, _msg);
> > if (stat != SQLITE_OK) {
> > printf("Delete error %d\n", stat);
> > }
> > } else if (rc == SQLITE_OK) {
> > id = sqlite3_last_insert_rowid(db);
> > } else /*if (rc != SQLITE_OK)*/ {
> > printf("Insert error %d\n", rc);
> > }
> > } while (rc == SQLITE_FULL);
> >
> > if (counter > 2) {
> > printf("%d - %d rows was removed\n", id, counter);
> > }
> > }
> >
> > printf("close -> %d\n", sqlite3_close(db));
> > }
> >
> >
> > Following the output:
> >
> > 1. rc = 0
> > 2. rc = 0
> > 3. rc = 0
> > 4. rc = 0
> > 5. rc = 0
> > 3959 - DB full
> > 3960 - 109 rows was removed
> > 4044 - 92 rows was removed
> > 4128 - 86 rows was removed
> > 4212 - 85 rows was removed
&g

Re: [sqlite] Remove row to insert new one on a full database

[Arthur Blondel]

OK, I wasn't clear.
I'm limited in space so when the DB is full (when sqlite3_exec() returns
SQLITE_FULL when I try to insert a new row), I remove the oldest row and
retry to insert the new one.
The data is always the same. That's why removing one row should be enough
to insert a new one.
My problem is that some times I need to remove many rows to add one new one.
This is basically my code:

main()
{
sqlite3* db;
int rc;
char *err_msg = 0;
int counter;
bool full = false;
int id;

/* --- Create DB --- */
rc = sqlite3_open("db_file.db", );
printf("1. rc = %d\n", rc);

rc = sqlite3_exec(db,
 "CREATE TABLE IF NOT EXISTS data_table"
 "(id INTEGER PRIMARY KEY AUTOINCREMENT, col1 INTEGER, col2
INTEGER, col3 INTEGER)",
 0, 0, _msg);
printf("2. rc = %d\n", rc);

/* --- Limit database size to 50 K --- */
rc = sqlite3_exec(db, "PRAGMA page_size=1024", 0, 0, _msg);
printf("3. rc = %d\n", rc);
rc = sqlite3_exec(db, "PRAGMA max_page_count=50", 0, 0, _msg);
printf("4. rc = %d\n", rc);
rc = sqlite3_exec(db, "VACUUM", 0, 0, _msg);  // resize file
printf("5. rc = %d\n", rc);


/* --- Fill DB --- */
for (int i = 0 ; i < 5000 ; i++) {
counter = 0;
do {
rc = sqlite3_exec(db,
  "INSERT INTO data_table"
  "(col1, col2, col3) VALUES(1, 2, 3)",
  0, 0, _msg);

if (rc == SQLITE_FULL) {
if (!full) {
printf("%d - DB full\n", id);
full = true;
}
counter++;
// delete oldest row
int stat = sqlite3_exec(db,
 "DELETE FROM data_table WHERE id IN "
 "(SELECT id FROM data_table ORDER BY id LIMIT
1)",
 0, 0, _msg);
if (stat != SQLITE_OK) {
printf("Delete error %d\n", stat);
}
} else if (rc == SQLITE_OK) {
id = sqlite3_last_insert_rowid(db);
} else /*if (rc != SQLITE_OK)*/ {
printf("Insert error %d\n", rc);
}
} while (rc == SQLITE_FULL);

if (counter > 2) {
printf("%d - %d rows was removed\n", id, counter);
}
}

printf("close -> %d\n", sqlite3_close(db));
}


Following the output:

1. rc = 0
2. rc = 0
3. rc = 0
4. rc = 0
5. rc = 0
3959 - DB full
3960 - 109 rows was removed
4044 - 92 rows was removed
4128 - 86 rows was removed
4212 - 85 rows was removed
4296 - 85 rows was removed
4380 - 84 rows was removed
4464 - 84 rows was removed
4548 - 84 rows was removed
4632 - 84 rows was removed
4716 - 84 rows was removed
4800 - 84 rows was removed
4884 - 84 rows was removed
4968 - 84 rows was removed
close -> 0

Thanks


On Thu, Apr 4, 2019 at 9:07 AM Arthur Blondel 
wrote:

>
> Hello
>
> When I try to insert new data to a full SQLite database, I need to remove
> much more than really needed. I'm doing the following:
>
> while(1) {
> do {
> status = insert_1_row_to_db();
> if (status == full) {
> remove_one_row_from_db();
> }
> } while (status == full);}
>
> The inserted data has always the same size. When the database is full,
> removing only one row is enough to insert the new one. But after a while, I
> need to remove 30, 40 and even more the 100 rows to be able to insert one
> new row. Is it the correct behavior of SQLite? Is there a way to remove
> only what is needed and no more? Thanks
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Remove row to insert new one on a full database

[Arthur Blondel]

Hello

When I try to insert new data to a full SQLite database, I need to remove
much more than really needed. I'm doing the following:

while(1) {
do {
status = insert_1_row_to_db();
if (status == full) {
remove_one_row_from_db();
}
} while (status == full);}

The inserted data has always the same size. When the database is full,
removing only one row is enough to insert the new one. But after a while, I
need to remove 30, 40 and even more the 100 rows to be able to insert one
new row. Is it the correct behavior of SQLite? Is there a way to remove
only what is needed and no more? Thanks
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users