B4J Question B4J server & MySql: is this code weak ?

[marcick]

I have a server and hundreds of B4A/B4I clients.
Clients consume "virtual credits" during operations and communicate this value to the server, so that a MySql table is updated with the new credit.
The flow is something like this:

1. Client A consumes 5 credits and posts this value to the B4J server that receive it through a server handler.
2. Server handler receive the value 5
3. Server make a DbQuery and read the current value of the credit, suppose it is 100
4. Server subtract 5 from the credits and store in the DB the new value 95
5. Server send an OK to the client

Now, suppose we are at point 3 in the server and another client communicates its consumed credits to the server, suppose the value is 10.
I suppose a new session of the server handler is started, right ?
What happens if the first handler is not completed and the second handler still read 100 from the DB ?

When everything will be finished, will be the final value in the DB always 85 or may happens 90 ?

 

[XbNnX_507]

I think if you are using SQL pooling to get the connection for every client then this should not be a problem.

 

[Erel]

Are you using ConnectionPool?

Can you post the query?

 

[marcick]

Are you using ConnectionPool?

Can you post the query?

mmmhh. I have a doubt .....
This is the simplified code

Dim conA As SQL
conA=Main.rdcConnector1.GetConnection
Dim cursor1 As ResultSet
Dim query As String="SELECT * from credits WHERE Username=" & Chr(34) & bcm.Get("Username") & Chr(34) & " AND Password=" & Chr(34) & bcm.Get("Password") & Chr(34)
cursor1=conA.ExecQuery(query)
Dim Credits As Int=cursor1.GetString("Credits")
Credits=Credits-bcm.Get("Amount")
Dim query As String="UPDATE `credits` SET `LastAccess` = " & Chr(34) & LastAccess & Chr(34) _
& ", `Credits` = " & Credits & " WHERE `Username` = " & Chr(34) & bcm.Get("Username") & Chr(34) _
& " AND `Password` = " & Chr(34) & bcm.Get("Password") & Chr(34)
conA.ExecNonQuery(query)
resp.Write(Credits)

 

[Erel]

This is not the correct way to build such queries. I recommend you to watch the SQL video tutorial:

 

[marcick]

Is it the query syntax that is not correct or the method I use to update the data?

 

[OliverA]

Use parameterized queries (ExecQuery2, ExecNonQuery2). It looks like you are using a modified version of jRDC2, since you have

conA=Main.rdcConnector1.GetConnection

. Standard jRDC2 uses connection pooling.

 

[marcick]

Use parameterized queries (ExecQuery2, ExecNonQuery2). It looks like you are using a modified version of jRDC2, since you have

conA=Main.rdcConnector1.GetConnection

. Standard jRDC2 uses connection pooling.

My server app is 2 or 3 years old and is a mix ... I remember a time I have switched from JRDC to JRDC2 but I'm no longer sure of nothing.
Can you show me a code where I see connection pooling is used ?

edit: is "SQL.BeginTransaction" and "SQL.TransactionSuccessful" the key to avoid the problem ?

 

[OliverA]

Look at the RDCConnector class. There should be a

Private pool As ConnectionPool

in Class_Globals.
The Initialze method should have

pool.Initialize(config.Get("DriverClass"), config.Get("JdbcUrl"), config.Get("User"), _
   config.Get("Password"))

The the method GetConnection should have

Return pool.GetConnection

 

[marcick]

I confirm: when I call "conA=Main.rdcConnector1.GetConnection" I have a pool.GetConnection from the RDCConnector class.
So, I don't understand where I'm wrong and (coming back to my first post) if the code is weak

 

[XbNnX_507]

Another option would be to create an after select trigger. for step 4 :

Server subtract 5 from the credits and store in the DB the new value 95

Just in case you wanna be sure the value gets updated.

 

[OliverA]

if the code is weak

You're not using parameterized queries. First thing I found that may explain it more: https://blogs.msdn.microsoft.com/sqlphp/2008/09/30/how-and-why-to-use-parameterized-queries/

 

[marcick]

complicated ... but I'll try to understand

 

[Erel]

Is it the query syntax that is not correct or the method I use to update the data?

You should never build queries with concatenation on the server. Use parameterized queries. It is not more complicated, it is actually simpler. Watch the video.
Your current code is weak and vulnerable to SQL injections.

 

[marcick]

Thank you. I've already watched the video but ... ok, I'll watch again and again

 

[marcick]

Is it correct ?

Dim conA As SQL=Main.rdcConnector1.GetConnection
Dim cursor1 As ResultSet
cursor1=conA.ExecQuery2("SELECT * from credits WHERE Username=? AND Password=?", array AS string(Username, Password))
Dim Credits As Int=cursor1.GetString("Credits")
Credits=Credits-5
cursor1=conA.ExecNonQuery2("UPDATE credits SET LastAccess=?, Credits=? WHERE Username=? AND Password=?", array AS String(LastAccess, Credits, Username, Password))
If conA <> Null And conA.IsInitialized Then conA.Close

This way I'm sure nothing can happen between the first and the last line and any other parallel access to the db is queued til the code above is completed ?

 

[OliverA]

Is it correct ?

Instead of Array As String, use Array As Object.

This way I'm sure nothing can happen between the first and the last line and any other parallel access to the db is queued til the code above is completed ?

I don't think pooling gives you that guarantee. Pooling is for parallelizing queries, not serializing them. Have you tried

conA.ExecNonQuery2("UPDATE credits SET LastAccess=?, Credits=Credits-5 WHERE Username=? AND Password=?", Array AS Object(LastAccess, Username, Password))

This way if you have multiple requests coming in for the same user, each request should decrease credits by 5. In your original SQL statement, you explicitly set the credit amount, which may go awry if the statements are executed out of order.

 

[marcick]

This way if you have multiple requests coming in for the same user, each request should decrease credits by 5. In your original SQL statement, you explicitly set the credit amount, which may go awry if the statements are executed out of order.

Oh thanks, I'll try later. Can a variable be used Instead of the specific amount 5 ? (This is the real case)

 

[Erel]

Try to replace the '5' with ? and add a variable to the array. If it doesn't work, the SQL engine will throw an error, then change it to:

conA.ExecNonQuery2($"UPDATE credits SET LastAccess=?, Credits=Credits - ${YourVariable} WHERE Username=? AND Password=?"$, Array AS Object(LastAccess, Username, Password))

 

[Daestrum]

An alternative is changing the createStatement to a type that allows ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE

This will allow you to basically - do a Select Query and change any value inside the ResultSet, which is then reflected back to the table.