Share My Creation MySQL + PHP incl. Source Code

hi,

2 days ago i touched for the first time MySql. I had no clue where to start. it was a very new topic for me. But thanx to this great community i have after 2 days a working example of MySql+PHP

First i would like to thanx to @KMatle, @sorex, @DonManfred, @Lahksman and ofcourse @Erel

So what does this app do?

it has 2 Screens:

* Registration screen
* MySql Screen

In the registration screen you can login with your username and password that you create in this app (not your b4x details). After Signing Up you will be able to login.

There is a b4login.php file that add your details to a db and ask for it when you try to login.

in the MySql Screen you have 5 buttons and a listview

you can Count Entries, Get all Entries, Update an Entry, Insert a new Entry, Delete an Entry

the mysql db table has 6 columns (id,name,adress,email.phone,vip)

The app sends a HttpJob to b4ascript.php and from there to MySql.

i am not using any libs except the jCore, jFX (that are defaults) and jHttpUtils2

i created a code module where all incoming data are converted to strings.

i assume that most of you allready know mysql+php very well but for me it was a very new topic so maybe this example could help also to other newbies like me

(Note that you can use this code also in b4a and b4i !!)

you have 3 php files:

Login, Get/Edit/Add/Delete from db and a mydetails php

you only need to change the password, db, username and host in mydetails.php and upload all 3 php's to your host.

i don't include those informations in my other php's because @KMatle explained to me how importent it is not to include them in those php's. (it would not be safe enough)

Have fun

 

[Erel]

I still don't see why you didn't use jRDC2... It would have been simpler, with better security and much more powerful.

Specifically your current code is vulnerable to SQL injection:

case "InsertNewPerson":
     $id = $_GET["id"];
     $name = $_GET["name"];
     $adress = $_GET["adress"];
     $email = $_GET["email"];
     $phone = $_GET["phone"];
     $vip = $_GET["vip"];
     $q = mysql_query("INSERT INTO $table (name, adress, email, phone, vip) VALUES ('$name', '$adress', '$email', '$phone', '$vip')");

You should use mysql_real_escape_string or a similar method to escape all user input before you use it in a query.

 

[ilan]

i will try it out but it will require a different pc where the server is always running, right?

 

[Erel]

i will try it out but it will require a different pc where the server is always running, right?

No. You can run one or more B4J servers on the same server.

 

[ilan]

i have downloaded jrdc2 but there are some libs missing, (ByteConverter)

anyway i would like to understand how it works, because i have almost no experience with mysql.

i see that the client is connecting to a wifi ip (192.168.0.x) the question is if i run the jrdc2 server on my raspberry pi2 how can i connect to it from my android device via 3g??

 

[Erel]

BTW, here is a simple example of a possible SQL injection that deletes all data from the table:

This is the code:

$id = $_GET["id"];
$q = mysql_query("DELETE FROM $table WHERE id='$id'");

The user can pass this parameter:
0' OR '1' = '1

i have downloaded jrdc2 but there are some libs missing, (ByteConverter)

It is the B4A ByteConverter library.

i see that the client is connecting to a wifi ip (192.168.0.x) the question is if i run the jrdc2 server on my raspberry pi2 how can i connect to it from my android device via 3g??

Where is the MySQL database hosted?

 

[ilan]

It is the B4A ByteConverter library.

sorry, i am a little bit confused, jrdc2 is not a b4j application?
i thought it is a b4j application that i will need to run on a different pc and then send to it http requests like i do now to php.

Where is the MySQL database hosted?

whois.com

 

[Erel]

It is a B4J application. Many B4A libraries are compatible with B4J.

i thought it is a b4j application that i will need to run on a different pc and then send to it http requests like i do now to php.

It is somewhat similar to the PHP. You can run it on the same server. You don't need a different computer.

The only exception is if you are using a shared hosting for the server. In that case you will only be able to use PHP.

 

[ilan]

It is a B4J application. Many B4A libraries are compatible with B4J.

thanx, i didn't knew that

It is somewhat similar to the PHP. You can run it on the same server. You don't need a different computer.

so i just upload it to my server and instead of calling php script i send it to jrdc2??

 

[Erel]

You need to edit the configuration file and run it on your server: https://www.b4x.com/android/forum/threads/server-run-a-server-on-a-vps.60378/#content

On the client side you need to use DBRequestManager class to interact with the server (with jRDC2). It is compatible with B4A, B4i and B4J.

 

[sorex]

I don't mind the php+mysql combo, it's always good to have it under the hood as experience when there is demand for it (clients).

just some small remarks (only from looking at the source via notepad2) besides the missing escaping...

$q = mysql_query("SELECT * FROM $table");

while it is handy to use you should avoid * as it behave worse than only getting the required fields as it pulls in entire rows.
It doesn't matter for what you're doing here but when things get complex with a lot of active users you'll start noticing it.

$q = mysql_query("SELECT id, name, adress, email, phone, vip FROM $table ORDER BY id ASC");

order by is always ascending, no need to add it unless you want to do DESCending.

mysql_query("SELECT password FROM $table WHERE username='$username'");

the = might cause issues when using ilan or Ilan, better use username like '$username' instead.
for the password you might need a binary compare method depending on the used encoding.
otherwise PaSsWoRd might still be a valid match when entering password.

for the rest a nice example to get going without any knowledge.

 

[ilan]

while it is handy to use you should avoid * as it behave worse than only getting the required fields as it pulls in entire rows.
It doesn't matter for what you're doing here but when things get complex with a lot of active users you'll start noticing it.

if i want to read all entries from my db is there a different way instead of using * ?

order by is always ascending, no need to add it unless you want to do DESCending.

when my id was not a primary key and AI i did get the entries not sorted and after fixing it i just left it as it was before, but good to know that it is done automatically

the = might cause issues when using ilan or Ilan, better use username like '$username' instead.

yes you are right, is see that now that if i use capital letters the username still works but password will not work if you type PaSsWorD because its getting the password you have entered when you signed up and compare it with the string that is in your textfield so it must be the same

for the rest a nice example to get going without any knowledge.

thanx

 

[ilan]

besides the missing escaping...

can you give me an example how to do it right??

 

[sorex]

if i want to read all entries from my db is there a different way instead of using * ?

just "SELECT id, name, adress, email, phone, vip FROM $table" like you did for the persons.

for the count you used * but select count(*) will be faster than reading everything and then check the row count.

 

[sorex]

$username=mysql_real_escape_string($_POST["username"]);


or for easier handling


$userName=sqlescape($_POST["name"]);

function sqlescape(v){return mysql_real_escape_string(v);}