Android Question insert data into table with PHP

[LG Arts HD]

hi!

I have a problem.

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

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

<?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

 

[NJDude]

You are POSTING and your PHP script is GETTING.

 

[DonManfred]

Try Download2. This is a GET-Request.

job1.PostString("http://192.168.1.64/obtain.php", "name='" & name & "', orig='" & orig & "', state='" & state & "', opi='" & opi & "'")

job1.Download2("http://192.168.1.64/obtain.php", Array As String("name", name, "orig", orig, "state", state,"opi",opi))

 

[LG Arts HD]

Try Download2. This is a GET-Request.

job1.Download2("http://192.168.1.64/obtain.php", Array As String("name", name, "orig", orig, "state", state,"opi",opi))

it doesn't work I still get Internal Server Error

 

[KMatle]

For the first step, please use a simple php script to echo/print just "Hello world" and try again. Does this work?

 

[BillMeyer]

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 ?

 

[nwhitfield]

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

$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)

$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:

$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.

 

[nwhitfield]

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.

 

[nwhitfield]

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

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.