Re: [PHP] MySql Injection advice
Things I have used prepared statements for:
1. SELECT
2. UPDATE
3. INSERT
4. DELETE
5. Stored procedures
Things I am aware of that prepared statements are not capable of doing:
What have you read that prepared statements can't do? I've not heard
of anything, nor have I encountered anything, myself. And given that
I am prone to making errors, I like the fact that my work flow
prevents a mistake I make leading to an unnoticed vulnerability.
On Mon, Jul 13, 2009 at 5:00 PM, Michael A. Petersmpet...@mac.com wrote:
tedd wrote:
At 3:53 PM -0400 7/12/09, Paul M Foster wrote:
On Sun, Jul 12, 2009 at 09:07:45AM -0400, tedd wrote:
snip
As for prepared statements, I'm no authority on them, but from what
I've read they are not going to be something I'll be practicing
anytime soon.
Aside from Stuart's comments about slowness, what else have you read
that makes you discount the use of prepared statements? The PDO class
emphasizes that you're safe from SQL injection exploits, which seems a
big plus.
Paul
Paul:
As I said, I'm no authority. However as I have read, prepared statements
are for a limited set of instructions in MySQL. They can't be used for
everything. Why should I learn one way to do something that isn't universal
in the language?
They are useful for select, insert, and update queries, which are the three
most common types of queries in web applications and are most often used for
SQL injection.
I personally use the MDB2 database abstration layer. Here's how it's done -
$types = Array('integer','text');
$q = SELECT something,else FROM table WHERE id ? AND type=?
$sql = $mdb2-prepare($q,$types,MDB2_PREPARE_RESULT);
$args = Array($someinput,$someotherinput);
$rs = $sql-execute($args);
Here's the non prepared way
$sql = SELECT something,else FROM table WHERE id $someinput AND
type='$someotherinput'
$rs = $mdb2-query($sql);
The two are very similar syntax, just a few extra steps required for
prepared statements - and if the query is performed multiple times with
different arguments, you can re-use the prepared statement and don't have to
make it again.
The first has sql injection protection automatically for the two arguments,
the second requires that you first sanitize the two arguments - which is
where mysql_real_escape_string comes in - but as soon as you use that mysql
specific function, your code no longer is as easily portable to other
databases.
Prepared statements may be a minor performance hit but I suspect if it is
even noticable, you are at the edge of what your server can handle and
either need hardware update, infrastructure update (IE dedicated sql servers
and load balancing), or code optimization that probably will find bigger
issues than sql prepared statements.
Using a cache (IE APC or memcached) for commonly performed queries makes the
speed difference between the two only matter when the query isn't cached.
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySql Injection advice
On Tue, 2009-07-14 at 01:52 -0400, Andrew Ballard wrote: On Mon, Jul 13, 2009 at 4:18 PM, Haig Dedeyanhdede...@videotron.ca wrote: for the phone #'s, I'm using int as the data type storing each part of the phone # in its own cell, When it gets displayed, I add a dash in between each part of the phone #'s (country code-area code-1st set of digits-last set of digits) Cheers Haig I disagree. Telephone numbers are not actually numbers; they are sequences of numeric digits. Unlike IP addresses where 10.0.0.1 is equivalent to 010.000.000.001, leading zeros are significant; they are part of the data, not just padding to be inserted automatically by the database or by a formatting function in the presentation layer. When you validate an area code in the North American numbering plan, do you validate that it is a number between 1 and 999 or do you validate that it is a string of exactly 3 decimal-digit characters long? Expand that to international phone numbers, and the zeros become even more significant since you can't easily make assumptions about the length of various segments in a phone number. Sorry, but I just don't see any advantage to storing them as integers. Andrew Yeah, that makes sense. Last time I tried to store a phone number as a number was at school, when I realised that none of the leading zeros were preserved. On UK phone numbers, there's always a leading zero unless you're calling directory enquiries or emergency services! Also, IP addresses can be converted to IP numbers with the long2ip() function of PHP, which means you can store them as long ints and do normal number comparisons on them, great for matching an IP address to a range of 'valid' ones. Thanks Ash www.ashleysheridan.co.uk -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySql Injection advice
Eddie Drapkin wrote: Things I have used prepared statements for: 1. SELECT 2. UPDATE 3. INSERT 4. DELETE 5. Stored procedures Things I am aware of that prepared statements are not capable of doing: What have you read that prepared statements can't do? I've not heard of anything, nor have I encountered anything, myself. And given that I am prone to making errors, I like the fact that my work flow prevents a mistake I make leading to an unnoticed vulnerability. There was some stuff specified in the MySQL documentation. I *think* for example selection data resulting from a union of two tables with the AS TABLE modifier. I might be wrong about that. It was nothing I frequently do. I do have one really ugly query that does joins of one table and another table that actually is a union of two tables - but that particular query does not use any user provided data (it's part of my range map generation script) so I don't use prepared statements with it anyway. There's actually a bug in it (my huge query) though not significant, I'm planning to just break it up into several smaller queries and use php to do the hard work since that's easier to read and performance isn't an issue (run by server twice a month to generate a png image, never run by user). But yeah - the stuff in the documentation where prepared statements don't work is pretty obscure stuff. I believe MDB2 simulates prepared statements for databases without native prepared statements anyway. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySql Injection advice
On July 12, 2009 03:34:49 pm Haig Dedeyan wrote:
On Sat, 11 Jul 2009 21:26:36 -0400, Haig Dedeyan wrote:
On Sun, Jul 12, 2009 at 4:09 AM, Haig Dedeyan hdede...@videotron.ca
wrote:
mysql_query(INSERT INTO phonedir
(fname, lname) VALUES('$new_fname','$new_lname'))
or die(mysql_error());
I won;t be using 2x escapes but I just need to know if I should be seeing
the backslash in the dbase.
No, the backslashes should not be stored in the database.
They are only there to tell the database engine how to
separate data from the SQL syntax.
/Nisse
Ahhh. Thanks for the info.
Cheers
Haig
Re: [PHP] MySql Injection advice
On July 12, 2009 08:52:56 am Haig Dedeyan wrote:
At 6:39 PM -0400 7/11/09, Haig Dedeyan wrote:
[1]
mysql_query(INSERT INTO phonedir
(fname, lname) VALUES('$new_fname','$new_lname'))
or die(mysql_error());
or
[2]
mysql_query(INSERT INTO phonedir
(fname, lname)
VALUES('.mysql_real_escape_string($new_fname).','.mysql_real_escape_str
ing($new_lname).')) or die(mysql_error());
I always do [1] and NOT [2].
The reason for this is that when I clean and scrub data prior to
insertion, I may do more than pass it through a
mysql_real_escape_string() function.
For example, I may want to trim() it; or check if it's a valid email
address; or check if it's a number; or do any number of other checks
prior to insertion. I don't want to place all those functions into a
query, so why place one?
Lastly, I think [1] is easier to read than [2].
That's my take.
Cheers,
tedd
--
---
http://sperling.com http://ancientstones.com http://earthstones.com
Thanks. Yes I agree [1] is the better way to go.
After reading Nisse's response, it looks like the backslashes are never stored
in the table so all is good for me.
Thanks to everyone to helped out.
Cheers
Haig
Re: [PHP] MySql Injection advice
At 8:50 PM +0530 7/12/09, Zareef Ahmed wrote: On Sun, Jul 12, 2009 at 8:42 PM, tedd mailto:tedd.sperl...@gmail.comtedd.sperl...@gmail.com wrote: As with all communication, it's better to be clear than obtuse. Agree, but I believe obtuse word meaning is contextual and depends :) The word obtuse can mean difficult to understand or stupid depending upon context. Considering the context of my post was addressing difficulties in understanding your replies to postings, I think it proper to use the word in the manner I did. However, if you wish to take the other meaning, then that's your choice. Cheers, tedd -- --- http://sperling.com http://ancientstones.com http://earthstones.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySql Injection advice
At 3:53 PM -0400 7/12/09, Paul M Foster wrote: On Sun, Jul 12, 2009 at 09:07:45AM -0400, tedd wrote: snip As for prepared statements, I'm no authority on them, but from what I've read they are not going to be something I'll be practicing anytime soon. Aside from Stuart's comments about slowness, what else have you read that makes you discount the use of prepared statements? The PDO class emphasizes that you're safe from SQL injection exploits, which seems a big plus. Paul Paul: As I said, I'm no authority. However as I have read, prepared statements are for a limited set of instructions in MySQL. They can't be used for everything. Why should I learn one way to do something that isn't universal in the language? Additionally, I think the way I sanitize data is sufficient AND I understand it. *My* learning curve may introduce security problems that I am not willing to risk, at this moment. As I said, I have more than enough on my plate to digest -- including learning non-prepared statements in MySQL. Cheers, tedd -- --- http://sperling.com http://ancientstones.com http://earthstones.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySql Injection advice
On Monday 13 July 2009 14:31:09 tedd wrote: At 3:53 PM -0400 7/12/09, Paul M Foster wrote: On Sun, Jul 12, 2009 at 09:07:45AM -0400, tedd wrote: snip As for prepared statements, I'm no authority on them, but from what I've read they are not going to be something I'll be practicing anytime soon. Aside from Stuart's comments about slowness, what else have you read that makes you discount the use of prepared statements? The PDO class emphasizes that you're safe from SQL injection exploits, which seems a big plus. Paul Paul: As I said, I'm no authority. However as I have read, prepared statements are for a limited set of instructions in MySQL. They can't be used for everything. Why should I learn one way to do something that isn't universal in the language? Additionally, I think the way I sanitize data is sufficient AND I understand it. *My* learning curve may introduce security problems that I am not willing to risk, at this moment. As I said, I have more than enough on my plate to digest -- including learning non-prepared statements in MySQL. Cheers, tedd -- --- http://sperling.com http://ancientstones.com http://earthstones.com Generally speaking, what I have always done to avoid MySQL injection is to use mysql_real_escape_string() on all variables I'm chucking into the database. This won't avoid hacks that involve people trying to insert other types of code into your content, aka XSS, et al, though. What I do for cases like these is try to be as specific as possible when allowing users to enter data and try to sanitise it as much as possible. For example, a name field shouldn't contain anything other than letters, so you can write a regex for that. Phone number fields should only contain numbers, the odd + sign, and sometimes spaces and brackets if you're users are really fastidious with their input. Sometimes this isn't possible, as in the case of a lot of free-text entry boxes, so for those you should try and make some attempt to strip out tags or html encode the data prior to displaying it. Anyway, that's my take on it, and it seems to work for me, but I'm always welcome to know of other ways, as I'd prefer being told on the list than finding out the hard way! :p -- Thanks, Ash http://www.ashleysheridan.co.uk -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySql Injection advice
On July 13, 2009 09:48:54 am Haig Dedeyan wrote: On Monday 13 July 2009 14:31:09 tedd wrote: At 3:53 PM -0400 7/12/09, Paul M Foster wrote: On Sun, Jul 12, 2009 at 09:07:45AM -0400, tedd wrote: snip As for prepared statements, I'm no authority on them, but from what I've read they are not going to be something I'll be practicing anytime soon. Aside from Stuart's comments about slowness, what else have you read that makes you discount the use of prepared statements? The PDO class emphasizes that you're safe from SQL injection exploits, which seems a big plus. Paul Paul: As I said, I'm no authority. However as I have read, prepared statements are for a limited set of instructions in MySQL. They can't be used for everything. Why should I learn one way to do something that isn't universal in the language? Additionally, I think the way I sanitize data is sufficient AND I understand it. *My* learning curve may introduce security problems that I am not willing to risk, at this moment. As I said, I have more than enough on my plate to digest -- including learning non-prepared statements in MySQL. Cheers, tedd -- --- http://sperling.com http://ancientstones.com http://earthstones.com Generally speaking, what I have always done to avoid MySQL injection is to use mysql_real_escape_string() on all variables I'm chucking into the database. This won't avoid hacks that involve people trying to insert other types of code into your content, aka XSS, et al, though. What I do for cases like these is try to be as specific as possible when allowing users to enter data and try to sanitise it as much as possible. For example, a name field shouldn't contain anything other than letters, so you can write a regex for that. Phone number fields should only contain numbers, the odd + sign, and sometimes spaces and brackets if you're users are really fastidious with their input. Sometimes this isn't possible, as in the case of a lot of free-text entry boxes, so for those you should try and make some attempt to strip out tags or html encode the data prior to displaying it. Anyway, that's my take on it, and it seems to work for me, but I'm always welcome to know of other ways, as I'd prefer being told on the list than finding out the hard way! :p -- Thanks, Ash http://www.ashleysheridan.co.uk Hi Ashley, for the phone #'s, I'm using int as the data type storing each part of the phone # in its own cell, When it gets displayed, I add a dash in between each part of the phone #'s (country code-area code-1st set of digits-last set of digits) Cheers Haig
Re: [PHP] MySql Injection advice
On Mon, Jul 13, 2009 at 4:18 PM, Haig Dedeyanhdede...@videotron.ca wrote: On July 13, 2009 09:48:54 am Haig Dedeyan wrote: On Monday 13 July 2009 14:31:09 tedd wrote: At 3:53 PM -0400 7/12/09, Paul M Foster wrote: On Sun, Jul 12, 2009 at 09:07:45AM -0400, tedd wrote: snip As for prepared statements, I'm no authority on them, but from what I've read they are not going to be something I'll be practicing anytime soon. Aside from Stuart's comments about slowness, what else have you read that makes you discount the use of prepared statements? The PDO class emphasizes that you're safe from SQL injection exploits, which seems a big plus. Paul Paul: As I said, I'm no authority. However as I have read, prepared statements are for a limited set of instructions in MySQL. They can't be used for everything. Why should I learn one way to do something that isn't universal in the language? Additionally, I think the way I sanitize data is sufficient AND I understand it. *My* learning curve may introduce security problems that I am not willing to risk, at this moment. As I said, I have more than enough on my plate to digest -- including learning non-prepared statements in MySQL. Cheers, tedd -- --- http://sperling.com http://ancientstones.com http://earthstones.com Generally speaking, what I have always done to avoid MySQL injection is to use mysql_real_escape_string() on all variables I'm chucking into the database. This won't avoid hacks that involve people trying to insert other types of code into your content, aka XSS, et al, though. What I do for cases like these is try to be as specific as possible when allowing users to enter data and try to sanitise it as much as possible. For example, a name field shouldn't contain anything other than letters, so you can write a regex for that. Phone number fields should only contain numbers, the odd + sign, and sometimes spaces and brackets if you're users are really fastidious with their input. Sometimes this isn't possible, as in the case of a lot of free-text entry boxes, so for those you should try and make some attempt to strip out tags or html encode the data prior to displaying it. Anyway, that's my take on it, and it seems to work for me, but I'm always welcome to know of other ways, as I'd prefer being told on the list than finding out the hard way! :p -- Thanks, Ash http://www.ashleysheridan.co.uk Hi Ashley, for the phone #'s, I'm using int as the data type storing each part of the phone # in its own cell, When it gets displayed, I add a dash in between each part of the phone #'s (country code-area code-1st set of digits-last set of digits) Cheers Haig I too, store them as an int but then create a mask to show then user the correct format based on country -- Bastien Cat, the other other white meat -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySql Injection advice
On Mon, 2009-07-13 at 16:30 -0400, Bastien Koert wrote: On Mon, Jul 13, 2009 at 4:18 PM, Haig Dedeyanhdede...@videotron.ca wrote: On July 13, 2009 09:48:54 am Haig Dedeyan wrote: On Monday 13 July 2009 14:31:09 tedd wrote: At 3:53 PM -0400 7/12/09, Paul M Foster wrote: On Sun, Jul 12, 2009 at 09:07:45AM -0400, tedd wrote: snip As for prepared statements, I'm no authority on them, but from what I've read they are not going to be something I'll be practicing anytime soon. Aside from Stuart's comments about slowness, what else have you read that makes you discount the use of prepared statements? The PDO class emphasizes that you're safe from SQL injection exploits, which seems a big plus. Paul Paul: As I said, I'm no authority. However as I have read, prepared statements are for a limited set of instructions in MySQL. They can't be used for everything. Why should I learn one way to do something that isn't universal in the language? Additionally, I think the way I sanitize data is sufficient AND I understand it. *My* learning curve may introduce security problems that I am not willing to risk, at this moment. As I said, I have more than enough on my plate to digest -- including learning non-prepared statements in MySQL. Cheers, tedd -- --- http://sperling.com http://ancientstones.com http://earthstones.com Generally speaking, what I have always done to avoid MySQL injection is to use mysql_real_escape_string() on all variables I'm chucking into the database. This won't avoid hacks that involve people trying to insert other types of code into your content, aka XSS, et al, though. What I do for cases like these is try to be as specific as possible when allowing users to enter data and try to sanitise it as much as possible. For example, a name field shouldn't contain anything other than letters, so you can write a regex for that. Phone number fields should only contain numbers, the odd + sign, and sometimes spaces and brackets if you're users are really fastidious with their input. Sometimes this isn't possible, as in the case of a lot of free-text entry boxes, so for those you should try and make some attempt to strip out tags or html encode the data prior to displaying it. Anyway, that's my take on it, and it seems to work for me, but I'm always welcome to know of other ways, as I'd prefer being told on the list than finding out the hard way! :p -- Thanks, Ash http://www.ashleysheridan.co.uk Hi Ashley, for the phone #'s, I'm using int as the data type storing each part of the phone # in its own cell, When it gets displayed, I add a dash in between each part of the phone #'s (country code-area code-1st set of digits-last set of digits) Cheers Haig I too, store them as an int but then create a mask to show then user the correct format based on country -- Bastien Cat, the other other white meat What about other data? Is what I'm doing already sufficient do you think? Thanks Ash www.ashleysheridan.co.uk -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySql Injection advice
On Mon, Jul 13, 2009 at 5:52 PM, Ashley Sheridana...@ashleysheridan.co.uk wrote: On Mon, 2009-07-13 at 16:30 -0400, Bastien Koert wrote: On Mon, Jul 13, 2009 at 4:18 PM, Haig Dedeyanhdede...@videotron.ca wrote: On July 13, 2009 09:48:54 am Haig Dedeyan wrote: On Monday 13 July 2009 14:31:09 tedd wrote: At 3:53 PM -0400 7/12/09, Paul M Foster wrote: On Sun, Jul 12, 2009 at 09:07:45AM -0400, tedd wrote: snip As for prepared statements, I'm no authority on them, but from what I've read they are not going to be something I'll be practicing anytime soon. Aside from Stuart's comments about slowness, what else have you read that makes you discount the use of prepared statements? The PDO class emphasizes that you're safe from SQL injection exploits, which seems a big plus. Paul Paul: As I said, I'm no authority. However as I have read, prepared statements are for a limited set of instructions in MySQL. They can't be used for everything. Why should I learn one way to do something that isn't universal in the language? Additionally, I think the way I sanitize data is sufficient AND I understand it. *My* learning curve may introduce security problems that I am not willing to risk, at this moment. As I said, I have more than enough on my plate to digest -- including learning non-prepared statements in MySQL. Cheers, tedd -- --- http://sperling.com http://ancientstones.com http://earthstones.com Generally speaking, what I have always done to avoid MySQL injection is to use mysql_real_escape_string() on all variables I'm chucking into the database. This won't avoid hacks that involve people trying to insert other types of code into your content, aka XSS, et al, though. What I do for cases like these is try to be as specific as possible when allowing users to enter data and try to sanitise it as much as possible. For example, a name field shouldn't contain anything other than letters, so you can write a regex for that. Phone number fields should only contain numbers, the odd + sign, and sometimes spaces and brackets if you're users are really fastidious with their input. Sometimes this isn't possible, as in the case of a lot of free-text entry boxes, so for those you should try and make some attempt to strip out tags or html encode the data prior to displaying it. Anyway, that's my take on it, and it seems to work for me, but I'm always welcome to know of other ways, as I'd prefer being told on the list than finding out the hard way! :p -- Thanks, Ash http://www.ashleysheridan.co.uk Hi Ashley, for the phone #'s, I'm using int as the data type storing each part of the phone # in its own cell, When it gets displayed, I add a dash in between each part of the phone #'s (country code-area code-1st set of digits-last set of digits) Cheers Haig I too, store them as an int but then create a mask to show then user the correct format based on country -- Bastien Cat, the other other white meat What about other data? Is what I'm doing already sufficient do you think? Thanks Ash www.ashleysheridan.co.uk I think it all comes down to how you view the data and the validation routines. I keep those separate from the sanitation routines as my validations need to be more fluid (thinking about dates, life date( basically the last 100 years) vs event date (not in the past, but within the next 24 hours (depends on where the client locations are)) From a sanitation perspective, I don't have any issues with what you are doing and in many cases I do the same thing. I just have extra validation other factors of the data. -- Bastien Cat, the other other white meat -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySql Injection advice
tedd wrote:
At 3:53 PM -0400 7/12/09, Paul M Foster wrote:
On Sun, Jul 12, 2009 at 09:07:45AM -0400, tedd wrote:
snip
As for prepared statements, I'm no authority on them, but from what
I've read they are not going to be something I'll be practicing
anytime soon.
Aside from Stuart's comments about slowness, what else have you read
that makes you discount the use of prepared statements? The PDO class
emphasizes that you're safe from SQL injection exploits, which seems a
big plus.
Paul
Paul:
As I said, I'm no authority. However as I have read, prepared statements
are for a limited set of instructions in MySQL. They can't be used for
everything. Why should I learn one way to do something that isn't
universal in the language?
They are useful for select, insert, and update queries, which are the
three most common types of queries in web applications and are most
often used for SQL injection.
I personally use the MDB2 database abstration layer. Here's how it's done -
$types = Array('integer','text');
$q = SELECT something,else FROM table WHERE id ? AND type=?
$sql = $mdb2-prepare($q,$types,MDB2_PREPARE_RESULT);
$args = Array($someinput,$someotherinput);
$rs = $sql-execute($args);
Here's the non prepared way
$sql = SELECT something,else FROM table WHERE id $someinput AND
type='$someotherinput'
$rs = $mdb2-query($sql);
The two are very similar syntax, just a few extra steps required for
prepared statements - and if the query is performed multiple times with
different arguments, you can re-use the prepared statement and don't
have to make it again.
The first has sql injection protection automatically for the two
arguments, the second requires that you first sanitize the two arguments
- which is where mysql_real_escape_string comes in - but as soon as you
use that mysql specific function, your code no longer is as easily
portable to other databases.
Prepared statements may be a minor performance hit but I suspect if it
is even noticable, you are at the edge of what your server can handle
and either need hardware update, infrastructure update (IE dedicated sql
servers and load balancing), or code optimization that probably will
find bigger issues than sql prepared statements.
Using a cache (IE APC or memcached) for commonly performed queries makes
the speed difference between the two only matter when the query isn't
cached.
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySql Injection advice
On Mon, Jul 13, 2009 at 4:18 PM, Haig Dedeyanhdede...@videotron.ca wrote: for the phone #'s, I'm using int as the data type storing each part of the phone # in its own cell, When it gets displayed, I add a dash in between each part of the phone #'s (country code-area code-1st set of digits-last set of digits) Cheers Haig I disagree. Telephone numbers are not actually numbers; they are sequences of numeric digits. Unlike IP addresses where 10.0.0.1 is equivalent to 010.000.000.001, leading zeros are significant; they are part of the data, not just padding to be inserted automatically by the database or by a formatting function in the presentation layer. When you validate an area code in the North American numbering plan, do you validate that it is a number between 1 and 999 or do you validate that it is a string of exactly 3 decimal-digit characters long? Expand that to international phone numbers, and the zeros become even more significant since you can't easily make assumptions about the length of various segments in a phone number. Sorry, but I just don't see any advantage to storing them as integers. Andrew -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySql Injection advice
2009/7/12 Haig Dedeyan hdede...@videotron.ca
On July 11, 2009 08:21:34 pm Haig Dedeyan wrote:
On Sun, Jul 12, 2009 at 4:09 AM, Haig Dedeyan hdede...@videotron.ca
wrote:
On July 11, 2009 10:57:14 am Haig Dedeyan wrote:
At 10:12 PM -0400 7/10/09, Haig Dedeyan wrote:
[1]
$fname = mysql_real_escape_string($fname);
$lname = mysql_real_escape_string($lname);
$sql = UPDATE phonedir SET fname = '$fname',lname = '$lname' WHERE
id=$id; $result = mysql_query($sql);
echo mysql_error() . \n;
This will result in the addition of the slashes.
[2]
If I do the following, there are no slashes. Just wondering if I'm
on
the
right path with the 1st code set..
$sql = UPDATE phonedir SET fname =
'.mysql_real_escape_string($fname).',lname =
'.mysql_real_escape_string($lname).' WHERE id=$id;
$result = mysql_query($sql);
echo mysql_error() . \n;
Haig:
Interesting, I did not know that -- that sounds like a bug to me --
both should be the same.
However, I commonly do [1] and when I have to display the data to a
browser, then I use htmlentities() and stripslashes() before
displaying the data. That way names like O'Brian appear correctly --
else they appear 0\'Brian.
Now maybe I'm doing something wrong, but this way works for me. If
there is a better way, I would like to here it.
Cheers,
tedd
Thanks Tedd.
I did more testing and here's what I have found.
@PHPSter - magic quotes are off
Just entering simple data where an apostrophe is part of the data.
The following code is entering the slash but that's becuase I am
escaping
it
twice since mysql_num_rows is throwing an error if an apostrophe is in
its search:
1 -
$new_fname = mysql_real_escape_string($new_fname);
$new_lname = mysql_real_escape_string($new_lname);
$result = mysql_query(SELECT * FROM phonedir WHERE fname =
'$new_fname'
lname = '$new_lname');
$num_rows = mysql_num_rows($result);
The error message may be saying the mysql_num_rows is throwing an error
but
actual error is on mysql_query function level (Not a correct query)
if($num_rows 0)
{
echo $fname. .$lname. already exists;
}
else
{
mysql_query(INSERT INTO phonedir
(fname, lname)
VALUES('.mysql_real_escape_string($new_fname).','.mysql_real_escape_st
ring($new_lname).')) or die(mysql_error());
BTW twice escaping is not good
2 - If I do the same code above without the mysql_num_rows and no
escaping, the data doesn't get entered.
I think this is normal behaviour.
Welcome to hell of quotes :(
3 - If I do any of the 2 following sets of code where there is 1
instance
of
escaping, the data gets entered with the apostrophe but I don't see any
back
slash entered.
The part that I am concerned about is if I should be seeing the
backslash
entered without having to double escape,
Please see magic_quotes_runtime setting configuration...
http://www.php.net/manual/en/info.configuration.php#ini.magic-quotes-runtim
e
If it is enables it will automatically removed the slashes from any
external source including databases...
It was there to make the life of developer somewhat easier ()...
magic quotes things are deprecated and completely will be removed in PHP
6
$new_fname = mysql_real_escape_string($new_fname);
$new_lname = mysql_real_escape_string($new_lname);
$result = mysql_query(SELECT * FROM phonedir WHERE fname =
'$new_fname'
lname = '$new_lname');
$num_rows = mysql_num_rows($result);
if($num_rows 0)
{
echo $fname. .$lname. already exists;
}
else
{
mysql_query(INSERT INTO phonedir
(fname, lname) VALUES('$new_fname','$new_lname'))
or die(mysql_error());
or
mysql_query(INSERT INTO phonedir
(fname, lname)
VALUES('.mysql_real_escape_string($new_fname).','.mysql_real_escape_st
ring($new_lname).')) or die(mysql_error());
Thansk Zareef.
Magic quotes are off. This is what my php ini says:
; Magic quotes for incoming GET/POST/Cookie data.
magic_quotes_gpc = Off
; Magic quotes for runtime-generated data, e.g. data from SQL, from exec(),
etc.
magic_quotes_runtime = Off
; Use Sybase-style magic quotes (escape ' with '' instead of \').
magic_quotes_sybase = Off
This is fine, But just for final confirmation of actual values at the
runtime can you try to get the value of function get_magic_quotes_runtime
function in your script.
var_dump(get_magic_quotes_runtime);
possibility of using a different php.ini or modifying values of variables
at runtime is also there :)
I won;t be using 2x escapes but I just need to know if I should be seeing
the backslash in the dbase.
@Tedd - I will be looking into prepared statements
Re: [PHP] MySql Injection advice
At 6:39 PM -0400 7/11/09, Haig Dedeyan wrote:
[1]
mysql_query(INSERT INTO phonedir
(fname, lname) VALUES('$new_fname','$new_lname'))
or die(mysql_error());
or
[2]
mysql_query(INSERT INTO phonedir
(fname, lname)
VALUES('.mysql_real_escape_string($new_fname).','.mysql_real_escape_string($new_lname).'))
or die(mysql_error());
I always do [1] and NOT [2].
The reason for this is that when I clean and scrub data prior to
insertion, I may do more than pass it through a
mysql_real_escape_string() function.
For example, I may want to trim() it; or check if it's a valid email
address; or check if it's a number; or do any number of other checks
prior to insertion. I don't want to place all those functions into a
query, so why place one?
Lastly, I think [1] is easier to read than [2].
That's my take.
Cheers,
tedd
--
---
http://sperling.com http://ancientstones.com http://earthstones.com
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySql Injection advice
Hi gang:
I am top posting only to show that the following post makes no sense
as to who said what.
At what point here Zareef, did you think you were helping anyone? Or
is this more of your I'm going to do whatever I want attitude?
I would hate to review code with such lack of forethought and
consideration for others to read.
tedd
-- totally useless post follows:
At 5:51 AM +0530 7/12/09, Zareef Ahmed wrote:
On Sun, Jul 12, 2009 at 4:09 AM, Haig Dedeyan
mailto:hdede...@videotron.cahdede...@videotron.ca wrote:
On July 11, 2009 10:57:14 am Haig Dedeyan wrote:
At 10:12 PM -0400 7/10/09, Haig Dedeyan wrote:
[1]
$fname = mysql_real_escape_string($fname);
$lname = mysql_real_escape_string($lname);
$sql = UPDATE phonedir SET fname = '$fname',lname = '$lname' WHERE
id=$id; $result = mysql_query($sql);
echo mysql_error() . \n;
This will result in the addition of the slashes.
[2]
If I do the following, there are no slashes. Just wondering if I'm on the
right path with the 1st code set..
$sql = UPDATE phonedir SET fname =
'.mysql_real_escape_string($fname).',lname =
'.mysql_real_escape_string($lname).' WHERE id=$id;
$result = mysql_query($sql);
echo mysql_error() . \n;
Haig:
Interesting, I did not know that -- that sounds like a bug to me --
both should be the same.
However, I commonly do [1] and when I have to display the data to a
browser, then I use htmlentities() and stripslashes() before
displaying the data. That way names like O'Brian appear correctly --
else they appear 0\'Brian.
Now maybe I'm doing something wrong, but this way works for me. If
there is a better way, I would like to here it.
Cheers,
tedd
Thanks Tedd.
I did more testing and here's what I have found.
@PHPSter - magic quotes are off
Just entering simple data where an apostrophe is part of the data.
The following code is entering the slash but that's becuase I am escaping it
twice since mysql_num_rows is throwing an error if an apostrophe is in its
search:
1 -
$new_fname = mysql_real_escape_string($new_fname);
$new_lname = mysql_real_escape_string($new_lname);
$result = mysql_query(SELECT * FROM phonedir WHERE fname = '$new_fname'
lname = '$new_lname');
$num_rows = mysql_num_rows($result);
The error message may be saying the mysql_num_rows is throwing an
error but actual error is on mysql_query function level (Not a
correct query)
if($num_rows 0)
{
echo $fname. .$lname. already exists;
}
else
{
mysql_query(INSERT INTO phonedir
(fname, lname)
VALUES('.mysql_real_escape_string($new_fname).','.mysql_real_escape_string($new_lname).'))
or die(mysql_error());
BTW twice escaping is not good
2 - If I do the same code above without the mysql_num_rows and no escaping,
the data doesn't get entered.
I think this is normal behaviour.
Welcome to hell of quotes :(
3 - If I do any of the 2 following sets of code where there is 1 instance of
escaping, the data gets entered with the apostrophe but I don't see any back
slash entered.
The part that I am concerned about is if I should be seeing the backslash
entered without having to double escape,
Please see magic_quotes_runtime setting configuration...
http://www.php.net/manual/en/info.configuration.php#ini.magic-quotes-runtimehttp://www.php.net/manual/en/info.configuration.php#ini.magic-quotes-runtime
If it is enables it will automatically removed the slashes from any
external source including databases...
It was there to make the life of developer somewhat easier ()...
magic quotes things are deprecated and completely will be removed in PHP 6
$new_fname = mysql_real_escape_string($new_fname);
$new_lname = mysql_real_escape_string($new_lname);
$result = mysql_query(SELECT * FROM phonedir WHERE fname = '$new_fname'
lname = '$new_lname');
$num_rows = mysql_num_rows($result);
if($num_rows 0)
{
echo $fname. .$lname. already exists;
}
else
{
mysql_query(INSERT INTO phonedir
(fname, lname) VALUES('$new_fname','$new_lname'))
or die(mysql_error());
or
mysql_query(INSERT INTO phonedir
(fname, lname)
VALUES('.mysql_real_escape_string($new_fname).','.mysql_real_escape_string($new_lname).'))
or die(mysql_error());
--
Zareef Ahmed :: A PHP Developer in India ( Delhi )
Homepage :: http://www.zareef.nethttp://www.zareef.net
--
---
http://sperling.com http://ancientstones.com http://earthstones.com
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySql Injection advice
At 9:26 PM -0400 7/11/09, Haig Dedeyan wrote: @Tedd - I will be looking into prepared statements eventually but I still want to understand escaping. Haig: I'm not the one pushing the prepared statements, that's someone else. However, I can see how you might think that considering how people have responded to your post. Mailing list rules have purpose and if people don't follow them, then confusion does. As for prepared statements, I'm no authority on them, but from what I've read they are not going to be something I'll be practicing anytime soon. Cheers, tedd -- --- http://sperling.com http://ancientstones.com http://earthstones.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySql Injection advice
On Sun, Jul 12, 2009 at 6:32 PM, tedd tedd.sperl...@gmail.com wrote:
Hi gang:
I am top posting only to show that the following post makes no sense as to
who said what.
At what point here Zareef, did you think you were helping anyone? Or is
this more of your I'm going to do whatever I want attitude?
As far as I understand the main problem was the original poster was NOT
SEEING slashes in the output from database after successful insertion (
with only single use of mysql_real_escape_string), so I put my 50 cent on
the magic quotes runtime..
(My understanding of the actual problem may be wrong thus the suggestion in
context)
I am sorry if I offended anyone
I would hate to review code with such lack of forethought and consideration
for others to read.
tedd
-- totally useless post follows:
At 5:51 AM +0530 7/12/09, Zareef Ahmed wrote:
On Sun, Jul 12, 2009 at 4:09 AM, Haig Dedeyan mailto:
hdede...@videotron.cahdede...@videotron.ca wrote:
On July 11, 2009 10:57:14 am Haig Dedeyan wrote:
At 10:12 PM -0400 7/10/09, Haig Dedeyan wrote:
[1]
$fname = mysql_real_escape_string($fname);
$lname = mysql_real_escape_string($lname);
$sql = UPDATE phonedir SET fname = '$fname',lname = '$lname' WHERE
id=$id; $result = mysql_query($sql);
echo mysql_error() . \n;
This will result in the addition of the slashes.
[2]
If I do the following, there are no slashes. Just wondering if I'm on
the
right path with the 1st code set..
$sql = UPDATE phonedir SET fname =
'.mysql_real_escape_string($fname).',lname =
'.mysql_real_escape_string($lname).' WHERE id=$id;
$result = mysql_query($sql);
echo mysql_error() . \n;
Haig:
Interesting, I did not know that -- that sounds like a bug to me --
both should be the same.
However, I commonly do [1] and when I have to display the data to a
browser, then I use htmlentities() and stripslashes() before
displaying the data. That way names like O'Brian appear correctly --
else they appear 0\'Brian.
Now maybe I'm doing something wrong, but this way works for me. If
there is a better way, I would like to here it.
Cheers,
tedd
Thanks Tedd.
I did more testing and here's what I have found.
@PHPSter - magic quotes are off
Just entering simple data where an apostrophe is part of the data.
The following code is entering the slash but that's becuase I am escaping
it
twice since mysql_num_rows is throwing an error if an apostrophe is in its
search:
1 -
$new_fname = mysql_real_escape_string($new_fname);
$new_lname = mysql_real_escape_string($new_lname);
$result = mysql_query(SELECT * FROM phonedir WHERE fname = '$new_fname'
lname = '$new_lname');
$num_rows = mysql_num_rows($result);
The error message may be saying the mysql_num_rows is throwing an error
but actual error is on mysql_query function level (Not a correct query)
if($num_rows 0)
{
echo $fname. .$lname. already exists;
}
else
{
mysql_query(INSERT INTO phonedir
(fname, lname)
VALUES('.mysql_real_escape_string($new_fname).','.mysql_real_escape_string($new_lname).'))
or die(mysql_error());
BTW twice escaping is not good
2 - If I do the same code above without the mysql_num_rows and no
escaping,
the data doesn't get entered.
I think this is normal behaviour.
Welcome to hell of quotes :(
3 - If I do any of the 2 following sets of code where there is 1 instance
of
escaping, the data gets entered with the apostrophe but I don't see any
back
slash entered.
The part that I am concerned about is if I should be seeing the backslash
entered without having to double escape,
Please see magic_quotes_runtime setting configuration...
http://www.php.net/manual/en/info.configuration.php#ini.magic-quotes-runtime
http://www.php.net/manual/en/info.configuration.php#ini.magic-quotes-runtime
If it is enables it will automatically removed the slashes from any
external source including databases...
It was there to make the life of developer somewhat easier ()...
magic quotes things are deprecated and completely will be removed in PHP 6
$new_fname = mysql_real_escape_string($new_fname);
$new_lname = mysql_real_escape_string($new_lname);
$result = mysql_query(SELECT * FROM phonedir WHERE fname = '$new_fname'
lname = '$new_lname');
$num_rows = mysql_num_rows($result);
if($num_rows 0)
{
echo $fname. .$lname. already exists;
}
else
{
mysql_query(INSERT INTO phonedir
(fname, lname) VALUES('$new_fname','$new_lname'))
or die(mysql_error());
or
mysql_query(INSERT INTO phonedir
(fname, lname)
VALUES('.mysql_real_escape_string($new_fname).','.mysql_real_escape_string($new_lname).'))
or die(mysql_error());
--
Zareef Ahmed :: A PHP Developer in India ( Delhi )
Homepage :: http://www.zareef.nethttp://www.zareef.net
--
---
http://sperling.com http://ancientstones.com
Re: [PHP] MySql Injection advice
At 8:24 PM +0530 7/12/09, Zareef Ahmed wrote: On Sun, Jul 12, 2009 at 6:32 PM, tedd mailto:tedd.sperl...@gmail.comtedd.sperl...@gmail.com wrote: Hi gang: I am top posting only to show that the following post makes no sense as to who said what. At what point here Zareef, did you think you were helping anyone? Or is this more of your I'm going to do whatever I want attitude? As far as I understand the main problem was the original poster was NOT SEEING slashes in the output from database after successful insertion ( with only single use of mysql_real_escape_string), so I put my 50 cent on the magic quotes runtime.. (My understanding of the actual problem may be wrong thus the suggestion in context) I am sorry if I offended anyone Zareef: You are missing the point. It's not an issue of offending anyone but rather if someone is going to contribute, it makes sense to be clear as to what you are contributing -- else -- it just becomes noise. When you are reviewing a long post and then inject your comments within it, while it may make sense to you, it may not make sense to others. As with all communication, it's better to be clear than obtuse. Cheers, tedd -- --- http://sperling.com http://ancientstones.com http://earthstones.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySql Injection advice
On Sun, Jul 12, 2009 at 8:42 PM, tedd tedd.sperl...@gmail.com wrote: At 8:24 PM +0530 7/12/09, Zareef Ahmed wrote: On Sun, Jul 12, 2009 at 6:32 PM, tedd mailto:tedd.sperl...@gmail.com tedd.sperl...@gmail.com wrote: Hi gang: I am top posting only to show that the following post makes no sense as to who said what. At what point here Zareef, did you think you were helping anyone? Or is this more of your I'm going to do whatever I want attitude? As far as I understand the main problem was the original poster was NOT SEEING slashes in the output from database after successful insertion ( with only single use of mysql_real_escape_string), so I put my 50 cent on the magic quotes runtime.. (My understanding of the actual problem may be wrong thus the suggestion in context) I am sorry if I offended anyone Zareef: You are missing the point. It's not an issue of offending anyone but rather if someone is going to contribute, it makes sense to be clear as to what you are contributing -- else -- it just becomes noise. 100% Agree When you are reviewing a long post and then inject your comments within it, while it may make sense to you, it may not make sense to others. Yes injecting comments within a long post can lead to many misunderstanding about the purpose of suggestion. As with all communication, it's better to be clear than obtuse. Agree, but I believe obtuse word meaning is contextual and depends :) This is my last post in this thread. Cheers, tedd -- --- http://sperling.com http://ancientstones.com http://earthstones.com -- Zareef Ahmed :: A PHP Developer in India ( Delhi ) Homepage :: http://www.zareef.net
Re: [PHP] MySql Injection advice
On Sat, 11 Jul 2009 21:26:36 -0400, Haig Dedeyan wrote:
On Sun, Jul 12, 2009 at 4:09 AM, Haig Dedeyan hdede...@videotron.ca wrote:
mysql_query(INSERT INTO phonedir
(fname, lname) VALUES('$new_fname','$new_lname'))
or die(mysql_error());
I won;t be using 2x escapes but I just need to know if I should be seeing the
backslash in the dbase.
No, the backslashes should not be stored in the database.
They are only there to tell the database engine how to
separate data from the SQL syntax.
/Nisse
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySql Injection advice
On Sun, Jul 12, 2009 at 09:07:45AM -0400, tedd wrote: snip As for prepared statements, I'm no authority on them, but from what I've read they are not going to be something I'll be practicing anytime soon. Aside from Stuart's comments about slowness, what else have you read that makes you discount the use of prepared statements? The PDO class emphasizes that you're safe from SQL injection exploits, which seems a big plus. Paul -- Paul M. Foster -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySql Injection advice
On Jul 10, 2009, at 10:12 PM, Haig Dedeyan hdede...@videotron.ca wrote: Hi everyone, I'm starting to experiment with an edit form and I am seeing the following behaviour: $fname = mysql_real_escape_string($fname); $lname = mysql_real_escape_string($lname); $sql = UPDATE phonedir SET fname = '$fname',lname = '$lname' WHERE id=$id; $result = mysql_query($sql); echo mysql_error() . \n; This will result in the addition of the slashes. If I do the following, there are no slashes. Just wondering if I'm on the right path with the 1st code set.. $sql = UPDATE phonedir SET fname = '.mysql_real_escape_string($fname).',lname = '.mysql_real_escape_string($lname).' WHERE id=$id; $result = mysql_query($sql); echo mysql_error() . \n; Cheers Haig Check that magic_quotes are turned off. Bastien Sent from my iPod -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySql Injection advice
At 10:12 PM -0400 7/10/09, Haig Dedeyan wrote: [1] $fname = mysql_real_escape_string($fname); $lname = mysql_real_escape_string($lname); $sql = UPDATE phonedir SET fname = '$fname',lname = '$lname' WHERE id=$id; $result = mysql_query($sql); echo mysql_error() . \n; This will result in the addition of the slashes. [2] If I do the following, there are no slashes. Just wondering if I'm on the right path with the 1st code set.. $sql = UPDATE phonedir SET fname = '.mysql_real_escape_string($fname).',lname = '.mysql_real_escape_string($lname).' WHERE id=$id; $result = mysql_query($sql); echo mysql_error() . \n; Haig: Interesting, I did not know that -- that sounds like a bug to me -- both should be the same. However, I commonly do [1] and when I have to display the data to a browser, then I use htmlentities() and stripslashes() before displaying the data. That way names like O'Brian appear correctly -- else they appear 0\'Brian. Now maybe I'm doing something wrong, but this way works for me. If there is a better way, I would like to here it. Cheers, tedd -- --- http://sperling.com http://ancientstones.com http://earthstones.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySql Injection advice
On July 11, 2009 10:57:14 am Haig Dedeyan wrote:
At 10:12 PM -0400 7/10/09, Haig Dedeyan wrote:
[1]
$fname = mysql_real_escape_string($fname);
$lname = mysql_real_escape_string($lname);
$sql = UPDATE phonedir SET fname = '$fname',lname = '$lname' WHERE
id=$id; $result = mysql_query($sql);
echo mysql_error() . \n;
This will result in the addition of the slashes.
[2]
If I do the following, there are no slashes. Just wondering if I'm on the
right path with the 1st code set..
$sql = UPDATE phonedir SET fname =
'.mysql_real_escape_string($fname).',lname =
'.mysql_real_escape_string($lname).' WHERE id=$id;
$result = mysql_query($sql);
echo mysql_error() . \n;
Haig:
Interesting, I did not know that -- that sounds like a bug to me --
both should be the same.
However, I commonly do [1] and when I have to display the data to a
browser, then I use htmlentities() and stripslashes() before
displaying the data. That way names like O'Brian appear correctly --
else they appear 0\'Brian.
Now maybe I'm doing something wrong, but this way works for me. If
there is a better way, I would like to here it.
Cheers,
tedd
Thanks Tedd.
I did more testing and here's what I have found.
@PHPSter - magic quotes are off
Just entering simple data where an apostrophe is part of the data.
The following code is entering the slash but that's becuase I am escaping it
twice since mysql_num_rows is throwing an error if an apostrophe is in its
search:
1 -
$new_fname = mysql_real_escape_string($new_fname);
$new_lname = mysql_real_escape_string($new_lname);
$result = mysql_query(SELECT * FROM phonedir WHERE fname = '$new_fname'
lname = '$new_lname');
$num_rows = mysql_num_rows($result);
if($num_rows 0)
{
echo $fname. .$lname. already exists;
}
else
{
mysql_query(INSERT INTO phonedir
(fname, lname)
VALUES('.mysql_real_escape_string($new_fname).','.mysql_real_escape_string($new_lname).'))
or die(mysql_error());
2 - If I do the same code above without the mysql_num_rows and no escaping,
the data doesn't get entered.
I think this is normal behaviour.
3 - If I do any of the 2 following sets of code where there is 1 instance of
escaping, the data gets entered with the apostrophe but I don't see any back
slash entered.
The part that I am concerned about is if I should be seeing the backslash
entered without having to double escape,
$new_fname = mysql_real_escape_string($new_fname);
$new_lname = mysql_real_escape_string($new_lname);
$result = mysql_query(SELECT * FROM phonedir WHERE fname = '$new_fname'
lname = '$new_lname');
$num_rows = mysql_num_rows($result);
if($num_rows 0)
{
echo $fname. .$lname. already exists;
}
else
{
mysql_query(INSERT INTO phonedir
(fname, lname) VALUES('$new_fname','$new_lname'))
or die(mysql_error());
or
mysql_query(INSERT INTO phonedir
(fname, lname)
VALUES('.mysql_real_escape_string($new_fname).','.mysql_real_escape_string($new_lname).'))
or die(mysql_error());
Re: [PHP] MySql Injection advice
On Sat, Jul 11, 2009 at 6:39 PM, Haig Dedeyanhdede...@videotron.ca wrote:
On July 11, 2009 10:57:14 am Haig Dedeyan wrote:
At 10:12 PM -0400 7/10/09, Haig Dedeyan wrote:
[1]
$fname = mysql_real_escape_string($fname);
$lname = mysql_real_escape_string($lname);
$sql = UPDATE phonedir SET fname = '$fname',lname = '$lname' WHERE
id=$id; $result = mysql_query($sql);
echo mysql_error() . \n;
This will result in the addition of the slashes.
[2]
If I do the following, there are no slashes. Just wondering if I'm on the
right path with the 1st code set..
$sql = UPDATE phonedir SET fname =
'.mysql_real_escape_string($fname).',lname =
'.mysql_real_escape_string($lname).' WHERE id=$id;
$result = mysql_query($sql);
echo mysql_error() . \n;
Haig:
Interesting, I did not know that -- that sounds like a bug to me --
both should be the same.
However, I commonly do [1] and when I have to display the data to a
browser, then I use htmlentities() and stripslashes() before
displaying the data. That way names like O'Brian appear correctly --
else they appear 0\'Brian.
Now maybe I'm doing something wrong, but this way works for me. If
there is a better way, I would like to here it.
Cheers,
tedd
Thanks Tedd.
I did more testing and here's what I have found.
@PHPSter - magic quotes are off
Just entering simple data where an apostrophe is part of the data.
The following code is entering the slash but that's becuase I am escaping it
twice since mysql_num_rows is throwing an error if an apostrophe is in its
search:
1 -
$new_fname = mysql_real_escape_string($new_fname);
$new_lname = mysql_real_escape_string($new_lname);
$result = mysql_query(SELECT * FROM phonedir WHERE fname = '$new_fname'
lname = '$new_lname');
$num_rows = mysql_num_rows($result);
if($num_rows 0)
{
echo $fname. .$lname. already exists;
}
else
{
mysql_query(INSERT INTO phonedir
(fname, lname)
VALUES('.mysql_real_escape_string($new_fname).','.mysql_real_escape_string($new_lname).'))
or die(mysql_error());
2 - If I do the same code above without the mysql_num_rows and no escaping,
the data doesn't get entered.
I think this is normal behaviour.
3 - If I do any of the 2 following sets of code where there is 1 instance of
escaping, the data gets entered with the apostrophe but I don't see any back
slash entered.
The part that I am concerned about is if I should be seeing the backslash
entered without having to double escape,
$new_fname = mysql_real_escape_string($new_fname);
$new_lname = mysql_real_escape_string($new_lname);
$result = mysql_query(SELECT * FROM phonedir WHERE fname = '$new_fname'
lname = '$new_lname');
$num_rows = mysql_num_rows($result);
if($num_rows 0)
{
echo $fname. .$lname. already exists;
}
else
{
mysql_query(INSERT INTO phonedir
(fname, lname) VALUES('$new_fname','$new_lname'))
or die(mysql_error());
or
mysql_query(INSERT INTO phonedir
(fname, lname)
VALUES('.mysql_real_escape_string($new_fname).','.mysql_real_escape_string($new_lname).'))
or die(mysql_error());
No offense or anything, but all of this work you've done is
immediately mode obsolete the second you switch to prepared
statements. They're easier to use and more secure, as well as making
code more readable. I don't understand why it's so hard for them to
catch on among PHP developers when they're so popular in other
languages.
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySql Injection advice
2009/7/12 Eddie Drapkin oorza...@gmail.com: No offense or anything, but all of this work you've done is immediately mode obsolete the second you switch to prepared statements. They're easier to use and more secure, as well as making code more readable. I don't understand why it's so hard for them to catch on among PHP developers when they're so popular in other languages. They are also a *lot* slower for statements you're only going to execute once as they involve two round trips to the DB server instead of one. If your DB is local and not very heavily loaded then you probably won't notice this, but for those of us working on sites with substantial traffic they can kill site performance dead if applied unconditionally. Prepared statements have their uses, but they are not universally applicable, which is something that the MySQL documentation also clearly states. As far as security goes prepared statements offer nothing more than a reasonable and, IMHO, necessary amount of due diligence on the part of the developer will also achieve. -Stuart -- http://stut.net/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySql Injection advice
On Sun, Jul 12, 2009 at 4:09 AM, Haig Dedeyan hdede...@videotron.ca wrote:
On July 11, 2009 10:57:14 am Haig Dedeyan wrote:
At 10:12 PM -0400 7/10/09, Haig Dedeyan wrote:
[1]
$fname = mysql_real_escape_string($fname);
$lname = mysql_real_escape_string($lname);
$sql = UPDATE phonedir SET fname = '$fname',lname = '$lname' WHERE
id=$id; $result = mysql_query($sql);
echo mysql_error() . \n;
This will result in the addition of the slashes.
[2]
If I do the following, there are no slashes. Just wondering if I'm on
the
right path with the 1st code set..
$sql = UPDATE phonedir SET fname =
'.mysql_real_escape_string($fname).',lname =
'.mysql_real_escape_string($lname).' WHERE id=$id;
$result = mysql_query($sql);
echo mysql_error() . \n;
Haig:
Interesting, I did not know that -- that sounds like a bug to me --
both should be the same.
However, I commonly do [1] and when I have to display the data to a
browser, then I use htmlentities() and stripslashes() before
displaying the data. That way names like O'Brian appear correctly --
else they appear 0\'Brian.
Now maybe I'm doing something wrong, but this way works for me. If
there is a better way, I would like to here it.
Cheers,
tedd
Thanks Tedd.
I did more testing and here's what I have found.
@PHPSter - magic quotes are off
Just entering simple data where an apostrophe is part of the data.
The following code is entering the slash but that's becuase I am escaping
it
twice since mysql_num_rows is throwing an error if an apostrophe is in its
search:
1 -
$new_fname = mysql_real_escape_string($new_fname);
$new_lname = mysql_real_escape_string($new_lname);
$result = mysql_query(SELECT * FROM phonedir WHERE fname = '$new_fname'
lname = '$new_lname');
$num_rows = mysql_num_rows($result);
The error message may be saying the mysql_num_rows is throwing an error but
actual error is on mysql_query function level (Not a correct query)
if($num_rows 0)
{
echo $fname. .$lname. already exists;
}
else
{
mysql_query(INSERT INTO phonedir
(fname, lname)
VALUES('.mysql_real_escape_string($new_fname).','.mysql_real_escape_string($new_lname).'))
or die(mysql_error());
BTW twice escaping is not good
2 - If I do the same code above without the mysql_num_rows and no escaping,
the data doesn't get entered.
I think this is normal behaviour.
Welcome to hell of quotes :(
3 - If I do any of the 2 following sets of code where there is 1 instance
of
escaping, the data gets entered with the apostrophe but I don't see any
back
slash entered.
The part that I am concerned about is if I should be seeing the backslash
entered without having to double escape,
Please see magic_quotes_runtime setting configuration...
http://www.php.net/manual/en/info.configuration.php#ini.magic-quotes-runtime
If it is enables it will automatically removed the slashes from any external
source including databases...
It was there to make the life of developer somewhat easier ()...
magic quotes things are deprecated and completely will be removed in PHP 6
$new_fname = mysql_real_escape_string($new_fname);
$new_lname = mysql_real_escape_string($new_lname);
$result = mysql_query(SELECT * FROM phonedir WHERE fname = '$new_fname'
lname = '$new_lname');
$num_rows = mysql_num_rows($result);
if($num_rows 0)
{
echo $fname. .$lname. already exists;
}
else
{
mysql_query(INSERT INTO phonedir
(fname, lname) VALUES('$new_fname','$new_lname'))
or die(mysql_error());
or
mysql_query(INSERT INTO phonedir
(fname, lname)
VALUES('.mysql_real_escape_string($new_fname).','.mysql_real_escape_string($new_lname).'))
or die(mysql_error());
--
Zareef Ahmed :: A PHP Developer in India ( Delhi )
Homepage :: http://www.zareef.net
Re: [PHP] MySql Injection advice
On July 11, 2009 08:21:34 pm Haig Dedeyan wrote:
On Sun, Jul 12, 2009 at 4:09 AM, Haig Dedeyan hdede...@videotron.ca wrote:
On July 11, 2009 10:57:14 am Haig Dedeyan wrote:
At 10:12 PM -0400 7/10/09, Haig Dedeyan wrote:
[1]
$fname = mysql_real_escape_string($fname);
$lname = mysql_real_escape_string($lname);
$sql = UPDATE phonedir SET fname = '$fname',lname = '$lname' WHERE
id=$id; $result = mysql_query($sql);
echo mysql_error() . \n;
This will result in the addition of the slashes.
[2]
If I do the following, there are no slashes. Just wondering if I'm on
the
right path with the 1st code set..
$sql = UPDATE phonedir SET fname =
'.mysql_real_escape_string($fname).',lname =
'.mysql_real_escape_string($lname).' WHERE id=$id;
$result = mysql_query($sql);
echo mysql_error() . \n;
Haig:
Interesting, I did not know that -- that sounds like a bug to me --
both should be the same.
However, I commonly do [1] and when I have to display the data to a
browser, then I use htmlentities() and stripslashes() before
displaying the data. That way names like O'Brian appear correctly --
else they appear 0\'Brian.
Now maybe I'm doing something wrong, but this way works for me. If
there is a better way, I would like to here it.
Cheers,
tedd
Thanks Tedd.
I did more testing and here's what I have found.
@PHPSter - magic quotes are off
Just entering simple data where an apostrophe is part of the data.
The following code is entering the slash but that's becuase I am escaping
it
twice since mysql_num_rows is throwing an error if an apostrophe is in
its search:
1 -
$new_fname = mysql_real_escape_string($new_fname);
$new_lname = mysql_real_escape_string($new_lname);
$result = mysql_query(SELECT * FROM phonedir WHERE fname = '$new_fname'
lname = '$new_lname');
$num_rows = mysql_num_rows($result);
The error message may be saying the mysql_num_rows is throwing an error but
actual error is on mysql_query function level (Not a correct query)
if($num_rows 0)
{
echo $fname. .$lname. already exists;
}
else
{
mysql_query(INSERT INTO phonedir
(fname, lname)
VALUES('.mysql_real_escape_string($new_fname).','.mysql_real_escape_st
ring($new_lname).')) or die(mysql_error());
BTW twice escaping is not good
2 - If I do the same code above without the mysql_num_rows and no
escaping, the data doesn't get entered.
I think this is normal behaviour.
Welcome to hell of quotes :(
3 - If I do any of the 2 following sets of code where there is 1 instance
of
escaping, the data gets entered with the apostrophe but I don't see any
back
slash entered.
The part that I am concerned about is if I should be seeing the backslash
entered without having to double escape,
Please see magic_quotes_runtime setting configuration...
http://www.php.net/manual/en/info.configuration.php#ini.magic-quotes-runtim
e
If it is enables it will automatically removed the slashes from any
external source including databases...
It was there to make the life of developer somewhat easier ()...
magic quotes things are deprecated and completely will be removed in PHP 6
$new_fname = mysql_real_escape_string($new_fname);
$new_lname = mysql_real_escape_string($new_lname);
$result = mysql_query(SELECT * FROM phonedir WHERE fname = '$new_fname'
lname = '$new_lname');
$num_rows = mysql_num_rows($result);
if($num_rows 0)
{
echo $fname. .$lname. already exists;
}
else
{
mysql_query(INSERT INTO phonedir
(fname, lname) VALUES('$new_fname','$new_lname'))
or die(mysql_error());
or
mysql_query(INSERT INTO phonedir
(fname, lname)
VALUES('.mysql_real_escape_string($new_fname).','.mysql_real_escape_st
ring($new_lname).')) or die(mysql_error());
Thansk Zareef.
Magic quotes are off. This is what my php ini says:
; Magic quotes for incoming GET/POST/Cookie data.
magic_quotes_gpc = Off
; Magic quotes for runtime-generated data, e.g. data from SQL, from exec(),
etc.
magic_quotes_runtime = Off
; Use Sybase-style magic quotes (escape ' with '' instead of \').
magic_quotes_sybase = Off
I won;t be using 2x escapes but I just need to know if I should be seeing the
backslash in the dbase.
@Tedd - I will be looking into prepared statements eventually but I still want
to understand escaping.
Cheers
Haig
[PHP] MySql Injection advice
Hi everyone, I'm starting to experiment with an edit form and I am seeing the following behaviour: $fname = mysql_real_escape_string($fname); $lname = mysql_real_escape_string($lname); $sql = UPDATE phonedir SET fname = '$fname',lname = '$lname' WHERE id=$id; $result = mysql_query($sql); echo mysql_error() . \n; This will result in the addition of the slashes. If I do the following, there are no slashes. Just wondering if I'm on the right path with the 1st code set.. $sql = UPDATE phonedir SET fname = '.mysql_real_escape_string($fname).',lname = '.mysql_real_escape_string($lname).' WHERE id=$id; $result = mysql_query($sql); echo mysql_error() . \n; Cheers Haig
Re: [PHP] MySql Injection advice
Haig Dedeyan wrote: Hi everyone, I'm starting to experiment with an edit form and I am seeing the following behaviour: $fname = mysql_real_escape_string($fname); $lname = mysql_real_escape_string($lname); $sql = UPDATE phonedir SET fname = '$fname',lname = '$lname' WHERE id=$id; $result = mysql_query($sql); echo mysql_error() . \n; This will result in the addition of the slashes. If I do the following, there are no slashes. Just wondering if I'm on the right path with the 1st code set.. $sql = UPDATE phonedir SET fname = '.mysql_real_escape_string($fname).',lname = '.mysql_real_escape_string($lname).' WHERE id=$id; $result = mysql_query($sql); echo mysql_error() . \n; Cheers Haig I highly recommend you switch to prepared statements and not use mysql_real_escape_string Prepared statements is the right way, and you don't end up with slashes. http://dev.mysql.com/tech-resources/articles/4.1/prepared-statements.html -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySql Injection advice
On July 10, 2009 11:26:04 pm Haig Dedeyan wrote: Haig Dedeyan wrote: Hi everyone, I'm starting to experiment with an edit form and I am seeing the following behaviour: $fname = mysql_real_escape_string($fname); $lname = mysql_real_escape_string($lname); $sql = UPDATE phonedir SET fname = '$fname',lname = '$lname' WHERE id=$id; $result = mysql_query($sql); echo mysql_error() . \n; This will result in the addition of the slashes. If I do the following, there are no slashes. Just wondering if I'm on the right path with the 1st code set.. $sql = UPDATE phonedir SET fname = '.mysql_real_escape_string($fname).',lname = '.mysql_real_escape_string($lname).' WHERE id=$id; $result = mysql_query($sql); echo mysql_error() . \n; Cheers Haig I highly recommend you switch to prepared statements and not use mysql_real_escape_string Prepared statements is the right way, and you don't end up with slashes. http://dev.mysql.com/tech-resources/articles/4.1/prepared-statements.html Thanks Michael. I didn't know about prepared statements, I'll check this out and see how it works out. Cheers Haig
