Android Question Need advice for storing Data to cloud and ... (B4X)

[ilan]

hi,

i have 2 issues that i would like to consult with you guys.

#1
i have an app with about 25k users. the users are storing data to an kvs file with custom types.
what i have implemented now for the backup solution is to backup the db file to an external storage or even upload it to google drive so the user can download it if he switch to another phone.

the problem is that you have to back up it manually. and most dont do it. now i want to implement an auto backup option that runs once per day. so what i have tought is to use firebase auth to let the user log in with his google account and send the backup file named with his username (email) to my server.

i dont like this solution because :

1, 25k users will send files to my server?? to much storage will be needed.
2, if the file is send and overwrite the old file, there is a risk that the user will send an empty file after switching to a new phone and all data will be lost, what i could do is always send a new file with date but that will lead to too much storage as mentioned in point 1. (in 1 week = 7 backup files * 25k users)

so i am sure there is another solution for that, like using mysql db and create for each user an mysql db but i am not sure if it is possible on my server (have so much db's)

another solution is to upload it to his google drive account but is this possible? i mean an auto upload option to google drive or dropbox??

any other solution is very much appreciated.

#2
now my other problem.
i want to create an app that user can load money to it like pay 20$ via paypal and have on his account 20$ in the app and purchase digital products with that money.
my question is how to make it safe enough? i want to use b4j for that. i was thinking to use firebase auth to let the user login to his account but firebase is a mobile lib so i cannot use it with b4j. i could use mysql (username + password) to let user login to the app but i dont think this is safe enough. anyway even if i will solve to login issue how to make it with the creadit part. so user purchase credit via paypal and use it in the app to purchase products? i could also use for this mysql db so after purchase the credit is stored in the mysql db BUT after using on several games mysql for scoreboards i am sure people hack my mysql db's. because i see scores with 6-7 numbers and thats impossible to archive so they hack it and i want to avoid people hack and put credits to their accounts and use it without buying it via paypal.

i will be very thankful for any help of you guys.

thanx, ilan

 

[udg]

so i am sure there is another solution for that, like using mysql db and create for each user an mysql db but i am not sure if it is possible on my server (have so much db's)

Why not a single DB with an user table?
I mean a table where you register your 25k users, then one or more tables for their "intermixed" data where a foreign key field will refer data to the proper user.

Users           Data
id name        id  fk_users_id   data
1 joe            1    1          this is joe's data
2 ilan           2    1          other data for joe
                 3    2          ilan's data

udg

 

[BillMeyer]

Problem #1
You are already using kvs and already have a server - why not switch to ckvs with the modification to use MySQL as the database engine ? With ckvs you can determine in what direction and how often you update online, when and where to delete data and whether data remains intact without being altered.

Problem #2
SQL Injection - not a nice thing !! Using human readable syntax passed to php etc - also not nice - so my opinion here would be 2 ways you could consider.

1. Since you have a Server, jRDC2 - no question about it - and if you get really clever at that one, you send code words in a call with parameters to your jRDC2 and your server does the rest. Logging in too, becomes quite safe using this method.

2. You could use MQTT with an Encrypted payload to a server that then speaks to a MySQL database (after decryption obviously) and there are ways and means to manipulate MQTT so that it seems as though it is speaking to one individual instead of too everybody at the same time. Of Course Firebase with Data messages (Silent) will probably do the same as MQTT with an encrypted payload.

Exciting stuff !!

And Good Luck !!

 

[Herbert32]

Hi Ilan,

for #2 my suggestion is to store all transactions in your mysql-db and generate checksums over each customers-transaction-part of this table which you store in a non-public-database. Finally only a perfect matching transaction-history which also matches to their checksum grants usage of stored value. You should also store the transaction-ids which are submitted by the payment-processors. Usually there are also checksums integrated which gives you the possibility to check their validity.

So even if your database is hacked, you are able to detect this and you can act against it by suspending this account automatically until the situation is cleared with the customer.

To be more secure against db-hacks you also can generate your own encrypted protocoll based on which your customer-application talks to your server-application which is talking exclusive to your db. - It's up to you how complicated and in result 'hard to hack' your server-communication will be.

best regards
Herbert

 

[ilan]

Why not a single DB with an user table?
I mean a table where you register your 25k users, then one or more tables for their "intermixed" data where a foreign key field will refer data to the proper user.

Users           Data
id name        id  fk_users_id   data
1 joe            1    1          this is joe's data
2 ilan           2    1          other data for joe
                 3    2          ilan's data

udg

ok, so in my case, the user adds shifts (app link) to its DB that is stored on his phone. and what you suggest is to create for each user his own table and upload it a MySQL DB. how about the performance? if a user has about 22 shifts each month for let say 1 year, that would be about 22*12 (264 for each user) * 25000 = 6.600.000 entries in the MySQL. I am very weak when it's about MySql but will it be possible to add so many entries to a MySQL DB? and how will be the performance?

Hi Ilan,

for #2 my suggestion is to store all transactions in your MySQL-DB and generate checksums over each customers-transaction-part of this table which you store in a non-public-database. Finally, only a perfect matching transaction-history which also matches to their checksum grants usage of stored value. You should also store the transaction-ids which are submitted by the payment-processors. Usually there are also checksums integrated which gives you the possibility to check their validity.

So even if your database is hacked, you are able to detect this and you can act against it by suspending this account automatically until the situation is cleared with the customer.

To be more secure against db-hacks you also can generate your own encrypted protocoll based on which your customer-application talks to your server-application which is talking exclusive to your db. - It's up to you how complicated and in result 'hard to hack' your server-communication will be.

best regards
Herbert

I like the idea, so what you are saying is to let the user perform the purchases with MySQL and perform checks in a constant period to find any illegal actions? so compare the purchases with the user transactions. that could work. thank you

Problem #1
You are already using kvs and already have a server - why not switch to ckvs with the modification to use MySQL as the database engine ? With ckvs you can determine in what direction and how often you update online, when and where to delete data and whether data remains intact without being altered.

as far as i know CKVS does not work on a shared hosting.

1. Since you have a Server, jRDC2 - no question about it - and if you get really clever at that one, you send code words in a call with parameters to your jRDC2 and your server does the rest. Logging in too, becomes quite safe using this method.

2. You could use MQTT with an Encrypted payload to a server that then speaks to a MySQL database (after decryption obviously) and there are ways and means to manipulate MQTT so that it seems as though it is speaking to one individual instead of too everybody at the same time. Of Course Firebase with Data messages (Silent) will probably do the same as MQTT with an encrypted payload.

online DB is not my strength so I have no idea how to do the stuff you suggested above.
i have some apps where i use mysql for highscore tables and send data from my app via php to mysql and also receive data. but not more then that so i will need to investigate more about sending encrypt data to online db.

 

[ilan]

Hi Ilan,

for #2 my suggestion is to store all transactions in your mysql-db and generate checksums over each customers-transaction-part of this table which you store in a non-public-database. Finally only a perfect matching transaction-history which also matches to their checksum grants usage of stored value. You should also store the transaction-ids which are submitted by the payment-processors. Usually there are also checksums integrated which gives you the possibility to check their validity.

So even if your database is hacked, you are able to detect this and you can act against it by suspending this account automatically until the situation is cleared with the customer.

To be more secure against db-hacks you also can generate your own encrypted protocoll based on which your customer-application talks to your server-application which is talking exclusive to your db. - It's up to you how complicated and in result 'hard to hack' your server-communication will be.

best regards
Herbert

I could also implement it in my b4j app to make those check automatically. so it will download from the MySQL DB all purchases and checks if the purchase is not higher the transactions. and if yes it will show and alarm or maybe inform me via push notification. so there will not be a live checking method it will be only a check/punish after illegal action method. I guess I will do it like this. it will be a complex b4j app but I like complex apps

 

[Herbert32]

I like the idea, so what you are saying is to let the user perform the purchases with MySQL and perform checks in a constant period to find any illegal actions? so compare the purchases with the user transactions. that could work. thank you

I could also implement it in my b4j app to make those check automatically. so it will download from the MySQL DB all purchases and checks if the purchase is not higher the transactions. and if yes it will show and alarm or maybe inform me via push notification. so there will not be a live checking method it will be only a check/punish after illegal action method. I guess I will do it like this. it will be a complex b4j app but I like complex apps

Yes, I would also have suggested to check the specific users transaction-history only each time a user requests or submitts a new transaction-entry

I'm happy to see that I have been able to point you in a direction, which you see as possible and wish you good luck and great success with you App.

 

[DonManfred]

and what you suggest is to create for each user his own table and upload it a MySQL DB

If i understand it correctly.
You have one User Table and one Datatable with a 1:n relation.

The datatable stores all values of all users. No need for a table for each user.

 

[ilan]

You have one User Table and one Datatable with a 1:n relation.

can you please explain this.

 

[DonManfred]

can you please explain this

Learn SQL. Do i need to give you some google searches like "how to work with databases", "database 1:n relation" "structured Databases", "mysql database for dummies"?

I suggest to get a good "Database for beginners book" or something like that.

- https://www.datapine.com/blog/best-sql-books/
- https://www.vandelaydesign.com/best-database-books/

The Databasestructure is posted by @udg in #2.
One table for the users, one table for the data.

 

[ilan]

Why not a single DB with an user table?
I mean a table where you register your 25k users, then one or more tables for their "intermixed" data where a foreign key field will refer data to the proper user.

Users           Data
id name        id  fk_users_id   data
1 joe            1    1          this is joe's data
2 ilan           2    1          other data for joe
                 3    2          ilan's data

udg

hi udg,

i found a very useful video that explains exactly what you suggest me to do. (video)

my question is if i put all data in that single table how will be the performance if i would like to get all entries from a specific user if i have about 6 milion raws in that table?

thank you

 

[DonManfred]

my question is if i put all data in that single table how will be the performance if i would like to get all entries from a specific user if i have about 6 milion raws in that table?

Consider adding good indexes. Having set good indexes it should be no problem for a databaseengine to get the 2000 raw-data from user 123. But probably you just do not need ALL of them. If you just need the newest then it is only one raw record. Also this can be found by th db engine when you have set indexes.

 

[ilan]

Ok, thanx to everyone. I have understood now how to solve my issues and will start working on it.

For #1 i will use udg suggestion and create 2 tables. One with users and 1 with data and index the user with a uniqe id to improove performance (as donmanfred has explained).

For #2 i will use herbert32 suggestion.

Thank you very much for your time to answer my questions

 

[udg]

Hi Ilan,
as Manfred already told you, indexes are your best friends. Any good DBMS is comfortable with millions of records, so that should pose you no problems.
But you can think about a few "tricks" if you want to be smart..

if a user has about 22 shifts each month for let say 1 year, that would be about 22*12 (264 for each user) * 25000 = 6.600.000 entries

Not necessarily. If for any shift you record initial and ending time, you could pack a whole week into a single DB record. This will lead to about 52 records for each of your 25K user or 1.3 million records.

Another "trick" could be to add a "table reference" to each user record. This way you could record user data in more than one Data table. I mean something like "joe's data are in data table 1; ilan's data are in data table2, udg's data are in data table 1; and so on...".

My point is that, knowing the app, you can design the DB to be efficient both in terms of space and performance (as a trade-off between the two). Just spend some time to think about alternative scenarios. BTW, congratulations for your 25K users!

Ok, now I go for my coffee..

 

[sorex]

my question is if i put all data in that single table how will be the performance if i would like to get all entries from a specific user if i have about 6 milion raws in that table?

#1

it depends on a lot of things. hardware, database structures, the way you build your queries/view, query caching, indexing.

you could split up things in multiple tables for example if your app is mainly month based you could use one table per month which gives you 12 tables and the record amount will be theoretically ~1/12th compared to the flat table.

you can always start with the flat one and if performance kills after a while you can still still split it up which doesn't require any app changes but only on the backend if you do it right from the start.

you can also use 1 month record with userid, month, year and 31 cells for (csv) data.
but there you put yourself at risk when a situation happends where someone might have 2 full or half shifts on one day
and it will require wildcard searching when filtering on some specific hours compared to 1 record with seperate shift start & shift end fields.

you will have to think about how easy it has to be to add, edit & search things and build the ideal structure based on that.

#2

that won't pass apple reviewing, not sure about Google but I guess they also want their cut.
so why not use the regular in-app purchases for this?

 

[ilan]

Not necessarily. If for any shift you record initial and ending time, you could pack a whole week into a single DB record. This will lead to about 52 records for each of your 25K user or 1.3 million records.

Another "trick" could be to add a "table reference" to each user record. This way you could record user data in more than one Data table. I mean something like "joe's data are in data table 1; ilan's data are in data table2, udg's data are in data table 1; and so on...".

yhank you, those are very good ideas. the main purpose of this backup is not to search for a specific shift. what i need is just a backup of all records and when the user lost his phone or his phone broke i want to download all HIS records from the mysql db to his phone so bassicly the reading may be very rear. the writing to db will be on a daily basis but the reading maybe once in a year or so. so even if the process will take few minutes the user will be happy to have all his records backed up and not lost

you could split up things in multiple tables for example if your app is mainly month based you could use one table per month which gives you 12 tables and the record amount will be theoretically ~1/12th compared to the flat table.

I like this idea too, but i think the indexing of the userid is good enough. i made some test with about 3000 records and getting all data from 1 user took about 0.006 sec so that's very fast. and even if it would take a minute or two that's fine since that process may be done only once in a year so only if the user will switch his phone to another and would like to get all shifts to his new phone.

#2

that won't pass apple reviewing, not sure about Google but I guess they also want their cut.
so why not use the regular in-app purchases for this?

its not ment for the Store's.

###############

thanx for everyone here, this is why i like this forum. everyone here has his strengths and is ready to share it with others. and i am happy to be a member of this forum since i really needl ot of help from pro's like you guys especially when its about SQL

so yesterday i make some test and sending data to MySQL db work fine but if i try to send multiple HTTP requests to my server, not all request are executed.
this is my code:

        Dim myusername As String = "testuser" & 25
        Dim myemail As String = "iii@gmail.com"
        Dim myuserdata As String = "bla bla bla"      
       
        Dim j As HttpJob
        j.Initialize("", Me)
        j.Download2("http://www.sagital.net/" & "xxx.php", Array As String ("action", "signup","username", myusername, "email", myemail, "userdata", myuserdata))
        Wait For (j) JobDone(j As HttpJob)
        If j.Success Then
            Log(j.GetString)
        End If
        j.Release

and that's my PHP code:

<?

$table = "users";
include ("xxx");

$con = new mysqli($host, $user, $pw, $db);
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}
mysqli_set_charset($con, "utf8");

$action = $_GET["action"];
 
switch ($action)
{
    Case "getusername":
        $username = $_GET["username"];
        // $q = mysql_query("SELECT password FROM $table WHERE username='$username'");
        $q = mysqli_query($con, "SELECT password FROM $table WHERE username='$username'");
        $rows = array();
        // while($r = mysql_fetch_assoc($q))
        while($r = mysqli_fetch_assoc($q))
        {
            $rows[] = $r;
        }
        print json_encode($rows);
    break;
 
    case "signup":
        $username = $_GET["username"];
        $email = $_GET["email"];
    $userdata = $_GET["userdata"];

        $q = mysqli_query($con, "INSERT INTO $table (username, email, userdata) VALUES ('$username', '$email', '$userdata')");
        print "Inserted";
    break;
}

mysqli_close($conn);
?>

the thing is if i use a multiple request like putting it in a loop not all data appears on my MySQL DB. i do get "inserted" in logs but i dont see them in my mysql db.

at the beginning when sending 20 request only 18 appeared but now about 1 -2 appeares. could it be the my server blocks those requests? is there something i need to setup on my server?

thanx, guys

 

[sorex]

a small hint... never use plain $_GET / $_POST. Do a search for mysqli_real_escape_string.

you could create a single bulk insert request to bypass attack trickers on the hosters firewall.

 

[ilan]

a small hint... never use plain $_GET / $_POST. Do a search for mysqli_real_escape_string.

you could create a single bulk insert request to bypass attack trickers on the hosters firewall.

can you help me with that?

 

[ilan]

a small hint... never use plain $_GET / $_POST. Do a search for mysqli_real_escape_string.

is this the right way?

$city = mysqli_real_escape_string($link, $city);

/* this query with escaped $city will work */
if (mysqli_query($link, "INSERT into myCity (Name) VALUES ('$city')")) {
    printf("%d Row inserted.\n", mysqli_affected_rows($link));
}

 

[sorex]

it would probably be

$city = mysqli_real_escape_string($con, $city);

or

$city = mysqli_real_escape_string($con, $_GET["city"]);

unless you changed $con to $link between the code posts above?


try to stick to $_POST as $_GET is limited in size.