Posted by Norman Peelman on 09/01/07 11:52

The Natural Philosopher wrote:
> Jerry Stuckle wrote:
>> The Natural Philosopher wrote:
>>> Jerry Stuckle wrote:
>>>> The Natural Philosopher wrote:
>>>>> ELINTPimp wrote:
>>>>>> On Aug 31, 2:18 pm, giloosh <giloos...@gmail.com> wrote:
>>>>>>> On Aug 30, 9:11 am, Jan Thomä <k...@insomnia-hq.de> wrote:
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>> Jerry Stuckle wrote:
>>>>>>>>> Jan Thomä wrote:
>>>>>>>>>> I always use the placeholder notation for doing SQL.
>>>>>>>>>> Concatening SQL
>>>>>>>>>> strings from input values is almost certainly a safe path to SQL
>>>>>>>>>> injection.
>>>>>>>>> Not if you properly cleanse your input. Ensure numeric values are
>>>>>>>>> really numeric, and string values are processed through
>>>>>>>>> mysql_real_escape_string(), for instance.
>>>>>>>> Thanks for the input, and yes I agree, you should definitely
>>>>>>>> cleanse your
>>>>>>>> input before feeding it to the database. My point was simply,
>>>>>>>> that when you
>>>>>>>> give this kind of work to a framework and always use the ?
>>>>>>>> notation, you
>>>>>>>> are safe from injection, even if you forget to check a single input
>>>>>>>> variable (which surely happens from time to time). Also you
>>>>>>>> don't have to
>>>>>>>> do the conversions to different formats manually, so you save a
>>>>>>>> bit of time
>>>>>>>> and effort.
>>>>>>>> Best regards,
>>>>>>>> Jan
>>>>>>>> --
>>>>>>>> _________________________________________________________________________
>>>>>>>>
>>>>>>>> insOMnia - We never sleep...http://www.insOMnia-hq.de
>>>>>>> thanks for the feedback...
>>>>>>> isn't is safe to just put quotes around the variable
>>>>>>> $id = $_POST['id']
>>>>>>>
>>>>>>> $q = "select * from table1 where id = '$id'";
>>>>>>>
>>>>>>> even if id holds none numeric characters, it's still safe... no?
>>>>>>
>>>>>> No. That introduces sql injection and cross site scripting
>>>>>> vulnerabilities. mysql_real_escape_strings, as previously suggested,
>>>>>> will help prevent against this.
>>>>>>
>>>>>
>>>>> Dunno what that means, but since Ive been working all day on this
>>>>> sort of stuff..
>>>>>
>>>>> 1/. $q = "select * from table1 where id = '".$id."'";
>>>>>
>>>>> is what you want. Note the order of quotes and dots.
>>>>>
>>>>> 2/. On my server - virgin php5 apache2 setup, all POST data gets
>>>>> escaped with backslashes into a format that is completely
>>>>> digestible by MySql. So no special processing is needed before
>>>>> inserting or updating data from $_POST[] derived variables.
>>>>>
>>>>
>>>> mysql_real_escape_string is much safer - it takes into consideration
>>>> the charset currently in use, for one thing. Also, being a MySQL
>>>> function, it knows what MySQL needs or uses.
>>>>
>>> Sager than what? Nothing? All characters that are entered in the
>>> fields make their way into the database unaltered. That's how I like it.
>>>
>>
>> Safer than using addslashes().
>
> I never mentioned addslashes

Well, you did say that all POST data was getting escaped with backslashes...

>>
>>> Ter is no nede to change anything in THAT area.
>>>
>>
>> No, not when you're ignorant about the realities, there isn't.
>>
> No when yu refuse to read anthing that is writtn.
>>>
>>>>> 3/. One coding bug showed up interestingly when I had done
>>>>>
>>>>> $new_id=mysql_insert_id;
>>>>>
>>>>> instead of
>>>>>
>>>>> $new_id=mysql_insert_id();
>>>>>
>>>>> The insert of what surprisinlgly was NOT a syntax error, but a
>>>>> string called "mysql_insert_id" into an integer field resulted in
>>>>> the value zero being put in. (Javascript gives you a NaN response
>>>>> when you try and do maths on a string like (1,000 * 1,000) = Not a
>>>>> Number ;-)).
>>>>>

.... and you should know that trying to use a string as a number will
result in 0 if there are no numbers in the string. So, not a MySQL error
or a PHP error but a programming error that would have been caught with
the proper error reporting on.

>>>>
>>>> You would have gotten an E_NOTICE if you had them turned on (which
>>>> you should have in development).
>>>
>>> You should not teach your grandmother to suck eggs.
>>>
>>
>> You shouldn't complain about not getting messages when you have those
>> very messages disabled.
> I didn't complain about no getting messages. You did.
>
>>
>>> I had my OWN debugging on. I looked at the echoed query strings and
>>> realised that what was going in was weird and didn't match what was
>>> in the database subseqeuntly, Simple innit?
>>>

MySQL doesn't alter the data it stores... would be very much of a
database if it did.


>>
>> Yes, and if you would have had the E_NOTICE turned on, you would have
>> gotten the message you bitched about not getting. You have no one to
>> blame but yourself.
>>
> I didn't bitch about anything. I made an error, I tested and checked and
> found the error, like I always do.
>
> The point of mentioning it was that it is what happens when yiu try and
> insert non numerical data nto a numerical feld. SQL is smart and des its
> best.

Had nothing to do with MySQL.

>
>>> Now thing about all the bugs that E_NOTICE would NOT catch, like
>>> having the data fields transposed so that the guys name goes in his
>>> street address and so on.
>>>
>>
>> That's not a bug. That's a programmer deficiency.
>>
> They are all programmer errors. My god it gets more obvious that you
> haven't written software profesoionally ever.
>

>>> Yu would have looked an seen no bugs, I would have looked three imes
>>> and double checked it and fund it. Because I wasn't relying on
>>> anything more than hard concentration and nitpicking over the details.
>>>
>>
>> And if you would have had E_NOTICE enabled, you would have known the
>> exact line number of the error the first time you executed the code.
>>
>>> Why do I get the feeling that you are someone who enjoys displaying a
>>> superfcial knowledge about programming, but doesn't actually do very
>>> much OF it in real world situations?
>>>
>>>
>>
>> Been programming for 40 years, asshole. Almost 20 different languages
>> over that time. And been a successful consultant for the last 17 years.
>>
>
> Bollocks.No one would employ you with that attitude.
>

Actually, it's the other way around with your programming practices.

>> I probably write more code in a week than you do in a year. At least
>> *good code*.
>>
>> Learn from your elders, asshole.
>>
>
> I am older than you then..
>
>>>>
>>>>> 4/. I did have an issue with redisplaying data that had come from a
>>>>> POST derived form..now normally I update the database, then read
>>>>> the data from the database back into the form: In this case I was
>>>>> testing 'failed to update, re-enter some data' and the backslashed
>>>>> stuff gave me issues with quotes and backslashes.
>>>>>
>>>>> Basically a variable that might be "Fred Bloggs" became \"Fred
>>>>> Bloggs\" after being POSTED. This went into mysql fine using the
>>>>> sort of query I outlined above. And checking using direct database
>>>>> access showed the data in *there* was in fact "Fred Bloggs"
>>>>>
>>>>> However if I wanted to reinsert that into e.g. text box as i.e.
>>>>> value="\"Fred Bloggs\"" it didn't work too well at all. Repeated
>>>>> updates left me with truncated data and/or multiple backlashes

Not sure why you would need to store double quotes around text in MySQL
but it's your application. This is a problem bewtween the quotes in your
strings and the quotes in your HTML.

>>>>>
>>>>
>>>> mysql_real_escape_string() solves that problem, also.
>>>>
>>>>> The key is as follows - in my particular apache php configs anyway
>>>>> - to do the following.
>>>>>
>>>>> Any POST data that needs to be inserted into input fields and the
>>>>> like -
>>>>> goes through this:-
>>>>>
>>>>> function sanitise($string)
>>>>> {
>>>>> $string=stripslashes($string); // remove any backslashes
>>>>> $string=htmlspecialchars($string); // turn oddities that HTML barfs
>>>>> // on into ampersand stuff
>>>>> return $string;
>>>>> }
>>>>
>>>> You shouldn't be doing this on data going into the database.
>>>
>>> If you read what I *said*, I specifically said that I *wasn't*. I was
>>> doing it on data coming from POSTed variables before applying to the
>>> BROWSER.
>>>
>>
>> No, you weren't at all clear about that.
>>
>
> No, you are simply too arrogant to read things properly.
>

and what part of 'POST data' implies that the data was from MySQL. I can
deduce what you mean but you need to get your terminology right. Data
coming out of a database is not POST data.


>>> What part of
>>>
>>> "all POST data gets
>>> escaped with backslashes into a format that is completely digestible
>>> by MySql. So no special processing is needed before inserting or
>>> updating data from $_POST[] derived variables."
>>>

and what is that format?

>>> Do you NOT understand?
>>>
>>
>> I understand when someone is clear with their words.
>>
>
> No you do not.
>
>>> You are heading for a plonk.
>>
>> Thank you. From assholes like you, it's an honor.
>>
>>>
>>>> htmlspecialchars() is a display function. Use it after reading out
>>>> of the database.
>>>>
>>>
>>> That is precisely what I said, dickhead.
>>>
>>
>> Then be clear about what you write, asshole.
>>
>>>> What happens, if for instance, you want to create a report to be
>>>> printed, say using PHP's CLI? Or even MySQL's CLI.
>>>>
>>>
>>> I'll write one. However, in this instance I don't.
>>>
>>
>> But the key is you don't know if you'll need one tomorrow, or six
>> months from now, or never. You've obviously never been on a project
>> which lasted more then four hours or you'd know that.
>>
>
> Actually I DO know exactly what I will need, since I am the one
> specifying this, and I know exactly what it needs to do.
>
>>> God, a whole post that merely confirms you didn't even read mine
>>> before jumping in to show off how fucking smart you are (not!).
>>>
>>
>> Bullshit. It just goes to show how unclear you were with your
>> posting, asshole.
>>
>>>
>>>>> If it's data derived from MySQL it comes out 'as written' with
>>>>> everything correct BUT that can confuse the browser..broswers don't
>>>>> like <> chars or '", but just running it through htmlspecialchars()
>>>>> is enough to sort THAT all out. Inverted commas and single quotes
>>>>> get the &blah; type treatment, and they display fine, and gets the
>>>>> correct stuff into the POST data when submitting the form - I know
>>>>> cos I sent three hours testing it.
>>>>>
>>>>> i.e. when I did a view source my input text box had
>>>>>
>>>>> value="&wahtever;Fred Bloggs&whatever;"
>>>>>
>>>>> inside it, but that showed up as "Fred Bloggs" IN the box, and
>>>>> POSTed up correctly as \"Fred Bloggs\" and went into SQL as "Fred
>>>>> Bloggs"
>>>>>
>>>>> So the correct php to generate THAT fragment would be
>>>>>
>>>>> echo ("value=\"".htmlspecialcars($my_value)."\"");
>>>>>
>>>>> And bugger., Ive just noted that the hardlinked copies of my
>>>>> library - one and the same file - have been busted into two
>>>>> different ones by my editor. It must be one of those that does a
>>>>> 'create tmp file, write it, unlink original, rename temp to
>>>>> original' that I so much hate... The quickest way to break SUNOS4
>>>>> linked config files was to use EMACS to edit them..
>>>>>
>>>>> man diff..;-)
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> }
>>>>>
>>>>>
>>>>
>>>>
>>
>> This is really funny. You're totally ignorant, but trying to give
>> advice. I'd recommend you stick your head back up your ass where it
>> normally is, and learn to program before giving advice.
>>
>> Because your advice is shit.
>>
>
> plonk,

[Back to original message]