Android Question insert data into table with PHP

LG Arts HD

Member
Licensed User
Longtime User
hi!

I have a problem.

I 'm trying to post variables into a database from b4a using HttpJob

B4X:
Sub send_Click
    name = nam.Text
    orig = orig.Text
    opi = op.Text
   
    Dim job1 As HttpJob
    job1.Initialize("Job1", Me)
    job1.PostString("http://192.168.1.64/obtain.php", "name='" & name & "', orig='" & orig & "', state='" & state & "', opi='" & opi & "'")
End Sub

Sub JobDone (Job As HttpJob)
    Log("JobName = " & Job.JobName & ", Success = " & Job.Success)
    If Job.Success = True Then
                Log(Job.GetString)
    Else
        Log("Error: " & Job.ErrorMessage)
        ToastMessageShow("Error: " & Job.ErrorMessage, True)
    End If
    Job.Release
End Sub

and here's my php script

B4X:
<?php
$servername = "localhost";
$username = "root";
$password = "1234";
$dbname = "visitors";

$conn = new mysqli($servername,$username,$password,$dbname);

if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}

$name = $_GET('name');
$orig = $_GET('orig');
$state = $_GET('state');
$opi = $_GET('opi');


$sql = "INSERT INTO mytable(NV, name, orig, state, opi) VALUES(1,'"$name"', '"$orig"', '"$state"', '"$opi"')"

if($conn->query($sql)===TRUE){
    echo "SUCCESS!";
}else{
    echo "ERROR"
}

$conn->close();

?>

the problem is when I click on send, I get this 'Error: Internal Server Error' I thinks something's wrong with my script but I don't know what's wrong

any idea? thank you
 

DonManfred

Expert
Licensed User
Longtime User
Try Download2. This is a GET-Request.
job1.PostString("http://192.168.1.64/obtain.php", "name='" & name & "', orig='" & orig & "', state='" & state & "', opi='" & opi & "'")
B4X:
job1.Download2("http://192.168.1.64/obtain.php", Array As String("name", name, "orig", orig, "state", state,"opi",opi))
 
Upvote 0

BillMeyer

Well-Known Member
Licensed User
Longtime User
This error usually occurs when MySQL cannot complete the query.

Please post your Table structure (mytable) - this will help to find the error.

Also, does this happen the first time (clean Table - just created) or the second/subsequent times you try to insert data ?
 
Upvote 0

nwhitfield

Active Member
Licensed User
Longtime User
Good practice in PHP would be to use $_REQUEST rather than $_GET; that way your script will work with both GET and POST. I'd also strongly suggest that you rewrite that code to use a prepared query, which will be much, much safer - depending on the values of the variables, your SQL could end up hopelessly mangled, and potentially vulnerable to an injection attack.

Try something along the lines of

B4X:
$query = $conn->stmt_init() ;

if ( $query->prepare("INSERT INTO mytable SET NV = 1, name = ?, orig = ?, state = ?, opi = ?") {
  $query->bind_param('iiii',$_REQUEST['name'],$_REQUEST['orig'],$_REQUEST['state'],$_REQUEST['opi']) ;
  $query->execute() ;
}

Note that in bind_param, I've assumed each column is an integer (iiii); if not, replace the i with s for string, eg if name is a string and the others are integers, use 'siii'.

If you don't want to use a prepared statement, then you must at the very least sanity check that data, and use real_escape_string. For example (and assuming name is a string, others are int)

B4X:
$sql = sprintf("INSERT INTO mytable SET NV = 1, name = '%s', orig = %d, state = %d, opi = %d",$conn->real_escape_string($_REQUEST['name']),$_REQUEST['orig'],$_REQUEST['state'],$_REQUEST['opi']) ;
$conn->query($sql) ;

Building the query with sprintf like this has two advantages - one is that it's much easier to read, and to spot quoting mistakes. And secondly by using %d you force an integer value for fields that are int, even if the parameters passed are strings.

It's also a really good idea to do sanity checking, for example, if state might be one of three options ( 'ready', 'waiting', 'done'), and opi should be an int you might say something like:

B4X:
$state = (( $_REQUEST['state'] == 'waiting' ) || ( $_REQUEST['state'] == 'ready']) || ( $_REQUEST['statue'] == 'done')) ? $_REQUEST['state'] : 'waiting' ;
$opi = ( intval($_REQUEST['opi']) == $_REQUEST['opi'] ) ? $_REQUEST['opi'] : 0 ;
to force the value to be 'waiting' if it's not one of the valid options.

This may seem like overkill, if you think only your app will ever talk to that server, but believe me - there are plenty of people who will throw anything they can at a script that they suspect might give access to an SQL database.
 
Upvote 0

nwhitfield

Active Member
Licensed User
Longtime User
Also, in your original $sql = ... the quotes are all over the place. You have extra double quotes you don't need, effectively terminating the string.

If you want to leave those in, there should be a . before the variable name. Or remove the double quotes from the middle - because you have double quoted the string variable substitution will be applied.

But, really, use one of the other suggestions I made above - sprintf to build it, or a prepared query.
 
Upvote 0

nwhitfield

Active Member
Licensed User
Longtime User
By the way, another handy trick if you have access to the command line on the server (or even on the PC where you're writing your scripts, if it has php installed).
Before calling the script via the web server, do a quick 'lint' check with php -l, like this

B4X:
php -l myscript.php

It won't execute the code, but it will parse it and find common errors, like missed brackets, semicolons, invalid function (but not method) names. (Ie, if you misspell preg_match as preg_macth it will spot that, because it's not a function, but a method like $mydb->quary($sql) instead of $mydb->query($sql) won't be spotted). Nevertheless, it's always a good check before uploading your script, and will give more information than the simple 'internal server error' that you get via the web server.
 
Upvote 0

Similar Threads

Top