PHP: Include an SQL query containing $variables

Question

I really dislike writing SQL queries within my PHP.

Given the following example piece of PHP:

script.php

$name = 'Bill'
$query = "SELECT * FROM a_table WHERE name='$name'";

I'd like to instead write the sql query in it's own file and include (or otherwise) get the contents into my script:

query.sql

SELECT * FROM a_table WHERE name='$name'

script.php

// This doesn't work obviously
$query = '"'.include('query.sql').'"';

Note that I'd like to be able to reference PHP variables within the SQL query (e.g: $name is setup declared in script.php but used as part of the query).

Watch out for SQL injection when writing such queries. See: stackoverflow.com/questions/60174/… — Pierre-Loup Pagniez
– Pierre-Loup Pagniez, Commented Sep 22, 2016 at 8:39
@Pierre-LoupPagniez thanks for that link. In my case all user input was being cleaned separately before being used in queries, but the attached question/answer is fantastic — s-low
– s-low, Commented Sep 22, 2016 at 9:22

Caspar Wylie · Accepted Answer · 2016-09-22 08:37:35Z

4

nice and simple solution:

 $sql = file_get_contents("query.sql");

And to reference variables, I suggest using PDO where you reference like this

$query = $db->query( "SELECT x FROM x WHERE x = :example")
$query->execute(array("example"=>$value));

...But you can do something similar to this in mysqli, etc.

answered Sep 22, 2016 at 8:37

Caspar Wylie

2,8633 gold badges23 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 12:00:53Z

1

query.sql:

SELECT * FROM a_table WHERE name=:name

script.php:

$query = file_get_contents('path/to/query.sql');

Then bind the parameters and execute the query.

See How can I prevent SQL-injection in PHP? for why you should bind variables instead of concatenating them into your query string.

edited May 23, 2017 at 12:00

CommunityBot

11 silver badge

answered Sep 22, 2016 at 8:40

timclutton

13.1k3 gold badges37 silver badges49 bronze badges

Collectives™ on Stack Overflow

PHP: Include an SQL query containing $variables

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related