I think this could work, but I'm not sure; does it detect all possible attacks and doesn't it break legal SQL?
Any help would really be appreciated
I would be more apt to allow a user to only create/drop databases in their own directories or to filter out the unsafe commands (ie: They can create, but have to contact you to drop a database or index).

Alternatively,You can force a (non-escaped) literal ';' to be the end of the statement under all circumstances.

As an aside, is there some reason that you want minimal constraints on what the user can do (especially if this is a production environment)? Use taint mode for input and validate that input for ;, followed by any of the SQL commands that you don't want the user to execute occurring anywhere in the line. That way (given that there is a very small set of commands) you can throw an exception (and log it when they occur.

UPDATE: Corrected for the real question :-(

I have this situation in which I have to create a table, but the user defines the column names and column definitions.
Anyway, I know now that the column name is handled by quote_identifier(..), but the definition, for example, a user might give me: VARCHAR(100) ); DROP DATABASE mysql; --'
Within your code, make sure "taint" mode is enabled (at least in the code block where the user input is occurring), then redefine (locally) $\ (The input record separator, newline by default) to be ";". Scan each input line for any of the "dangerous" commands such as DROP DATABASE (see the command reference for your flavor of SQL platform) and throw an exception if they should occur.

In reply to Re: Detect SQL injection by proceng
in thread Detect SQL injection by jeanluca

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.