Android Question (Solved) jRDC2 can someone give example on correct authentication

[knutf]

In Erel's original thread about jRDC2 he gives an example of the use of it. The only authentication in the example, as far I can see, is the login on to the database with username and password suplied in the config.properties file. When Erel and Aeric writes that authentication should be done on server side in a recent thread I don't think they mean the login to the database, right?

From Erel's original example about jRDC2, it is not easy for me to understand how authentication on the server side should be done. Can someone please show an example. It is nice if it is based on Erel's example.

In Erel's example, an animal table is created. Let's say we want individual users to log in with their individual username and password in their app. They can insert animals into the table when they are logged in. And when they request animals from the database, they only get the animals they have inserted.

 

[aeric]

I assume you understand SQL and database concepts such as 1 to 1, 1 to Many and Many to Many relationships.

I think there are 2 parts of your question above.
1. We need to make sure a user is registered in the database and allowed to execute commands with correct username and password
2. We need to make sure he/she only execute queries or update on his/her data, not data belongs or linked to another user

Let say the data looks like below.

users table

id	name	password
1	Alice	xxxxxx
2	Bob	yyyy

animals table

id	name
1	Cat
2	Dog
3	Echidna
4	Frog

user_animals table

user_id	animal_id
1	1
2	2
1	3

SELECT a.* FROM users u
JOIN user_animals ua ON ua.user_id = u.id
JOIN animals a ON ua.animal_id = a.id
WHERE u.name = 'Alice' AND u.password = 'xxxxxx'

This query will return 2 animals (Cat, Echidna) linked to Alice

SELECT a.* FROM users u
JOIN user_animals ua ON ua.user_id = u.id
JOIN animals a ON ua.animal_id = a.id
WHERE u.name = 'Bob' AND u.password = 'yyyy'

This query will return 1 animal (Dog) linked to Bob

If either one of the users or let say Oscar provides wrong username and password, no results are return.

UPDATE animals
SET name = 'Hedgehog' WHERE id = 3
AND id IN (SELECT a.id FROM animals a
JOIN user_animals ua ON ua.animal_id = a.id
JOIN users u ON ua.user_id = u.id
WHERE u.name = 'Alice' AND u.password = 'xxxxxx')

When Alice provides the correct password and the animal id she is updating is linked to her then the update will take place.

UPDATE animals
SET name = 'Gorilla' WHERE id = 3
AND id IN (SELECT a.id FROM animals a
JOIN user_animals ua ON ua.animal_id = a.id
JOIN users u ON ua.user_id = u.id
WHERE u.name = 'Bob' AND u.password = 'yyyy')

When Bob provides the correct password but the animal id is not linked to him then the database is not updated.

We can add above queries into config.properties of jRDC2 server as:

sql.select_user_animals = SELECT a.* FROM users u \
JOIN user_animals ua ON ua.user_id = u.id \
JOIN animals a ON ua.animal_id = a.id \
WHERE u.name = ? AND u.password = ?

sql.update_user_animal = UPDATE animals \
SET name = ? WHERE id = ? \
AND id IN (SELECT a.id FROM animals a \
JOIN user_animals ua ON ua.animal_id = a.id \
JOIN users u ON ua.user_id = u.id \
WHERE u.name = ? AND u.password = ?)

This is just a simple example. There are always room for improvement.

 

[aeric]

In Erel's original thread about jRDC2 he gives an example of the use of it. The only authentication in the example, as far I can see, is the login on to the database with username and password suplied in the config.properties file. When Erel and Aeric writes that authentication should be done on server side in a recent thread I don't think they mean the login to the database, right?

From Erel's original example about jRDC2, it is not easy for me to understand how authentication on the server side should be done. Can someone please show an example. It is nice if it is based on Erel's example.

In Erel's example, an animal table is created. Let's say we want individual users to log in with their individual username and password in their app. They can insert animals into the table when they are logged in. And when they request animals from the database, they only get the animals they have inserted.

If you think my answer above is correct, you can mark as solution (click the checkmark at right side of the post). Thanks.

 

[knutf]

you can mark as solution (click the checkmark at right side of the post)

I didn't now this. Yes your answer is a solution!

 

[aeric]

Here is another example but without using an intermediate table.

users table

id	name	password
1	Alice	xxxxxx
2	Bob	yyyy

animals table

id	name	user_id
1	Cat	1
2	Dog	2
3	Echidna	1
4	Frog	0

SELECT a.* FROM users u
JOIN animals a ON a.user_id = u.id
WHERE u.name = 'Alice' AND u.password = 'xxxxxx'

UPDATE animals
SET name = 'Hedgehog' WHERE id = 3
AND id IN (SELECT a.id FROM animals a
JOIN users u ON a.user_id = u.id
WHERE u.name = 'Alice' AND u.password = 'xxxxxx')

This one look simpler but there is difference.

Both Alice and Bob can add any animal to their list.
e.g. Alice and Bob can add Cat to their list.
For example 1, if one day an Admin user edit Cat and update it as Tom then Alice and Bob will see Tom in their list.

In example 2, one day Bob edit record in id 5 which is Cat to Mouse, this will not affect Cat in Alice's id 1.

id	name	user_id
1	Cat	1
2	Dog	2
3	Echidna	1
4	Frog	0
5	Cat	2

id	name	user_id
1	Cat	1
2	Dog	2
3	Echidna	1
4	Frog	0
5	Mouse	2

I hope this does not confuse anyone.

 

[knutf]

Here is another example but without using an intermediate table.

I like the solution without intermediate table better.

But how should inserts be restrected with username and password?

 

[aeric]

how should inserts be restrected with username and password?

sql.insert_user_animal = INSERT INTO animals \
(name, user_id) \
SELECT ?, id FROM users \
WHERE name = ? AND password = ?

 

[aeric]

I uploaded an example project to my GitHub

jRDC2 server authentication + console client

Simple jRDC2 server authentication example with user name and password This example is based on this solved question for learning purpose. If user name and password are not matched, the query will show 0 row result or affected rows. I added a non-UI console app to connect to the server. Edit...

www.b4x.com