SQLite Forum

Iterating through SQLite tables' rows
Login

Iterating through SQLite tables' rows

(1) By PongoZ on 2020-04-01 10:28:00 [link] [source]

I need to iterate through all fields present in a column named "progressiveID" inside "myTable" and UPDATE each value according to some criteria (only values which are > x). 

myTable
column name: progressiveID

Note:
I know how to update an individual entry: 
    $db->exec ("UPDATE myTable SET progressiveID = $vId_new");

Any suggestion would be appreciated!

(2) By Stephan Beal (stephan) on 2020-04-01 10:54:34 in reply to 1 [link] [source]

$db->exec ("UPDATE myTable SET progressiveID = $vId_new");

Ignoring that you should (almost) never expand variables in SQL strings that way (google for "SQL injection" for why), and instead should always use the "binding" API appropriate for your programming environment (which you didn't specify), what you're trying to do requires only a tiny change:

$db->exec ("UPDATE myTable SET progressiveID = $vId_new WHERE progressiveID > $x");

(3) By PongoZ on 2020-04-01 10:58:48 in reply to 2 [link] [source]

Thanks for the answer and the suggestion. The command will be launched from a website, therefore HTML, calling a PHP function to do the task. What binding API should I use?

(4) By Stephan Beal (stephan) on 2020-04-01 11:06:39 in reply to 3 [link] [source]

The command will be launched from a website, therefore HTML, calling a PHP function to do the task.

Which is the case where it critical to NEVER expand variables inline in SQL code. That's how SQL injections happen.

What binding API should I use?

PHP has multiple sqlite APIs, none of which i've used in a decade or so, so i'll leave that one to someone more up to date on that topic.

(5) By Warren Young (wyoung) on 2020-04-01 11:55:20 in reply to 2 [source]

you should (almost) never expand variables in SQL strings that way

The only defensible exception I've ever come up with is with numeric data types in statically-typed languages, where you thus inherently know the scope of possible generated characters cannot lead to a SQL injection flaw.

However, this looks like Perl, so even if you think the ID variable contains an integer, you could be wrong, thus turning possibly innocent code into an attack vector.

If I'm right and this is Perl DBI code, then the solution is easy.

(6) By PongoZ on 2020-04-02 21:28:56 in reply to 4 [link] [source]

I understand. Thank you for the warning! How could I then achieve the same result without risking being a target of SQL injections?

(7) By Warren Young (wyoung) on 2020-04-02 21:41:20 in reply to 6 [link] [source]