SQL INSERT INTO SELECT statement

Question

I have a android app that is using MySQL server, And im trying to find out how to use INSERT INTO SELECT statement. Here is what I have in my php script. It works when I test it with 'hard code' values.

<?php
require "conn.php";
$UserID = $_POST["UserID"];
$EventID = $_POST["EventID"];

$query = "INSERT INTO Interested (UserID, EventID) 
SELECT '$UserID' , '$EventID' FROM Interested 
WHERE NOT EXISTS (SELECT UserID, EventID FROM Interested WHERE UserID = '$UserID' 
AND EventID = '$EventID') LIMIT 1;";

$result = mysqli_query($conn ,$query);
if(mysqli_num_rows($result) > 0)
{
    echo "Interested";
}
else
{
    echo "Duplicate";
}
?>

Android gives me this error message

08-24 21:57:39.517 8553-9030/com.example.feelingoodlivinbeta.socialdreams_a1 E/AndroidRuntime: FATAL EXCEPTION: AsyncTask #3
   Process: com.example.feelingoodlivinbeta.socialdreams_a1, PID: 8553
   java.lang.RuntimeException: An error occurred while executing doInBackground()
       at android.os.AsyncTask$3.done(AsyncTask.java:309)
       at java.util.concurrent.FutureTask.finishCompletion(FutureTask.java:354)
       at java.util.concurrent.FutureTask.setException(FutureTask.java:223)
       at java.util.concurrent.FutureTask.run(FutureTask.java:242)
       at android.os.AsyncTask$SerialExecutor$1.run(AsyncTask.java:234)
       at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1113)
       at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:588)
       at java.lang.Thread.run(Thread.java:818)
    Caused by: java.lang.ArrayIndexOutOfBoundsException: length=2; index=2
       at com.example.feelingoodlivinbeta.socialdreams_a1.InterestedFunction.doInBackground(InterestedFunction.java:32)
       at com.example.feelingoodlivinbeta.socialdreams_a1.InterestedFunction.doInBackground(InterestedFunction.java:24)
       at android.os.AsyncTask$2.call(AsyncTask.java:295)
       at java.util.concurrent.FutureTask.run(FutureTask.java:237)
       at android.os.AsyncTask$SerialExecutor$1.run(AsyncTask.java:234) 
       at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1113) 
       at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:588) 
       at java.lang.Thread.run(Thread.java:818)

You code is vulnerable to [SQL injection](https://stackoverflow.com/questions/601300/what-is-sql-injection) — litelite, Aug 24 '17 at 19:46
why you are using the content of var in select instead tha insert the value directly .. ? — ScaisEdge, Aug 24 '17 at 19:50
The app just crash, @Barmar then the problem is probably not with the script — SpriteAndDreams, Aug 24 '17 at 19:50
Anything in the error log? Have you set up your mysqli connection to throw PHP exceptions when it encounters errors? — Don't Panic, Aug 24 '17 at 19:51
@SpriteAndDreams If you make a unique index on `(UserID, EventID)` then you can use `INSERT IGNORE`. It won't create duplicate values then. — Barmar, Aug 24 '17 at 19:53
`Caused by: java.lang.ArrayIndexOutOfBoundsException:` is the cause of the exception (and app crash). Check out line 32 of `InterestedFunction.java` ("com.example.feelingoodlivinbeta.socialdreams_a1.InterestedFunction.doInBackground(InterestedFunction.java:32)" explains where the exception came from) — WOUNDEDStevenJones, Aug 24 '17 at 20:15
[Little Bobby](http://bobby-tables.com/) says **[you are at risk for SQL Injection Attacks](https://stackoverflow.com/q/60174/)**. Learn about [Prepared Statements](https://en.wikipedia.org/wiki/Prepared_statement) for [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). Even **[escaping the string](https://stackoverflow.com/q/5741187)** is not safe! I recommend `PDO`, which I [wrote a function for](http://paragoncds.com/grumpy/pdoquery/#function) to make it extremely **easy**, very **clean**, and way more **secure** than using non-parameterized queries. — GrumpyCrouton, Aug 24 '17 at 20:20
Uhm think the problem is String value, but thing is i dont know how to send in int values to server @WOUNDEDStevenJones — SpriteAndDreams, Aug 24 '17 at 20:20
@SpriteAndDreams You need to use a regular browser so you can use Developer Tools to see the response with the error message. — Barmar, Aug 24 '17 at 20:23
PHP `$_POST` values are always strings. You can use `intval()` to convert it to a number before the SQL if you want. But (as recommended above), if you use something like PDO to process the query you don't have to worry about parameter type (string vs int) - it will be taken care of automatically for you. — WOUNDEDStevenJones, Aug 25 '17 at 14:09
If you post the Java code/function that's calling this PHP script it will also help us explain what's breaking. The exception is coming from the android app itself, not the PHP script. — WOUNDEDStevenJones, Aug 25 '17 at 14:11

score 0 · Accepted Answer · answered Aug 25 '17 at 16:11

0

Thnx guys I found the solution by using INSERT IGNORE, Thank you @Barmar your co

answered Aug 25 '17 at 16:11

SpriteAndDreams

50
1
10

SQL INSERT INTO SELECT statement

1 Answers1