Connecting to MySQL from Java using PHP

Question

I'm having a weird problem of connection between Java, PHP and MySQL.

I'm not having direct access to my MySQL database on the webserver through Java. So, I converted the mechanism to run a PHP script, which does the job for me and return the results to my Java Application.

It's working pretty fine for most of the queries. But I'm not getting expected results when the queries include character fields in the query.

I include the PHP and Java Code snippets for reference.

Please find me the thing, where I'm doing wrong...

PHP Script on the server...

<?php
$query=$_POST["query"];
echo" Query = ".$query."\n";
if($query){
$link=mysql_connect("mysql2.000webhost.com","a7946109_other","mypassword");
if($link){
mysql_select_db("a7946109_other");
$result=mysql_query($query);
if($result){
while($row=mysql_fetch_row($result)){ // for each row in results
foreach($row as $value){                           // for each column in row
echo "\t".$value;
}
echo"\n";
}
}
}else{
echo " MySQL connect failed ! \n";
}
}else{
echo " No Query ! \n";
}
exit();  // exit without auto_append_file
?>

Java Program...

import javax.swing.*;
import java.awt.*;
import java.io.*;
import java.net.*;
import java.applet.*;
import java.security.*;

public class MySQL_POST {
    public static void main(String r[]){
    HttpURLConnection conn=null;
    try{
    URL url=new URL("mywebsite.com/postdb.php");
    String agent="Applet";
    String query="query=" + r[0];
    String type="application/x-www-form-urlencoded";
    conn=(HttpURLConnection)url.openConnection();
    conn.setDoInput(true);
    conn.setDoOutput(true);
    conn.setRequestMethod("POST");
    conn.setRequestProperty( "User-Agent", agent );
    conn.setRequestProperty( "Content-Type", type );
    conn.setRequestProperty( "Content-Length", ""+query.length());

    OutputStream out=conn.getOutputStream();
    out.write(query.getBytes());
    BufferedReader in = new BufferedReader(new InputStreamReader(conn.getInputStream()));
    String inputLine;
    while((inputLine=in.readLine())!=null){
        System.out.print(inputLine+"\n");
    };
    in.close();
    int rc = conn.getResponseCode();
    System.out.print("Response Code = "+rc+"\n");
    String rm=conn.getResponseMessage();
    System.out.print("Response Message = "+rm+"\n");
    }catch(Exception e){
    e.printStackTrace();
    }finally{
    conn.disconnect();
    }
    }

    }

I Have a table named TEST, with fields SID INT(3), SNAME VARCHAR(20), SLNAME VARCHAR(20)

It has got two entries in the table.

When I execute the following queries using Java, I get different types of outputs as follows.

> java MYSQL_POST "SELECT * FROM TEST"

 Query = SELECT * FROM TEST
 1 Test1 Test1L
 2 Test2 Test2L
Response Code = 200
Response Message = OK

Which is want I expected.

> java MYSQL_POST "SELECT SNAME FROM TEST WHERE SID = 1"

 Query = SELECT SNAME FROM TEST WHERE SID = 1
 Test1
Response Code = 200
Response Message = OK

Which is also as I expected.

Here comes the weird one.

> java MYSQL_POST "SELECT SID FROM TEST WHERE SNAME = 'Test1'"

  Query = SELECT SID FROM TEST WHERE SNAME = \'Test1\'
 Response Code = 200
 Response Message = OK

Which doesn't result in any result, which as expected should be 1

The queries, which involve characters like SNAME = 'Test1' or any other or not working. Except these queries, others are fine.

Please anyone help me in fixing this.

Thanks in advance.

Solution

The server running your PHP script has the magic quotes setting on. This automatically escapes quotes in incoming data from POST/GET/etc with backslashes. Using magic quotes is not recommended and they have been deprecated since PHP 5.4.0. Disable them and add proper sanitation to your code - it's a huge security risk to execute queries straight from external input.