How to query using a query from an SQL query

Question

OK, so really complicated title, let me explain.

I'm trying to insert SQL queries into my SQL database, e.g.

INSERT INTO sample_db(query) VALUES ('SELECT * FROM users WHERE id={$userID}')

Then later on from another PHP file I'll do something like this:

mysqli_query($queryfromabove);

The problem is the PHP variable does not get passed through. (it exists in the file I call it from) I know this is highly unorthodox, and probably not recommended, but is there any way anyone knows of to do this?

As requested here's the actual code:

  $sql="INSERT INTO awards(name,image,query,clm,type,number)    VALUES ('".$_POST['name']."','".$_POST['image']."','".$_POST['query']."','".$_POST['column']."','".$_POST['condition']."','".$_POST['number']."')";
    mysqli_query($conn,$sql);

I've echoed all of the POSTS and know they have the proper data. The important POST variable here, is $_POST['query'] because it contains:

SELECT * FROM crts WHERE id='$crtid'

Then, from another file (excuse the sloppy variable names, this is a WIP):

    $that = mysqli_fetch_assoc(mysqli_query($conn,"SELECT * FROM awards WHERE id=2"));
echo $that['query'].'<br>';
$crtid = $_SESSION['crt']['id'];
$query = $that['query'];
$thisquery = mysqli_query($conn,$query);
$finally = mysqli_fetch_assoc($thisquery);
print_r($finally);

ID 2 is the id of the sql result that I inserted with all the posts.

Is that query in single quotes? $queryfromabove = 'query'; — chris85
– chris85, Commented Jul 10, 2015 at 23:19
No, the query as far as I know isn't. Actually to be very specific, I'm getting the first query from a $_POST variable. EDIT: Oh you mean the $queryfromabove variable. No, when I echo that variable out I don't see any quotes. — Newah
– Newah, Commented Jul 10, 2015 at 23:20
Don't do that. It is a serious security problem to get a query from a variable your user can change arbitrarily. Imagine that they POSTed the query "DROP TABLE sample_db.". — fmt
– fmt, Commented Jul 10, 2015 at 23:21
@Actorclavilis This is purely an admin thing, users won't have access to it. — Newah
– Newah, Commented Jul 10, 2015 at 23:22

Barmar · Accepted Answer · 2015-07-11 01:08:01Z

2

Use a prepared statement. Put the following in the DB:

INSERT INTO sample_db(query) VALUES ('SELECT * FROM users WHERE id=?')

Then when you want to execute it, you first prepare it, bind the parameter to the variable, and execute it.

$stmt = mysqli_prepare($conn, $queryfromabove);
mysqli_stmt_bind_param($stmt, "s", $userID);
mysqli_stmt_execute($stmt);

If you need to be able to substitute arbitrary variables, you could use preg_replace_callback to replace all {$variable} in the string with the value of the corresponding global variable.

$sql = preg_replace('/\{\$(\w+)\}/', function($matches) use ($conn) {
    return mysqli_real_escape_string($conn, $GLOBALS[$matches[1]]);
}, $queryfromabove);
mysqli_query($conn, $sql);

edited Jul 11, 2015 at 1:08

answered Jul 10, 2015 at 23:28

Barmar

789k57 gold badges554 silver badges669 bronze badges

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

3 Comments

Newah Over a year ago

This is a very good idea, but is there any way to specify the name of the variable in the query? In this example you'd have to assign variable names after the fact.

Barmar Over a year ago

I added a solution that uses preg_replace_callback to replace all variable references with their values.

Newah Over a year ago

Thank you so much man, you've actually answered one of my questions before and I'm thoroughly convinced you're a PHP wizard. This worked brilliantly.

Jason Edmonds · Accepted Answer · 2015-07-10 23:30:08Z

0

Allowing an SQL Query string to be stored would expose your database to malicious attacks. Please consider using Stored Procedures - this is an example for MySQL. http://dev.mysql.com/doc/connector-net/en/connector-net-tutorials-stored-procedures.html

answered Jul 10, 2015 at 23:30

Jason Edmonds

947 bronze badges

4 Comments

Barmar Over a year ago

But he wants the query to pick up values of PHP variables. How would a MySQL stored procedure do that?

Jason Edmonds Over a year ago

Thanks for the question, Barmar. As the query is a POST value, it can be changed client side which makes it a security risk, rather than calling a query directly from the $_SESSION['query'] variable, the author should call a reference to a Stored Procedure server-side so a dirty 'query' value wont permit a malicious query call. The value of the $_SESSION['query'] should call a PHP function, probably through a switch, to collect the PHP variables and populate any Stored Procedure arguments.

Barmar Over a year ago

He said in a comment that this is an administrative procedure, so the users who submit the form are presumably trusted. They can probably execute SQL queries directly, so allowing them to insert queries into the DB doesn't create any extra security risk.

Jason Edmonds Over a year ago

Fair enough, though one never knows if that may change in the future. In my humble opinion, better to do it right the first time.

Collectives™ on Stack Overflow

How to query using a query from an SQL query

2 Answers 2

3 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related