Gianluigi said

Fortunately, Minisini has provided us with the DB class, which, behind the scenes, transparently makes our queries secure and correct on any database.
Professionals like @gb.Willy can easily do without it because they know how to use queries correctly.
If the DB class doesn't always produce errors (?) when its syntax isn't respected, I think it's due to the risk of inoculation, but I'm not sure because I don't know how it works.

From “Post #12,877”, November 9th 2025, 4:53 AM

I simply never used db.subst because of following:
1. I use MariaDB only, so some of my  queries are very database specific and sometimes quite complex (with inner/outer joins etc).
db.subst is meant to build database-independent requests. So, your query will run on no matter what backend database. I have no need for that
2. The sql injection is something that happens when for example there is user input, like a input by user of data used for a filter, that is handled by a query.

How do SQL injections work:
Instead of adding name in the filter something else is entered.
For example let's take a field name tbxUser and a query that will be run to show stuff for that user.
The query could be:

Code

squery = Subst(SELECT * FROM Users WHERE Name = &1, tbxUser.Text)

A malicious user could enter " OR ""=" in tbxUser
The resulting query would look like:

Code

SELECT * FROM Users WHERE Name = "" OR ""=""

You see the injection and the unwanted result. It will return all users as the OR "" ="" is ALWAYS true!!

I don't have any of these possibilities in any of my applications. I use comboboxes with pre filled values for example as filters instead of letting user type in anything that might become part of the query.
So, yes, when you know what you are doing you can use regular gb.subst, but when not aware of how stuff get's hacked better used gb.db.subst.
As long as a user can't enter any text that becomes part of any query, you can avoid these sql injections, or in other works, you can design your application to be safer.

So, now you know why I stiil use gb.subst instead of gb.db.susbt.

gb.db.subst does a lot more than regular gb.subst as it:
- build database-independent requests.
- avoids sql injection
Those two alone make up for some extra code in the gb.db.subst.
So, when you don't know what you are doing better use gb.db.subst, like Benoit advised.

I hope this help people to understand the sql injection trouble that can occur.
 

 I have managed to get some comands working … But not "Update"
mo mater what I try I get some sort of error mostly syntax near "," or near "where" .

I must be doing something wrong

Try: UPDATE "bkaccts" SET 'accname' = 'newName' WHERE "accname" = 'newAcc95'

SYNTAX IS: 'accname'='[value]' NOT 'accname=[value]' to my knowledge.

gbWilly said

Try: UPDATE "bkaccts" SET 'accname' = 'newName' WHERE "accname" = 'newAcc95'

SYNTAX IS: 'accname'='[value]' NOT 'accname=[value]' to my knowledge.

From “Post #12,880”, November 9th 2025, 10:24 AM

Exactly.

Sorry, first of all, you should use the test I provided, so we know what you're talking about.
Secondly, "SET &4"? &4 must be in quotes, must be a column header, and must equal &5, which must be the new value of that tuple ("SET '&4' = &5").
I've attached some examples, why don't you study them?

SYNTAX IS: 'accname'='[value]' NOT 'accname=[value]' to my knowledge.

 That works …. but on a 8 column record update requires  8  &3 = &4,  and 19   variables to substitute.

I was trying to shorten it by combining  column and the value together … that does not seem to work .

can a "for" or "while" lope be put in the update command …. ?

Or should I be looking a using the "edit" instead of the "update" ?

Thanks for all the help in my learning  
 

GrayGhost said

Or should I be looking a using the "edit" instead of the "update" ?
 

From “Post #12,884”, November 9th 2025, 11:40 AM

Take the example I posted, expand the table until you have the columns you need for the test; you can even insert a single tuple.
I think Edit is simpler, and you can pass a collection or a variant array to the function (although I can feel the shivers running down the spines of experts    ), but I'd love to hear what gbWilly thinks about this.

 

Edit is easier to loop, but it can be done using UPDATE query as well.
The latter requires a more clever approach to get it done.

I am attempting to learn and use EDIT

but am getting an error trying to use a variable with it.  is a variable allowed  ?

As I understand it, you are trying to update multiple fields in 1 record. Am I correct?
If so, what are the names of those fields and where are the values that need to go into these fields, are they stored in the saInfo?
I presume saInfo[0] holds the value for first field to be replaced, saInfo[1] for the second field and so on.

Code (gambas)

1. RS = $Con.Edit(sFilename, sBkAccColumn = &1, sFileIndex)
2.  
3. RS!Field1 = saInfo[0]
4. RS!Field2 = saInfo[1]
5. RS!Field3 = saInfo[2]
6. ...
7.  
8. RS.Update
9.  

Replace Field1, Field2 etc by their respective names.


 

yes you are correct … BUT I want to use a variable for the Field names ….. sColumName = "Field1"

then use

RS!sColumName = saInfo[1]

GrayGhost said

yes you are correct … BUT I want to use a variable for the Field names ….. sColumName = "Field1"

then use

RS!sColumName = saInfo[1]

From “Post #12,893”, November 9th 2025, 7:25 PM

I've already shown you how to do it right away.
As the Latins used to say, "repetita iuvant". See yet another example attached.
If this isn't what you're looking for, then you need to explain yourself better, perhaps attaching a sample project.

 

DBEditTest-0.0.1.tar.gz

I finally found what I was looking for … update any file with any number of columns with data. (I think )
Thanks to all for putting up with me   

Public Sub UpdateFileData(sFileName As String, sFileIndex As String, saInfo As String[])
   Dim obj As ResultField
   Dim sBkAccColumn As String = "accname"
   Dim x As Integer = 0
   
    For Each obj In RS.Fields
      $con.Exec(db.Subst("update [&1] set &4 = &5  where  '&2' = &3", sFileName, sBkAccColumn, sFileIndex, obj.name, saInfo[x]))
      Inc x
   Next
End

GrayGhost said

I finally found what I was looking for … update any file with any number of columns with data. (I think )
Thanks to all for putting up with me   

From “Post #12,903”, November 10th 2025, 11:00 AM

I bet you learned a lot in the struggle to finding your solution  
No better way of learning…