Posted by Rik Wasmus on 09/01/07 02:58

On Sat, 01 Sep 2007 00:36:33 +0200, The Natural Philosopher <a@b.c> 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.
>>>>> 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.

http://nl3.php.net/manual/en/ref.info.php#ini.magic-quotes-gpc
"all ' (single-quote), " (double quote), \ (backslash) and NUL's are
escaped with a backslash"

Which isn't all that needs to be escaped. For an example:
http://shiflett.org/blog/2006/jan/addslashes-versus-mysql-real-escape-string

Also, mysql_real_escape_string() will take care of NOT altering data when
inserting it, providing you don;t have any hidious mechanisms like magic
quotes enabled.


> Ter is no nede to change anything in THAT area.

There really is.

>>> 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 ;-)).
>>>
>> 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.
>
> I had my OWN debugging on.

Your OWN? You know better how to parse PHP then the people who make it?

> 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?
>
> 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.

Indeed, next to normal PHP error reporting you should
log/echo/write/report (to your liking) application errors. Which are quite
different from script errors.

> 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 spending way to much time... Either exceptions or trigger_error()s
would provide you with apt debugging info for your inner workings with the
same mechanism PHP uses. Why split them?

> 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?

Dunno. Because you're paranoid?

>>> 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.

It should not automagically happen. It should be a 'conscious descision'
of the code on insertion into a database.

>>> 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
>>>
>> 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;
>>> }

Odd combination. And as a general rule: stripslashes is seldomly a good
thing in code. Normally it indicates someone or some code fucked things up
earlier on.

>> 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.
>
> 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."
>
> Do you NOT understand?
>
> You are heading for a plonk.

You are heading for a major embarrassment as soon as you learn/realize how
things really work.

>> htmlspecialchars() is a display function. Use it after reading out of
>> the database.
>>
>
> That is precisely what I said, dickhead.

Damn, I was gonna post further. There's no reason for this kind of
language though. Let's sum up by saying you know some things, but the
details are clearly lost on you. You should refrain from giving people
advise or at least include a disclaimer you're just an amateur.
--
Rik Wasmus

My new ISP's newsserver sucks. Anyone recommend a good one? Paying for
quality is certainly an option.

[Back to original message]