Page 1 of 3
Does anyone have a code snippet for...
Posted: Wed Apr 11, 2007 12:18 pm
by Jumba
Hi,
I need a code to upload .sql files and execue them in a database.
Pretty much like the restore function in forum softwares, so I just upload a file and the queries are then executed.
Posted: Wed Apr 11, 2007 12:28 pm
by jayshields
phpMyAdmin is a great, free web application that has this function.
Posted: Wed Apr 11, 2007 12:37 pm
by Jumba
jayshields wrote:phpMyAdmin is a great, free web application that has this function.
yea, I know, but the problem is, im making a site, but its not for me, so I dont want that person to go in phpmyadmin, so if just uploading the sql file to update it (So all the database details are in a php file) then that person cant mess with my other databases.
Posted: Wed Apr 11, 2007 12:40 pm
by feyd
Have a look around for bigdump.
Posted: Wed Apr 11, 2007 12:41 pm
by Benjamin
feyd wrote:Have a look around for bigdump.
Yuck

Posted: Wed Apr 11, 2007 12:41 pm
by feyd
Yeah, it's not the best of names, but apparently it works..
Posted: Wed Apr 11, 2007 12:42 pm
by Begby
If you give user the ability to upload and execute SQL then you might as well give them full access to PHPMyAdmin. The will have the ability to execute any SQL they want including stuff that may affect your other databases.
You need to have user level security setup so that the user has their own MySQL password and login, then you can have the sql uploader run the sql with their credentials, or give them access to phpmyadmin with the mysql login/pass.
Posted: Wed Apr 11, 2007 12:45 pm
by Jumba
Begby wrote:If you give user the ability to upload and execute SQL then you might as well give them full access to PHPMyAdmin. The will have the ability to execute any SQL they want including stuff that may affect your other databases.
You need to have user level security setup so that the user has their own MySQL password and login, then you can have the sql uploader run the sql with their credentials, or give them access to phpmyadmin with the mysql login/pass.
Oh, well, I found this
viewtopic.php?t=66219
Didn't knwo you could do that, so I'm going to give it a try

Posted: Wed Apr 11, 2007 12:58 pm
by jayshields
To be honest I think it would be pretty easy to code a webpage which allows an .sql file upload and executes it on a given database logged in as a set user.
Posted: Wed Apr 11, 2007 1:01 pm
by Jumba
jayshields wrote:To be honest I think it would be pretty easy to code a webpage which allows an .sql file upload and executes it on a given database logged in as a set user.
I have one, but its not working well, I get an error, But I have no idea what's wrgon because when I upload it in phpmyadmin it works perfectly well, so I decided to just nevermind that script and look for another one
btw, this is the script :
http://lnl.yurx.com/insert.php (the sql file is
http://lnl.yurx.com/punten.sql)
I tried almost everything I can do, but nothing worked = /
btw, bigdump isn't working, I got an Internal Server Error. The host is probably blocking it I guess
Posted: Wed Apr 11, 2007 1:07 pm
by feyd
Posted: Wed Apr 11, 2007 1:19 pm
by jayshields
Jumba wrote:I have one, but its not working well, I get an error, But I have no idea what's wrgon because when I upload it in phpmyadmin it works perfectly well, so I decided to just nevermind that script and look for another one
You could post your existing code in here or post it in a new thread and I'll be happy to help you with it.
Posted: Wed Apr 11, 2007 1:23 pm
by Jumba
Ok, this is the code
Code: Select all
<?php
// Open Connection To mySQL.
// Replace 'username' and 'password' with appropiate login values for your server.
$mysqlLink = mysql_connect( 'localhost' , 'username' , 'password' );
// Select mySQL Database.
mysql_select_db( 'lnlyurx_punten' , $mysqlLink );
// Create Query To Create Table.
$sqlfile = "punten.sql";
$handle = fopen($sqlfile, "r");
$sql_query = fread($handle, filesize($sqlfile));
fclose($handle);
// Issue Query.
mysql_query($sql_query, $mysqlLink) or die(mysql_error());
// Close mySQL Connection.
mysql_close( $mysqlLink );
?>
This is the sql query
http://www.lnl.yurx.com/punten.sql
And I get this error
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '; # ---------- CREATE TABLE `Table1` ---------- CREATE TABLE `Table1` ( `ID` ' at line 6
And uhm, feyd, about that thread, I don't really get it, is that the fix to the error I get? ---^
Posted: Wed Apr 11, 2007 1:29 pm
by jayshields
You need to implement something to skip lines that start with a #, as they are comments.
It would probably be easier chop the file into segments which contain each seperate query and then iterate through them all whilst executing them. You will most definately need some regular expressions for this, as you will need to locate the semi-colons which indicate the end of each query.
Posted: Wed Apr 11, 2007 1:37 pm
by feyd
The code posted later in the thread by myself will break a .sql file into individual queries stripping out comments in the process.