CODEWITHSUNDEEP
phpmysqlmysqliprepared-statement
Jamie Turner
Jamie Turner

Reputation: 579

Mysqli Prepared statement with a mysql variable

I have a sql query that returns 5 closest locations using latitude & longitude. 

$conn->query("SELECT activityId, ( 3959 * acos( cos( radians(
 -0.0761144) ) * cos( radians(
  activity.latitude ) ) * cos( radians(
  activity.longitude ) - radians(

  51.5080937) ) + sin( radians(
  -0.0761144) ) * sin( radians(
    activity.latitude ) ) ) ) AS distance
from activity order by distance limit 5;");

I need to make this a prepared statement, but to use the ? placeholder doesn't make sense as it would mean binding the same param twice. Oh and it doesn't actually work.

$stmt = $conn->prepare("SELECT activityId, ( 3959 * acos( cos( radians(
 ?) ) * cos( radians(
  activity.latitude ) ) * cos( radians(
  activity.longitude ) - radians(

  ?) ) + sin( radians(
  ?) ) * sin( radians(
    activity.latitude ) ) ) ) AS distance
from activity order by distance limit 5;");

$stmt = $conn->prepare("ddd", $lat, $lng, $lat);

So I've done a little research and from what I can gather using a variable in the mysql statement seems the best approach, but this is where i get stuck, so far I have as follows. 

$stmt = $conn->prepare("

set @lat = ?;
set @lng = ?;    

SELECT activityId, ( 3959 * acos( cos( radians(
 @lat) ) * cos( radians(
  activity.latitude ) ) * cos( radians(
  activity.longitude ) - radians(

  @lng) ) + sin( radians(
  @lat) ) * sin( radians(
    activity.latitude ) ) ) ) AS distance
from activity order by distance limit 5;

");

$stmt->bind_param("dd", $lat, $lng");

However now the params don't bind and I get a error response back stating this. I've had a look on google and the answer I have been directed towards consists of running two queries, the font creating the variables as just a query() then then SELECT statement in a second prepared statement. But surely then the statements are not being prepared and leaves the query open to SQL Injection?

Is there a better way to prepare this statement so it's secure from injection attacks?

//* EDIT *//

After some great answers it seems that using set @var isn't valid in $sql->prepare(); I guess I can now refine the question to be; How can I run this as a prepared statement?:

$stmt = $conn->prepare("SELECT activityId, ( 3959 * acos( cos( radians(
?) ) * cos( radians(
activity.latitude ) ) * cos( radians(
activity.longitude ) - radians(

?) ) + sin( radians(
?) ) * sin( radians(
activity.latitude ) ) ) ) AS distance
from activity order by distance limit 5;");

$stmt = $conn->prepare("ddd", $lat, $lng, $lat);

Upvotes: 1

Views: 609

Answers (2)

meda
meda

Reputation: 45500

Here is the proper way:

$stmt = $conn->prepare("SELECT activityId, ( 3959 * acos( cos( radians(
?) ) * cos( radians(
activity.latitude ) ) * cos( radians(
activity.longitude ) - radians(

?) ) + sin( radians(
?) ) * sin( radians(
activity.latitude ) ) ) ) AS distance
from activity order by distance limit 5;");

$stmt->bind_param("ddd", $lat, $lng, $lat);
$result = $stmt->execute();
if($result){
  echo 'SELECT succesful';
}

Upvotes: 1

lpg
lpg

Reputation: 4937

Maybe you could try setting the type to double?

$stmt->bind_param("dd", $lat, $lng);

Upvotes: 0

Related Questions