Hi

I am really struggling here i want this to check for fobidden words in an effort to stop sql injection.
I cant seem to get it to work 🙁

function secureit()
    {
    global $items_check;
    $unallowed = array('href', 'www', 'UPDATE', 'INSERT', 'DELETE', 'SET', 'OFFSET', 'ORDER BY', 'union', 'UPDATE', 'DROP TABLE', 'CREATE TABLE');
    foreach($unallowed as $field) {
        if(stristr($items_check, $field) == TRUE) {
            $mess = 'NO Thanks "'.$items_check .'" is forbidden content!';
            return $action = "0";
            }
        }
    }

The idea is that it checks the $items_check against a list of banned words if it finds one it doesnt allow the remaining script to execute.

    This is a wrong approach. Instead of checking for forbidden words, use an appropriate escaping mechanism. For example, with the PDO extension or MySQLi extension, you might use prepared statements with parameter binding.

      i really dont understand wht you have said being honest is there any chance you can explain or point me in the right direction

        The basic idea is that SQL injection is more effectively eliminated by properly "escaping" any values that may have come from possibly unreliable data sources like user input. "Escaping" means that you precede quotes and newline chars and that sort of thing with a backslash. Read the docs on [man]mysqli_real_escape_string[/man] to get a clearer idea of what I'm talking about. If you properly enclose strings in quotes and remember to escape them, then you'll prevent some evil user from attacking php code like this:

        $sql = "SELECT * FROM users WHERE username=\"" . $_GET["u"] . "\"";

        by carefully concocting a query string that defines $_GET["u"] so that query ends up looking something like this:

        SELECT * FROM users WHERE username="some_wealthy_customer" LEFT JOIN credit_card_data cc ON cc.id=1

        In this case, the evil user supplied a query string that defined $_GET as this:

        some_wealthy_customer" LEFT JOIN credit_card_data cc ON cc.id=1

        And, depending on how the page is constructed, this may have allowed them to look at all the credit card numbers in a database one by one.

        The approach you use above will not work. What will work is if you escape $_GET before feeding it into your query. Escaping it will turn that one " into \" which, instead of exposing credit card data, will try to locate a user with the name some_wealthy_customer" LEFT JOIN credit_card_data cc ON cc.id=1 which will probably harmlessly fail.

        In practice, you should ALWAYS escape any string you are feeding into a query unless you are completely certain that its format is safe. In practice, escaping strings manually by using myqli_real_escape_string and concatenating your SQL together is tedious and unnecessary. If you use PDO to connect to your database as Laserlight and Bonesnap suggest, then you can use prepared statements to handle this for you. Your code will be safer and easier to read and you will appear more professional and you'll make more money and generally be a happier person.

          sneakyimp;11045209 wrote:

          "Escaping" means that you precede quotes and newline chars and that sort of thing with a backslash.

          It may mean that you escape some quotes with a backslash. More precisely it means that you escape whatever character needs escaping with whatever character is designated as the escaping character. In standard SQL, it's pretty straightforward. Double quotes " delimit identifiers and single quotes ' delimit string literals. The delimiter is also the escaping character. Thus

          SELECT * FROM "order" WHERE "double""quote""column" = 'single''quote''in''string';

          In mysql it may be that simple if the server is conf'ed to use ANSI QUOTES (my.cnf: sql-mode="ANSI"). But it may also be a bit more differentiated if mysql defaults are used

          SELECT * FROM `order` WHERE `back``tick``column` = 'single\'quote\'in\'string';

          This is the reason why mysqli_real_escape_string requires a connection to the server: the server settings affect both what needs to be escaped, as well as how to escape it.

          But the only definite all-round way of avoiding SQL injection is to use prepared statements. There is, or at least used to be, a way of circumventing mysqli_real_escape_string given some specific settings for server, connection and client. It would not necessarily be possible to achieve this either, depending on given settings and user privileges.

          The reason why prepared statements are safe in this regard is that they send the data and query separately.

          As a a side note: in case you try changing sql-mode through mysql conf file /etc/my.cnf: This will have no effect on (some? all?) sql-modes if the pre-generated $MYSQL_HOME/my.cnf is not first deleted or moved.

            Write a Reply...