[igordonin]

Am I protected from SQL Injection by using such a function *only* ?
Should I be using htmlentities also ?

PHP Code:

function validate_input($input) {

    # Insert the junk
    $junk = array("select", "insert", "update", "delete", "drop", ";", "--", "xp_", "*", "'", '"', "truncate", "schema.");
    
    # Searchs for invalid inputs in the string and replaces them with empties
    for ($junk_i = 0; $junk_i < count($junk); $junk_i++) {
        $input = str_ireplace($junk[$junk_i], "", $input); 
    }

    # Returns the validated string
    return $input;

} 


The "schema" array value is the Oracle Database schema name I use.

Thanks in advance for any advice.

[georgev]

What about comments such as

/*This
is a
comment */

And they keyword "EXEC" / "EXECUTE"?

[sco08y]

Even if that does protect you, it also destroys perfectly valid input.

Your input is either going to be a string or a non-string value that matches a regular expression. For example, integers should always match /[+-]\d+/. PHP probably has what you need built in.

Strings are easy to escape in SQL. I'm not a PHP guy, but it's as simple as:

"'" . str_replace("'", "''", $input) . "'"

Now you've got a quoted string. But don't take my word for it, read the spec.

    <character string literal>    ::= 
     [  <introducer> <character set specification>  ]  <quote>  [  <character representation> ...  ]  <quote>  [  {  <separator> ...  <quote>  [  <character representation> ...  ]  <quote>  }...  ]
    <quote>    ::=   '
    <character representation>    ::=   <nonquote character>  |  <quote symbol>
    <nonquote characte r>    ::=   !! See the Syntax rules
    <quote symbol>    ::=   <quote>  <quote>
    <separator> ::= { <comment> | <space> | <newline> }...

  Syntax Rules

         1) In a <character string literal> or <national character string
            literal>, the sequence:

              <quote> <character representation>... <quote>
              <separator>... <quote> <character representation>... <quote>

            is equivalent to the sequence

              <quote> <character representation>... <character representa-
              tion>... <quote>

            Note: The <character representation>s in the equivalent se-
            quence are in the same sequence and relative sequence as in the
            original <character string literal>.

Basically, it's saying two things: you can represent a quote with two quotes. (That's the quote symbol rule.) And that if you have two strings next to each other, like 'foo' 'bar' it's the same as saying 'foobar'. This is the way it works in C and other languages, too. (Well, there's more stuff about choosing character sets, but I can't imagine that would work very well...)

Also note that there's no reason that 'a string on two lines

doesn''t work just fine'. And having keywords inside the quote marks doesn't bother SQL in the least... so long as it's properly quoted, it gets turned into a character string literal token by the lexer.

[sco08y]

Originally Posted by igordonin

Should I be using htmlentities also ?

No. Stop trying to do shotgun programming. HTML entities are for HTML, not SQL.

[igordonin]

Thank you very much for your help.


Cheers

[Frunkie]

Originally Posted by sco08y

No. Stop trying to do shotgun programming. HTML entities are for HTML, not SQL.

Hang on there Mister.. There is absolutley nothing wrong with filtering input with htmlentities(). I believe what he is actually looking for though is htmlspecialchars().

[sco08y]

Originally Posted by fjm1967

Hang on there Mister.. There is absolutley nothing wrong with filtering input with htmlentities(). I believe what he is actually looking for though is htmlspecialchars().

Are you sure you don't mean filtering output?

[RBARAER]

If you are using Oracle as it seems to be, just use bind variables and you will avoid SQL injection .

http://asktom.oracle.com/pls/asktom/...23863706595353

For even better Oracle programming, use PLSQL stored procedures within packages and there you go .

If you want some more details, feel free to ask.

Regards,

rbaraer

[Genx]

You can really use bind variables and prepared statements with most db systems if you have PHP 5, the PDO extension and the necessary adapter installed ... unless it ships with the core install these days.

Probably the best way of preventing sql injection and dealing with sql in php in general.

[RBARAER]

Originally Posted by Genx

Probably the best way of preventing sql injection and dealing with sql in php in general.

Absolutely

[rajesh_r_r]

htmlspecialchars() is also one of the methods used to avoid SQL injection.