• (cs) in reply to xtremezone
    xtremezone:
    ...so bare with me...
    Only if you're a hot mama.
  • Jason DeFontes (unregistered)

    Maybe this is what was intended?

    $sql = "select * from customers where "
       . "email_address = base64_decode('" . base64_encode($email_address) 
       . "') and password = base64_decode('" . base64_encode($password) . "')";
  • Daniel (unregistered)

    They forgot to block out SHOW TABLES, CREATE, ALTER, RENAME, and TRUNCATE. You could still have some fun in there, unless they validate input

  • John Rudy (unregistered) in reply to Victor

    That is true; they're not panacea. OTOH, in controlled environments they do solve a big part of the problem -- without resorting to disallowing large chunks of your potential user base.

    Just sayin.

  • iMalc (unregistered)

    null Mwhuhuhahaahaaaa!

    ah crap!

  • John Rudy (unregistered) in reply to John Rudy

    Double WTF ... I forgot to quote in my last message:

    Victor:
    TRWTF is these people believing parametrized queries makes you safe, there is always a moron who would use exec inside the stored procedure to build the query dynamically...

    Now, from experience don't say anything in a job interview, just go with the "official microsoft answer": parametrized queries (tm)

    This is what my "panacea" comment referred to.

  • (cs)

    No-one, and I mean no-one, which is to say "DELETE FROM PERSON;" has yet spotted The Real WTF here, which is:

    "therefore, the following words are restricted."

    Not forbidden, you understand; just "restricted." Not "restricted for use around the water-cooler," but presumably "restricted to use by top-notch executive types who want to enter them into their Web forms at any time of the day or night."

    I once had a boss who lost the company $29 million in a single weekend simply by being a raving incompetent maniac and ignoring all sane advice. She did so entirely through legitimate stupidity. I shudder to think what she'd make of powers like these.

  • MIQ (unregistered) in reply to JR
    JR:
    base64_decode(base64_encode(base64_decode(base64_encode($sensitive_string))))

    There's a even more secure method, but it uses more RAM:

    base64_decode(base64_decode(base64_encode(base64_encode($sensitive_string))))

  • (cs) in reply to Crabs
    Crabs:
    How about this for a trojan?
    Y'see, that's the other side of vocabulary black-lists on the Web.

    Please try to be more careful with your posts in future. It's all too easy to get into a rut (oops) and submit this sort of filth to a government website, at which point half the Feebs in Indiana will be breaking down your door to check out the nonexistent kiddy porn on your laptop...

  • EMan (unregistered) in reply to Brent Seidel
    Brent Seidel:
    EMan:
    I'm confused... It seems to me that base64 encoding the table entries would prevent SQL injection. Granted it does so in the weirdest and most roundabout way ever, but it would work, wouldn't it?

    This might be true, if the base64 encoded values were stored in the database. However, in the example shown the value is encoded and then immediately decoded, so you wind up stuffing the original value into the database.

    ROFL OK, I didn't catch that, but... just... WTF.

  • nitehawk (unregistered) in reply to Zap Brannigan

    Password to Air Shield: 12345

  • Matt (unregistered) in reply to JR

    As I understand it, this would be exponentially more effective -

    base64_decode(base64_decode(base64_encode(base64_encode($sensitive_string))))

  • (cs)

    Re. the third one: Hey, at least they made sure to tell the hackers exactly how their injection-avoidance works! I.e., at least they're not relying on security through obscurity.

  • Glow-in-the-dark (unregistered) in reply to Smash King
    Smash King:
    m0ffx:
    Also from Lincoln County Credit Union's site:
    Why can’t I use my physical keyboard to enter my password/PIN?

    The Password/PIN must now be entered using your mouse. The on-screen keyboard is used to prevent possible hackers from getting passwords while using special software designed to monitor keyboard strokes.

    Evidently they've never heard of the latest feature in that 'special software' - monitoring mouse clicks and taking screenshots.

    I'll play devil's advocate for a while, then. While it really is not failproof, this technique forces the <loose, vague term> hacker </loose, vague term> app to monitor the mouse and take screenshots. Those are a lot harder to mask from a knowing user, because they are more processor-hungry and use somewhat more disk space. Of course you shouldn't rely on the absence of these signals to assume you're in a secure environment.

    A while ago I had to use a friend's computer because my connection was down. He had an antivirus and he is not the sort of screw-up that plays around malware-ridden sites. Yet when I had to enter my mail password, I typed every keyboard character onto notepad and went back and forth copy-pasting them on my webmail password field, and not on the correct sequence. Sometimes I used the mouse, sometimes the keyboard. I would copy a string where only two separated characters mattered to me, then place the cursor between them and hold Del (single keystroke) until they were together. Anyone trying to recreate my password would have to log all my activity very carefully.

    .. or grab it on the way out via a man-in-the-browser attack, which requires a lot less attention as it can be automated. Nice try, though. Indeed, the aforementioned applet approach requires more recording and more manual labour, but if you've been following what has been happening with CAPTCHAS you may realise that that isn't quite the challenge it used to be.

  • Glow-in-the-dark (unregistered) in reply to Smash King
    Smash King:
    [..]

    As for my webmail password, it may be little value for you, but it is quite important to me. Important enough to make me spend 40 seconds on this little measure.

    Multiple personality disorders aside, I think the main point is that you're wasting 40 seconds on what is an illusion of security - all your elaborate (and IMHO quite ingenius) work is undone by the fact that it is eventually sent as one (1) string, thus perfect material for a man-in-the-browser grab.

    Now don't get too depressed, as for logon authentication it's a not so well kept secret that almost ALL eBanking approaches have weaknesses. They just differ per bank.

    OTP a la RSA tokens is right out, it has zero impact on transmission security, and is not man-in-the-middle proof. Challenge-response is better, but is again no match for MITM if your browser has been hijacked (not to mention that few know how to check) and especially MITB (browser) will undo all the good work. This is where that nice Java Applet MAY help, if it wasn't for the fact that you can mess with the browser..

    The only workable solution at present for both authentication and later authorization is mTAN: sending a message via a different method (known as out-of-band), for mTAN this is SMS. Challenges: SMS is not secure, and there is no SLA on SMS, especially if you're abroad, and in that case it's not exactly confidential either as SMS doesn't travel encrypted over inter-operator gateways. But it's a start, the exposure is contained by limiting SMS content.

    There is a solution, I know the above because I spent time analysing it all for private banking.. We are, however, by now HUGELY offtopic as the original subject was SQL cleaning..

  • (cs) in reply to halcyon1234
    halcyon1234:
    BUT what if instead of a direct SELECT, you ran a stored proc:
       Procedure SelectUsers (@Input varchar(255))
   AS
   @SQL = 'SELECT * FROM users WHERE name LIKE ' + @Input + ';'
   EXEC(@SQL)
    You have the same problem all over again. SELECT * FROM users WHERE name LIKE ''; DROP DATABASE-- ';'
    You deal with such idiots as wrote that sort of stored procedure by taking them out round the back of the bike sheds (or to a convenient janitorial space) and breaking their legs. It's by far the easiest way, and will save you a lot of trouble in the long run.

    (Either make your code robust against all inputs, or validate the inputs at a point where you have control and reject anything that's bad. It is exactly that simple. Oh, and remember that names can legitimately contain apostrophes; consider "Peter O'Toole"…)

  • James O'Boston (unregistered) in reply to tgape

    right.

    so it's all about YOU being GENEROUS to the users (customers)?

    it's crap practices like yours that make using web services such a delight.

  • (cs) in reply to php guy
    php guy:
    seems like most of the problem lies in selecting the username and password at the same time. with php i generally do:

    $username=$_POST['username']; $password=$_POST['password']; $password_md5=md5($password); $db_val=getPass($username); //getPass: select password from users where username=$username //the query can't be broken via injection

    if ($password_md5 == $db_val ) { $_SESSION['authenticated']=true; }

    My username is "Robert'); DROP TABLE users; --"

  • Mr.'; Drop Database -- (unregistered) in reply to php guy
    php guy:
    seems like most of the problem lies in selecting the username and password at the same time. with php i generally do:

    $username=$_POST['username']; $password=$_POST['password']; $password_md5=md5($password); $db_val=getPass($username); //getPass: select password from users where username=$username //the query can't be broken via injection

    if ($password_md5 == $db_val ) { $_SESSION['authenticated']=true; }

    This still has serious security flaws. You're still using MD5 (wtf) without salt (wtf!)

  • (cs) in reply to JR
    JR:
    And even more secure:

    base64_decode(base64_encode(base64_decode(base64_encode(base64_decode(base64_encode($sensitive_string))))))

    I think you're confused. It should be:

    base64_decode(base64_decode(base64_decode(base64_encode(base64_encode(base64_encode($sensitive_string))))

    --Tim

  • J D Eisenberg (unregistered)

    I guess I'm missing something here. Parameterized queries aren't that hard (at least in Perl DBI, they aren't), and they aren't even hard to explain. [I tell people to think of mail merge/fill-in-the-blank/MadLibs.] Are they really difficult to do in PHP?

  • (cs) in reply to J D Eisenberg
    J D Eisenberg:
    I guess I'm missing something here. Parameterized queries aren't that hard (at least in Perl DBI, they aren't), and they aren't even hard to explain. [I tell people to think of mail merge/fill-in-the-blank/MadLibs.] Are they really difficult to do in PHP?

    I don't know about PHP, but they're somewhere between "difficult" and "nightmare" in C, depending on your database.

  • (cs) in reply to nitehawk
    nitehawk:
    Password to Air Shield: 12345
    *refrains from making a luggage joke for fear of it being called "old hat" again...*

    np: Tied & Tickled Trio - Tamaghis (Aelita)

  • (cs) in reply to tgape
    tgape:
    The second example is a classic example of 'trying too hard'. For that code, any username with a ' character in it will cause problems, so just ban the lot of them. Of course, I tend to be more draconian, and also ban ", \, ,, ., and any other special character that seems interesting to me. (Actually, to be technical, I *allow* alphanumeric, underscores, and, depending on the system and field, maybe dashes and/or spaces. If it's for international use, I'll also allow unicode characters which are not part of the standard 7-bit ASCII set - but I'm only generous there because I haven't heard of any of those being exploitable.)
    Interesting technique. Personally, I prefer to just write secure code that can accept those characters without risk, but to each his own.
  • (cs) in reply to hagmanti
    hagmanti:
    It should be: 
    base64_decode(base64_decode(base64_decode(base64_encode(base64_encode(base64_encode($sensitive_string))))
    That's also known as Triple-BS encoding.
  • Mr.'; Drop Database -- (unregistered) in reply to J D Eisenberg
    J D Eisenberg:
    I guess I'm missing something here. Parameterized queries aren't that hard (at least in Perl DBI, they aren't), and they aren't even hard to explain. [I tell people to think of mail merge/fill-in-the-blank/MadLibs.] Are they really difficult to do in PHP?
    The main problem is that prepared statements are only available via the mysqli_stmt class, which was designed by fools. Check it out:
    $stmt = $db->prepare("SELECT name, dateOfSignup, creditLimit FROM Users WHERE creditLimit > ? AND name LIKE ?");
$stmt->bind_param("ii", $_GET["cl"], $_GET["name"]."%"); // Since MySQL does type inference, the "ii" parameter has no useful purpose.
$stmt->execute(); // You can't bind and execute in one step. Seriously.
$stmt->bind_result($name, $dateOfSignup, $creditLimit); // The *only* way to retrieve data is to give mysqli a bunch of references, into which it will read each row's values.
$results = array(); // So just to get just a simple array of data, which is what I'd normally do to separate my model and view, ...
while ($stmt->fetch()) // ... I must call this function repeatedly, making mysqli assign values to those references I specified earlier ...
    $results[] = array("name" => $name, "dateOfSignup" => $dateOfSignup, "creditLimit" => $creditLimit); // Then I manually put together the array that I really want.
    If you jump through the necessary hoops, it's possible to hide this crap behind a nice interface. At that point, it becomes easier to implement the nice interface in terms of real_escape_string.
  • Mr.'; Drop Database -- (unregistered)

    For reference, I looked up the Perl DBI library, and it seems to be more sensible, something like this:

    my $results = $db->selectall_arrayref("SELECT name, dateOfSignup, creditLimit FROM Users WHERE creditLimit > ? AND name LIKE ?", {Slice=>{}}, ($cl, $name."%"));

  • (cs) in reply to dkf
    dkf:
    ]You deal with such idiots as wrote that sort of stored procedure by taking them out round the back of the bike sheds (or to a convenient janitorial space) and breaking their legs. It's by far the easiest way, and will save you a lot of trouble in the long run.

    I'm shocked and offended that you would even suggest that. If I were to follow your advice, all I'd end up with is crap developers who are not only pumping out crap code, but who also get special compensation for being differently abled (unbroken boned challenged).

    No, your solution is TRWTF because it leaves them with the physical ability to continue coding. You are supposed to break their hands so they can't type, snap their toes so they can't foot-control, crush their larynx so they can't speech-to-text, and then for good measure, gouge out their eyes so they can't eye-track.

    After all, it's all about security through layers...

  • jk (unregistered) in reply to snoofle
    snoofle:
    Smash King:
    A while ago I had to use a friend's computer because my connection was down. He had an antivirus and he is not the sort of screw-up that plays around malware-ridden sites. Yet when I had to enter my mail password, I typed every keyboard character onto notepad and went back and forth copy-pasting them on my webmail password field, and not on the correct sequence. Sometimes I used the mouse, sometimes the keyboard. I would copy a string where only two separated characters mattered to me, then place the cursor between them and hold Del (single keystroke) until they were together. Anyone trying to recreate my password would have to log all my activity very carefully.
    With all due respect to your diligence, if you do this on a FRIENDs computer, what do you do when using the computer of a less trusted individual?

    telekinesis, I shouldn't wonder.

  • ollo (unregistered) in reply to JR
    And even more secure:

    base64_decode(base64_encode(base64_decode(base64_encode(base64_decode(base64_encode($sensitive_string))))))

    You've done it completely wrong! How could that possibly increase security if you always instantly decode what you encode? This would be correct:

    base64_decode(base64_decode(base64_decode(base64_encode(base64_encode(base64_encode($sensitive_string))))))

  • Justus (unregistered) in reply to Mr.'; Drop Database --

    In PHP, you could also just use PDO:

    $sth = $db->prepare('SELECT id FROM users WHERE username = ? AND password = ?');
$sth->execute(array('someuser', 'somepassword'));
$results = $sth->fetchAll(); // returns array of results

    This seems quite a bit cleaner than just using mysqli directly, and it's (theoretically) portable across databases.

  • IByte (unregistered) in reply to ThePants999
    ThePants999:
    Ahh, so Harland is still happy for you to use ALTER then.
    Or GRANT, for that matter...
  • AdT (unregistered) in reply to Victor
    Victor:
    TRWTF is these people believing parametrized queries makes you safe, there is always a moron who would use exec inside the stored procedure to build the query dynamically...

    Now, from experience don't say anything in a job interview, just go with the "official microsoft answer": parametrized queries (tm)

    TRWTF are people who don't know the difference between parameterized queries (a client side feature) and stored procedures (a server side feature) and write clueless rants as a consequence thereof.

  • carnegie (unregistered) in reply to JR

    You fool! Anyone well versed in Securitology knows that you want

    base64_decode(base64_decode(base64_decode(base64_encode(base64_encode(base64_encode($sensitive_string))))));

    and the coefficient of securitishness is multiplicative with the number of nests, not additive, so if the securitishness is x for the original implementation, it is now x^3.

    Doesn't anyone know ANYTHING?!?!?!

  • carnegie (unregistered) in reply to carnegie

    alright, clearly, I need to read each page, but I'm honestly touched that everyone including me decided to use exactly 3 nests.

  • Benjamin (unregistered) in reply to JR
    JR:
    I wonder why the coder responsible for the first example didn't see fit to increase the security in the same way that he created it. After all, the following routine would be twice as effective:

    base64_decode(base64_encode(base64_decode(base64_encode($sensitive_string))))

    And even more secure:

    base64_decode(base64_encode(base64_decode(base64_encode(base64_decode(base64_encode($sensitive_string))))))

    Genius stuff, surely.

    You may even make your security parametrizable:

    int securitivness = 50;//

    for (i=1; i< securitivness; i++){ $sensitive_string = base64_decode(base64_encode($sensitive_string)); } }

  • tego (unregistered)

    Parametrized queries are okay as long as the database libraries handling those don't have bugs. After having to occasionally work with MySQL I know from personal experience that that is not always true.

  • frustrati (unregistered) in reply to tgape
    tgape:
    The second example is a classic example of 'trying too hard'. For that code, any username with a ' character in it will cause problems, so just ban the lot of them. Of course, I tend to be more draconian, and also ban ", \, ,, ., and any other special character that seems interesting to me. (Actually, to be technical, I *allow* alphanumeric, underscores, and, depending on the system and field, maybe dashes and/or spaces. If it's for international use, I'll also allow unicode characters which are not part of the standard 7-bit ASCII set - but I'm only generous there because I haven't heard of any of those being exploitable.)
    We recognise you, TopCod3r. You cannot hide under a pseudonym that easily!
  • KenF (unregistered) in reply to m0ffx

    Evidently they have not heard of the real problem of someone looking over your shoulder. Far simpler.

  • · (unregistered) in reply to Glow-in-the-dark
    Glow-in-the-dark:
    Challenge-response is better, but is again no match for MITM if your browser has been hijacked (not to mention that few know how to check) and especially MITB (browser) will undo all the good work.
    No match? Challenge-response is specifically designed to prevent replay attacks, but a simple alteration makes it usable against MITM attacks too. Just connect the challenge to the action, providing authentication instead of only identification. Some user awareness is required, but it's the equivalent of not signing blank checks. That's why the bank clearly states the form of each challenge; at my bank, login challenges start with 9, new recipients use the account number as a challenge, and transfers use the amount.
  • (cs) in reply to Carnildo
    Carnildo:
    J D Eisenberg:
    I guess I'm missing something here. Parameterized queries aren't that hard (at least in Perl DBI, they aren't), and they aren't even hard to explain. [I tell people to think of mail merge/fill-in-the-blank/MadLibs.] Are they really difficult to do in PHP?

    I don't know about PHP, but they're somewhere between "difficult" and "nightmare" in C, depending on your database.

    This is absolutely true, if you have some sort of psychotic aversion to third-party tools (and no, I don't mean PRO*C or the like).

    Get a clue. Embed something like Python into your application, and do it right. Don't worry about the trivial database crap, because somebody else has sorted all that out for you. Just SWIG it and carry on as normal.

    Jeez, C programmers these days ...

  • Tirinoarim (unregistered)

    Hmm, looks like they also need to lookup the keyword "TRUNCATE TABLE"....

  • Wodin (unregistered) in reply to tgape
    tgape:
    If it's for international use, I'll also allow unicode characters which are not part of the standard 7-bit ASCII set - but I'm only generous there because I haven't heard of any of those being exploitable.)
    http://lwn.net/Articles/185813/
    A recent urgent update to PostgreSQL vividly demonstrates the problems with validating user input that are the foundation of SQL injection attacks. Widely used techniques to escape characters in user input can still allow SQL injection when coupled with multibyte character encodings. While this problem was first discovered in PostgreSQL, today's security fix announcement for MySQL indicates a similar problem there as well.
  • marcan (unregistered) in reply to JR

    But that just undoes the security each time!

    Here's the real secure version: base64_decode(base64_decode(base64_decode(base64_encode(base64_encode(base64_encode($sensitive_string))))))

  • umalut (unregistered) in reply to JR

    I store passwords in a database using strong encryption algorithm called tripleROT13: ROT13(ROT13(ROT13(password)))

    I think it's a good idea to increase the security of my database, therefore I think I shold consider switching to tripleROT13(tripleROT13(password)) encoding.

  • God Damned Anonymous (unregistered)

    Reading the idiots on this site literally makes my head hurt. Seriously. How many of you have programmed anything for money?

    THIS is how you stop SQL injection without wordlists, manual escaping, or other faggotry (PHP/MySQL):

    $mysqlsearch = mysql_real_escape_string($_POST['search']);

    $search=<<<SQL SELECT field1 FROM table WHERE field2 = '{$mysqlsearch}' SQL; //mysql query, fetch object, etc goes here

    THERE! That's all you do! That's all you need to do! The problem was solved by PHP, itself, long ago!

    Jesus!

  • conventio (unregistered) in reply to God Damned Anonymous
    God Damned Anonymous:
    THIS is how you stop SQL injection without wordlists, manual escaping, or other faggotry (PHP/MySQL)
    So by manually escaping (by using mysql_real_escape_string -function), you can avoid such unpleasantries as manual escaping. How nice.
  • TheShyte (unregistered)

    Only noobs use dynamic SQL

  • Mr.'; Drop Database -- (unregistered) in reply to conventio
    conventio:
    God Damned Anonymous:
    THIS is how you stop SQL injection without wordlists, manual escaping, or other faggotry (PHP/MySQL)
    So by manually escaping (by using mysql_real_escape_string -function), you can avoid such unpleasantries as manual escaping. How nice.
    I think it was a joke post. In this case the joke wasn't very good. If he'd suggested magic_quotes to avoid the need for manual escaping, that would be funny. Bonus points if he suggests enabling it with ini_set.
  • Your Name (unregistered) in reply to ThePants999

    Bet they'll like TRUNCATE too.

Leave a comment on “SQL Injection Protection * 3”

Log In or post as a guest

Replying to comment #:

Log Out

« Return to Article