Problem with MySQL

[badal405]

Dear Erel,
I am facing problem with MySQL while inserting data. Rather then this everything is working perfectly.
When i pass a single word like "Basic4android" or "aa" it's saving. But when i pass more then word with space(f.e "Basic 4 android" or "a a") then it is raising an exception that i already attached. Please see the attachment.

Here below i have given the method how i am sending request to web database server.
Sub btnSubmit_Click
Dim req As HttpRequest ' requesting net data
req.InitializeGet("http://batajordan.com/netdb.php?&action=add& name=Sharmin&deviceID=" & deviceID & "&text=" & txtInput.Text)
httpC.Execute(req, 1)
ProgressDialogShow("data has been saving...")
End Sub

Thanks and best regards.

 

[Erel]

You should use a solution similar to the PHP code and B4A code posted in the MySQL tutorial.

 

[badal405]

Here is the php code for connection. I have quoted this connection file from one of your published user guide pdf file.

<?php
$mysqlDatabaseName = "localhost";
$mysqlUsername = "db_user";
$mysqlPassword = "db_pass";

mysql_connect($mysqlDatabaseName, $mysqlUsername, $mysqlPassword) or die(mysql_error());
mysql_select_db("db") or die(mysql_error());
if(isset($_GET['action'])){
$action = $_GET['action'];
}
if($action == "add"){
$name = $_GET['name'];
$deviceID = $_GET['deviceID'];
$text = $_GET['text'];
mysql_query("INSERT INTO netDB
(name, deviceid, text) VALUES('$name', '$deviceID', '$text') ")
or die(mysql_error());
}
if($action == "getall"){
$result = mysql_query("SELECT name, text, deviceID FROM netDB" ) or die(mysql_error());
//DATA OUT TO PROGRAM
while($row = mysql_fetch_array($result)){
echo $row['name'];
echo "\r\n".$row['text'];
echo "\r\n".$row['deviceID']."\r\n";
}
}
?>

 

[Erel]

The solution mentioned in the MySQL tutorial in the forum uses a different method to send the queries. The reason is to avoid all kinds of issues with encodings.

 

[AndyDroid2012]

Have you tried debugging on the PHP side by doing a simple echo instead of

$name = $_GET['name'];
$deviceID = $_GET['deviceID'];
$text = $_GET['text'];
mysql_query("INSERT INTO netDB
(name, deviceid, text) VALUES('$name', '$deviceID', '$text') ")
or die(mysql_error());

try

$name = $_GET['name'];
$deviceID = $_GET['deviceID'];
$text = $_GET['text'];
$query =  "INSERT INTO netDB(name, deviceid, text) VALUES('"
. $name. "', '" . $deviceID . "', '$text') ";

//open a text file and write $query to the file since we cant simply echo $query;

$fp = fopen("debug.txt", "o");
fwrite($fp, $query);
fclose($fp);

//   mysql_query($query);
//   uncomment if the "query is OK

If the query looks OK then copy the string displayed and go into MySql Admin in your server and
execute the query by pasting into SQL

Does that work ?
You will get a sensible sql error if it fails

 

[Roger Garstang]

To add to Erel's comment, your code example also allows for SQL Injection and other fun things. Not only is no encoding or escaping of values done, but there is the assumption that most of the values exist too. Depending on server config you may even have to strip out extra things in older versions that was added to aid those not encoding and escaping their data to prevent injections:

if (get_magic_quotes_gpc() == 1) {
   foreach ($_POST as &$value) $value = stripslashes($value);
   foreach ($_GET as &$value) $value = stripslashes($value);
}

With the assumptions of values existing and such too you may also be getting error/warning text back causing issues if reporting of them isn't off.

I actually prefer the mysqli library in PHP too which gives even better options and allows for providing SQL Parameters to prevent injection. Lots of "or die" lines in the code too. I usually properly handle errors and close things/free results to not have issues, even if the documentation claims it is done for you.

 

[badal405]

Sorry dear, It is working fine with the previous code. Due to some reasons It was not working with the emulator but when i installed to my phone i found it is working without having any error. Sorry guys to bother you and thank you very much for quick reply.
My regards to all of you.

To add to Erel's comment, your code example also allows for SQL Injection and other fun things. Not only is no encoding or escaping of values done, but there is the assumption that most of the values exist too. Depending on server config you may even have to strip out extra things in older versions that was added to aid those not encoding and escaping their data to prevent injections:

if (get_magic_quotes_gpc() == 1) {
   foreach ($_POST as &$value) $value = stripslashes($value);
   foreach ($_GET as &$value) $value = stripslashes($value);
}

With the assumptions of values existing and such too you may also be getting error/warning text back causing issues if reporting of them isn't off.

I actually prefer the mysqli library in PHP too which gives even better options and allows for providing SQL Parameters to prevent injection. Lots of "or die" lines in the code too. I usually properly handle errors and close things/free results to not have issues, even if the documentation claims it is done for you.

 

[badal405]

Sorry dear, It is working fine with the previous code. Due to some reasons It was not working with the emulator but when i installed to my phone i found it is working without having any error. Sorry guys to bother you and thank you very much for quick reply.
My regards to all of you.

Dear Erel,
I am facing the same problem with MySQL while inserting data. I have posted it before. Still it is working with any mobile have android gingerbread. But when i tried with android 4.1 it's through an exception.
The previous exception was different and it has been solve by the HttpUtil2 and HttpJob method. I have attached the exception.

When i pass a single word like "Basic4android" or "aa" it's saving. But when i pass more then word with space(f.e "Basic 4 android" or "a a") then it is raising an exception that i already attached. Please see the attachment.

waiting for your reply.

 

[Erel]

Note that you can copy the error from the Logs tab (right click to copy).

You should encode the URL. You can use HttpJob.Download2 to do it.

 

[badal405]

Thank you very much Erel. It is working.