Escaping Delimiters

(This was originally posted by Dan in a discussion about setting up my blog.)

This is safe:
$sql="update blogitems set status = '".$get['status']."'";
runSQL($sql,$GLOBALS['blogConn']);

This is safe
$sql="update blogitems set status = '".mysql_real_escape_string($_GET['status'])."'";
runSQL($sql,$GLOBALS['blogConn']);

This is NOT safe:
$sql="update blogitems set status = '".$_GET['status']."'";
runSQL($sql,$GLOBALS['blogConn']);


Anything in $_GET and $_POST has been sent to the program from a browser. There is nothing to stop a browser from sending embedded ' marks or other characters, I really am not sure which, that can trick a program into thinking it has hit a delimiter. Then, in the string, after the rogue delimiter, an attacker can put php commands and our programs would be none the wiser. This is called SQL Injection. Power to run php always must be exclusive to us.

There are times when it is safe to ignore this. For instance, no one can reach a program if it is in an ip-restricted directory. But if your blog is public and it uses querystrings, then the world can see you are accepting data at a certin url and they even know the field names.

If no one ever discovers blog edit, they will have no way to discover formhandler.php, which does all the updating to the database for the blog. Still,formhandler.php protects itself from data posted to it. The database interactions happen in hookups/003-formhandler/000-php-pre.php. If you look in there, you will see mysql_real_escape_string all over the place. The function mysql_esc() converts $_POST to $post the same way way $_GET has been converted to $get automatically on all pages.. I cannot think of a good reason to not do to $_POST what we do to $_GET, that is convert it to $post as part of every page load. I had it that way, encountered a complication and now it's done where I said.

 

Seeing mysql_real_escape_string makes me see, one of the things I need to do for your blog is convert hookups to mysqli instead of just mysql. All the database connections in hookups now are use the mysql extension to php. That extension is being phased out (aka deprecated). The new extension is called mysqli.

Using mysqli requires using mysqli_real_escape_string() instead of
mysqli_real_escape_string()


The new extension has improvements that we don't need but we have to upgrade to it or hookups will look dumb. Plus, php 7, which we will need to be running on relatively soon, does not allow mysql, only mysqli. I have worked out how to do the conversion. It is not hard. This is a perfect time to upgrade hookups. After we do it hookups, build your blog with it and release the free edition of hookups, I will need to upgrade hwc the same way.

NOTES
Most of what we do with SQL, and 100% of the sql everyone learning this software does is this.
$sql="select, update, insert or delete ... dataset where condition yadda yadda bing bong";
runSQL($sql, $GLOBALS['connection'], strval(__LINE__).__FILE__);
or
$array=getRows($sql, $GLOBALS['connection'], strval(__LINE__).__FILE__);
or
$value=getValue($sql, $GLOBALS['connection'], strval(__LINE__).__FILE__);

I am not certain, but my understanding none of that will change, except that the statements putting sql commands into $sql will have to use mysqli_real_escape_string.

The rest of the changes will be to the functions that create $GLOBALS['connection'] and to the functions runSQL($sql, $conn, $whereCalled), getRows($sql, $conn, $whereCalled), and getValue($sql, $conn, $whereCalled)