Save mysql query into database

Question

I am trying to allow users (In my place of work) to run a query, and if they choose, save it into a database so that they can just run it again later with a single click (Like if they are updating a database). I am having a problem with this, though. Simply running the query isn't an issue, but the query doesn't save correctly. The query is something like this:

LOAD DATA INFILE 'path/to/file/file.txt' INTO TABLE table FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n';

I can get it to save into the database correctly if I escape the backslashes when typing out the query, but that causes the query to run incorrectly on submit. I am wondering if there is a way that I can automatically escape backslashes. Or would it just be easier/better to separate the two features? If any more information is needed let me know, and thanks in advance.

When reading the query back in could you use REPLACE - dev.mysql.com/doc/refman/5.0/en/… to change \\ to \ ? — Giles
– Giles, Commented Oct 16, 2014 at 13:45
Have you tried using prepared statements? In that case you don't need to escape the data. Anyways, if you want to escape before adding to the database, then just unescape when you retrieve it! — ItalyPaleAle
– ItalyPaleAle, Commented Oct 16, 2014 at 13:50
Have you tried mysqli_real_escape_sting() (or similar) or as suggested above use prepared statements to insert the record (thus avoiding the need to escape at all)? — Mike Brant
– Mike Brant, Commented Oct 16, 2014 at 13:53
I have tried mysqli_real_escape_string(). I get this as a result: LOAD DATA INFILE \'path/to/file/file.txt\' INTO TABLE table FIELDS TERMINATED BY \' \' LINES TERMINATED BY \'\n\'; It keeps the \n, but still messes up the \t — Chris Buckley
– Chris Buckley, Commented Oct 16, 2014 at 14:20
Encode the file contents as 'base64' (base64_encode) before storing it? 'base64_decode' when displaying it. Note: 'base64 encoding' is 'database' and 'HTML' 'safe'. — Ryan Vincent
– Ryan Vincent, Commented Oct 16, 2014 at 14:41

Chris Buckley · Accepted Answer · 2014-10-16 15:59:13Z

1

I did some tinkering, and I got it. Here is the solution I came up with:

First, I used str_replace on the update.

$q = str_replace(array("\t", "\n"),array("\\t","\\n"), $_POST['update']);

Then, I prepared it as suggested.

if($stmt = $con->prepare("insert into queries (Query_Name, Query, Description) values (?, ?, ?);")){
    $stmt->bind_param('sss', $qn, $q, $qd);
    $stmt->execute();
    $stmt->store_result();
    $stmt->close();
}

Finally, I ran a mysqli_multi_query (because sometimes there is more than one query in a submission, which caused it to fail) to run the query(s) the user puts in.

$query = mysqli_multi_query($con, $q);

Thanks to everyone who offered suggestions!

answered Oct 16, 2014 at 15:59

Chris Buckley

499 bronze badges

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

Comments

David Ansermot · Accepted Answer · 2014-10-16 13:44:12Z

0

You have to unescape the query before using it.
Create a method dedicated to run queries from the database like "myClass->unescapeAndExecute($escapedQueryFromDb);"

answered Oct 16, 2014 at 13:44

David Ansermot

6,1148 gold badges51 silver badges84 bronze badges

Collectives™ on Stack Overflow

Save mysql query into database

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related