spEditor – A PHP-based MySQL Stored Procedure Editor

spEditor is an online MySQL Stored Procedure Editor with that allows you to:

  • Create / Edit / Delete stored procedures easily online
  • Call stored procedures (with custom input parameters)

Why?

I’m currently working on a project that could really use some of MySQL’s stored procedures.  The problem?  I host my web sites through GoDaddy and they don’t offer direct database connectivity or SSH/telnet (unless you want to pay $$ for a dedicated server).  So, to write/edit stored procedures, I coded up this nifty little script that accomplishes just that – and does it pretty well.  I figured other people out there had the same problem…so here you go…

Keep in mind that this is the first release (I’m calling it a BETA), so if you find some bugs, please let me know – my email address is at the top of the script.

Requirements:

Installation:

Step 1: Download the Editor (download links below).
Step 2: Edit the following lines in the file:

//server configuration    
$server = “mysql_server_name”;   //server name
$user = “mysql_user_name”;   //server user name
$password = “mysql_password”;   //server password
$schema = “mysql_database”;   //database name
$use_deterministic = true;   //true | false – some shared hosts require
    // this to be true (i.e., GoDaddy)
//preferences – defaults are OK….
$enable_confirmation = true;   //true | false – controls the POP-UP box
    // (are you sure you want to…)
$show_query = false;   //true | false
    // true = show all queries
    // false = show query on errors only

Step 4: Place the file in a secure location (not publically accessible) on your web server.  Again, this file must be protected by a .htaccess file as a user with access to this script can really hose your database!

**Note: You may have to change the default extension from .php5 to .php (most shared hosts – like GoDaddy, etc. – require PHP5 scripts to have the .php5 extension, but your situation may vary…).

Step 5: Open the script in a web browser.

Download:

Version 0.91 BETA (download link)

Need some help using spEditor?  Justin Tubbs has written up a very nice tutorial here.

Change Log:

Version 0.91 BETA – Fixed a bug that allowed a maximum of 64 characters in the parameter list.  Fixed a bug related to improperly escaped strings. Confirm message will now appear if update failed and you change pages.

Version 0.9 BETA – First beta release.

23 Responses to spEditor – A PHP-based MySQL Stored Procedure Editor

  1. sango says:

    Tested you script today, looks good. A shame phpmyadmin isnt capable of SP. Maybe you could try and help the phpmyadmin guys with that issue.

  2. Hi, I am Byron from Ecuador, I have problems with sp and phpmyAdmin, I will try your tool. i HOPE it will help me.

    Byron.

  3. Johnny says:

    It’s working great for me! Thanks ! Maybe the database password should be passed as a GET parameter (and then stored in input hidden in forms) for a little embedded security…

  4. clloyd says:

    I’m glad it’s working for you Johnny. I thought about embedding some security, but with the constant update requirements (somebody discovers an exploit and then I have to patch it, etc.), I decided to just make it the user’s responsibility to protect the script by placing it in a password protected directory (by use of a .htaccess file). This is actually a very simple thing to do – Google ‘htaccess htpasswd’ and you will get a ton of hits

  5. William says:

    It works well. Thanks for your sharing. But what about MYSQL functions and triggers? It seems that some more functions are needed while delopying PHP systems through GoDaddy.

  6. clloyd says:

    You know, William, I want functions & triggers too!….but GoDaddy doesn’t offer them in their shared hosting (Linux) packages (which is what I developed this script for). I may add support for those in the furture though….

  7. Nate Mow says:

    Great script Chad! phpMyAdmin was driving me crazy on GoDaddy!

  8. Neil says:

    If you can’t get this to work you may need to change your php version in your hosting account. It defaults to version 4, which on my server, did not have mysqli compiled in. (details below post)

    Hopefully their version 5 configure didn’t leave this out. I’ll find out whenever their server finishes switching versions for my vhost.

    I have mysqli on my development box, and it does show up in the phpinfo() output on this server, but it doesn’t on my hosting acct’s phpinfo output.

    Here’s a link that describes how you switch versions:
    http://help.godaddy.com/topic/418/article/3937

    -Neil
    —-
    ‘./configure’ ‘–with-cgi’ ‘–enable-fastcgi’ ‘–with-config-file-path=/web/conf’ ‘–with-gd’ ‘–with-xml’ ‘–with-gettext’ ‘–with-zlib-dir=/usr/src/zlib’ ‘–disable-posix’ ‘–with-jpeg-dir=../jpeg-6b’ ‘–enable-gd-native-ttf’ ‘–enable-ftp’ ‘–with-freetype-dir=/usr’ ‘–with-freetype’ ‘–with-sybase=/usr/src/freetds’ ‘–with-curl=/usr/bin/curl’ ‘–with-dom’ ‘–enable-calendar’ ‘–enable-soap’ ‘–enable-bcmath’ ‘–with-zip’ ‘–with-openssl’ ‘–with-mcrypt’ ‘–with-mysql=/usr/local/mysql-5.0′

  9. Neil says:

    ….and the server switched versions. mysqli is in version 5.

    Hope this helps someone 8)

    -Neil

  10. Neil says:

    Script works great. I’m sort of a code formatting nut tho. The script will die during proc syntax check if you have your parameters set up like
    (
    param1 type,
    param2 type,
    param3 type
    )
    And cut and paste them directly from your editor to spedit.
    It doesn’t like the newlines.

    if you
    //handle newlines and crlfs in parameters from crazy people
    $parameterlist=preg_replace(‘/[\r\n]/e’,”,$parameterlist);
    before building sql it will handle this XD

    Your form displays the cleaned up stuff in a single line after submitting 8)

    Nice script man, keep plugging.

    -Neil

  11. Deepak Sharma says:

    Dear Clloyd,

    Tested your script today and found that the phpmyadmin people are missing such a powerful tool in there pack. Your script is really dam…. GOO….D. It can give very much relaxation to the developers to make there code very light and felexible. Although it is an beta version but its really nice. THANKS FOR THE SCRIPT.

  12. Brody says:

    Nice Script! I can’t wait for function, and trigger support!

  13. Tim says:

    With all the positive comments, I’m sure the error is in my php congifguration or something, but it doesn’t work for me. My error log says “PHP Fatal error: Trying to clone an uncloneable object of class mysqli in [hidden]/speditor.php on line 90″

    I don’t use mysqli, in my other apps, I use the regular, no OO mysql connection functions. phpMyAdmin works great for me though. Any ideas of what do I need to do to get this to work?

  14. clloyd says:

    Hi Tim –

    Here’s a few things you can try….

    On line 90, replace the “new” keyword with “@new” and see if that works.

    If that doesn’t work, change it back to “new” and add the following line just above line 90:

    @ini_set(‘zend.ze1_compatibility_mode’, 0);

    Chad

  15. kurt.maderal says:

    WOW!! very nice proc editor!! It really worked.!! Thanks…

  16. Mike says:

    Your script is great! Thanks.

    I was stuck by my website provider – they only allow phpMyAdmin updates to MySQL, *and* they do not support InnoDb. Therefore, stored procedures are the way to go.

    I really needed your script to get going. Thanks.

  17. Dennis Kuntz says:

    Excellent – thank you! Just FYI that I added:

    at the beginning because my configuration didn’t like that $_POST['flag'] wasn’t set :)

    You’ve been a huge help!

  18. Hello,

    I would like to thank you for saving me from even more frustration. I was pissed that phpMyAdmin couldn’t handle stored procedures.

    I’m actually so thankful that I’ve create a post about you on my blog.

    Thanks

  19. Almo says:

    Awesome. Thanks!

  20. Kgee says:

    This script is excellent. I am currently building a web framework with PhpMyAdmin as a database front-end, and without this script we would not be able to create large amounts of the sql procedures without having remote access to ssh. Finding this improved our production rate and security dramatically.

    I encourage you to keep tinkering with this software and expanding it. By the looks of the comments above, you have an appreciative user base.

    Thank you for your awesome contribution.

  21. Tony says:

    Great job on the script.. however, I’m having some difficulty. I added a SP and inserted this simple query and am receiving “Syntax Check……………..FAILED.”

    SQL I’m using:

    #This is placeholder code.
    #Simply replace this code with your own code.
    SELECT * FROM tbl_users

  22. Tony says:

    Ok.. nevermind.. forgot the semi-colon! :)

    GREAT SCRIPT!! Thanks!

  23. Riki says:

    Thanks Chad, you rock!!