Question

I have 3 tables, but I need that when the user logs in and enters his login and password, it shows me only the information of that client. But it is showing me the information of all the clients.

Bank(mid,code,cid,sid,type,amount,mydate,note) from CPS300 This is the table where I want the information of a client to be seen

Customers(id,name,login,password) from CPS200

Sources(id,name) from CPS200

---------------------------------------------------------------------------------------------------------------------------------

for now I have two queries, but I must correct the one for client authentication because I must prevent SQL injections

SELECT c.login,c.password FROM CPS200.Customers c WHERE login='$username' or password='$password'"; (wrong query - SQL injections)

SELECT b.mid,b.code,b.type,b.amount,s.name as source,b.mydatetime,b.note
FROM CPS300.Bank as b
JOIN CPS200.Sources s ON b.sid=s.id;

Answer 1

which gives a pass to any 3 rd party user having any one of the credential of the user.

Query to get specific customer's information who gets logged in the system.

SELECT b.mid,b.code,b.type,b.amount,s.name as source,b.mydatetime,b.note
FROM ((CPS300.Bank as b
INNER JOIN CPS200.Sources s ON b.sid=s.id) INNER JOIN CPS200.Customers c ON c.id=s.id AND c.name=s.name) WHERE login='$username' AND password='$password';

The above query fetches ID and name of the customer according to their loginID and password and then using id and name of soucre table cpk(composite primary key) fetch the data from bank table to display the specific information of unique customer.

And alternatively you can also refer to query

select b.mid,b.code,b.type,b.amount,s.name as source,b.mydatetime,b.note from Bank b where b.sid=(select id from Sources where id =(select id from Customer WHERE login='$username' AND password='$password' ) and name= (select name from Customer WHERE login='$username' AND password='$password'))

The above query filters id and name from customer table for the given login credentials and uses that id and name to get id and name from the sources table which further makes a pull request to the bank table as per the fetched sid to print the requisite details.