[PHP] Re: Inserting NULL Integer Values
Oliver Grätz wrote:
Shaun schrieb:
$qid = mysql_query('INSERT INTO MYTABLE (
column1,
column2,
) VALUES (
'.$value1.',
'.$value2.'
)');
A bit off-topic but important: Always make sure that you check the
contents of $value1 and $value2 before putting them into the query!
With
$value1 = 'xyz,xyz); DELETE FROM MYTABLE;';
you might get surprising results!
This is called SQL injection and it's important to escape all the values
before putting them into the statement.
Did you try that? This doesn't work on my machine:
mysql_query(DELETE FROM mytable; DELETE FROM mytable;);
ie, mysql extension won't let me do more than one statement at a time.
--
Open source PHP code generator for DB operations
http://sourceforge.net/projects/bfrcg/
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: Inserting NULL Integer Values
On Sat, October 29, 2005 4:45 am, Bogdan Ribic wrote: $value1 = 'xyz,xyz); DELETE FROM MYTABLE;'; you might get surprising results! This is called SQL injection and it's important to escape all the values before putting them into the statement. Did you try that? This doesn't work on my machine: mysql_query(DELETE FROM mytable; DELETE FROM mytable;); ie, mysql extension won't let me do more than one statement at a time. PHP MySQL has not allowed multiple statements per query for awhile, I think. I also think it's possible to change that, or that it might change in the future. Regardless of all that, the general principle remains sound. Even if the one specific example does not work, that doesn't mean that there aren't a few billion that WILL work to compromise your site. http://phpsec.org -- Like Music? http://l-i-e.com/artists.htm -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: Inserting NULL Integer Values
On Tue, October 18, 2005 2:15 pm, Shaun wrote:
Thanks for your replies, rather than check each vaule by name I am
trying to
produce a more dynamic solution:
foreach ($_POST as $key = $value) {
if ($value == '') {
$_POST[$key] == 'NULL';
If you actually have == in this line, that is your trouble.
}
}
--
Like Music?
http://l-i-e.com/artists.htm
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Re: Inserting NULL Integer Values
Either cast your empty ints (which should make it zero) or do an if
(!isset($variable)) { $variable = 'NULL'; }
Ben
On Tue, 18 Oct 2005 12:15:41 -0400, Shaun [EMAIL PROTECTED]
wrote:
Hi,
Up to this point in time I used to construct my insert statements like
this
$qid = mysql_query('INSERT INTO MYTABLE (
column1,
column2,
) VALUES (
'.$value1.',
'.$value2.'
)');
However I understand it is better to remove the quote marks around an
insert
if the column type is an integer. This is easy to do, however if the
$value
is empty it causes a mysql error. Has anyone encountered this and found a
solution?
Thanks for your advice
--
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: Inserting NULL Integer Values
What Ben said is correct, but I'd like to elaborate so you know why it's
correct.
The INSERT statement you're trying to end up with is:
INSERT INTO MYTABLE (column1, column2) VALUES ('somevalue1', 'somevalue2')
I'm not sure why it wouldn't work if you ended up with:
INSERT INTO MYTABLE (column1, column2) VALUES ('', '')
That should work. You can set it so you can't have NULL, but dont know of
anything that tells the database not to accept '' as a value (barring triggers
or other things that check on insert).
Anyway, assuming that the first example is what youre going for, then it sounds
like this is what you want if the first value is empty:
INSERT INTO MYTABLE (column1, column2) VALUES (NULL, 'somevalue2')
So I might try something like this:
$value1 = ;
$value2 = somevalue;
if (is_empty($value1)) {
$value1 = NULL;
} else {
$value1 = ' . $value1 . ';
}
if (is_empty($value2)) {
$value2 = NULL;
} else {
$value2 = ' . $value2 . ';
}
$qid = mysql_query(INSERT INTO MYTABLE (column1, column2) VALUES ($value1,
$value2));
That way, if it's empty, you'll get NULL, otherwise you'll get 'somevalue'.
I use double quotes () PHP variable values (yeah, I know.. some people have
issues because it makes everything inside interpret..blah blah..) and use
single quotes (') for SQL stuff. Looks like you do the opposite. Whatever
works for you.
Good luck!
-TG
= = = Original message = = =
Either cast your empty ints (which should make it zero) or do an if
(!isset($variable)) $variable = 'NULL';
Ben
On Tue, 18 Oct 2005 12:15:41 -0400, Shaun [EMAIL PROTECTED]
wrote:
Hi,
Up to this point in time I used to construct my insert statements like
this
$qid = mysql_query('INSERT INTO MYTABLE (
column1,
column2,
) VALUES (
'.$value1.',
'.$value2.'
)');
However I understand it is better to remove the quote marks around an
insert
if the column type is an integer. This is easy to do, however if the
$value
is empty it causes a mysql error. Has anyone encountered this and found a
solution?
Thanks for your advice
___
Sent by ePrompter, the premier email notification software.
Free download at http://www.ePrompter.com.
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Re: Inserting NULL Integer Values
Hi Ben,
Thanks for your reply, woudn't that insert a string with a value of'NULL';?
Ben Litton [EMAIL PROTECTED] wrote in message
news:[EMAIL PROTECTED]
Either cast your empty ints (which should make it zero) or do an if
(!isset($variable)) { $variable = 'NULL'; }
Ben
On Tue, 18 Oct 2005 12:15:41 -0400, Shaun [EMAIL PROTECTED]
wrote:
Hi,
Up to this point in time I used to construct my insert statements like
this
$qid = mysql_query('INSERT INTO MYTABLE (
column1,
column2,
) VALUES (
'.$value1.',
'.$value2.'
)');
However I understand it is better to remove the quote marks around an
insert
if the column type is an integer. This is easy to do, however if the
$value
is empty it causes a mysql error. Has anyone encountered this and found a
solution?
Thanks for your advice
--
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Re: Inserting NULL Integer Values
Yes, but NULL is a special thing to MySQL. If you don't quote 'NULL' it
just means 'empty' to mySQL. If your database schema allows NULLS (it's
optional), your insert will go through.
On Tue, 18 Oct 2005 13:10:32 -0400, Shaun [EMAIL PROTECTED]
wrote:
Hi Ben,
Thanks for your reply, woudn't that insert a string with a value
of'NULL';?
Ben Litton [EMAIL PROTECTED] wrote in message
news:[EMAIL PROTECTED]
Either cast your empty ints (which should make it zero) or do an if
(!isset($variable)) { $variable = 'NULL'; }
Ben
On Tue, 18 Oct 2005 12:15:41 -0400, Shaun
[EMAIL PROTECTED]
wrote:
Hi,
Up to this point in time I used to construct my insert statements like
this
$qid = mysql_query('INSERT INTO MYTABLE (
column1,
column2,
) VALUES (
'.$value1.',
'.$value2.'
)');
However I understand it is better to remove the quote marks around an
insert
if the column type is an integer. This is easy to do, however if the
$value
is empty it causes a mysql error. Has anyone encountered this and
found a
solution?
Thanks for your advice
--
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/
--
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: Inserting NULL Integer Values
Good explanation but I think he wanted to avoid quoting the integers. I
may be wrong, but I think not quoting integers is a decent practice
because it makes it easier to port your SQL over to a different database
if you later decide you must do so. Of course he could just add a single
quote to both sides of the string, whether it is empty or not, but if he
wants to go without any single quotes, he'll have to use NULL or a
numberic value for every column.
Everything you said is of course correct and a bit less lazy than my
explanation. I would just say that if he didn't want any single quotes at
all he could just replace your elses of $value = ' . $value2 . '; with
$value = (int) $value1; OR $value1 = intval($value1).
If he decies not going to use quotes, it's probably a good idea to make
sure it's really an number or it'll break the query. He could also do an
is_numeric($value1) to make sure it really is one, but if you don't mind
converting an errant string to a zero, casting works fine.
As for the double quotes inside the parser thing, I do that too out of
laziness on occasion, but try not to. If the OP wants to stick to the
gospel and employ quotes he could make your else $value1 = '\'.$value1.\'';
_Ben
On Tue, 18 Oct 2005 13:42:19 -0400, [EMAIL PROTECTED] wrote:
What Ben said is correct, but I'd like to elaborate so you know why it's
correct.
The INSERT statement you're trying to end up with is:
INSERT INTO MYTABLE (column1, column2) VALUES ('somevalue1',
'somevalue2')
I'm not sure why it wouldn't work if you ended up with:
INSERT INTO MYTABLE (column1, column2) VALUES ('', '')
That should work. You can set it so you can't have NULL, but dont know
of anything that tells the database not to accept '' as a value (barring
triggers or other things that check on insert).
Anyway, assuming that the first example is what youre going for, then it
sounds like this is what you want if the first value is empty:
INSERT INTO MYTABLE (column1, column2) VALUES (NULL, 'somevalue2')
So I might try something like this:
$value1 = ;
$value2 = somevalue;
if (is_empty($value1)) {
$value1 = NULL;
} else {
$value1 = ' . $value1 . ';
}
if (is_empty($value2)) {
$value2 = NULL;
} else {
$value2 = ' . $value2 . ';
}
$qid = mysql_query(INSERT INTO MYTABLE (column1, column2) VALUES
($value1, $value2));
That way, if it's empty, you'll get NULL, otherwise you'll get
'somevalue'.
I use double quotes () PHP variable values (yeah, I know.. some people
have issues because it makes everything inside interpret..blah blah..)
and use single quotes (') for SQL stuff. Looks like you do the
opposite. Whatever works for you.
Good luck!
-TG
= = = Original message = = =
Either cast your empty ints (which should make it zero) or do an if
(!isset($variable)) $variable = 'NULL';
Ben
On Tue, 18 Oct 2005 12:15:41 -0400, Shaun [EMAIL PROTECTED]
wrote:
Hi,
Up to this point in time I used to construct my insert statements like
this
$qid = mysql_query('INSERT INTO MYTABLE (
column1,
column2,
) VALUES (
'.$value1.',
'.$value2.'
)');
However I understand it is better to remove the quote marks around an
insert
if the column type is an integer. This is easy to do, however if the
$value
is empty it causes a mysql error. Has anyone encountered this and found
a
solution?
Thanks for your advice
___
Sent by ePrompter, the premier email notification software.
Free download at http://www.ePrompter.com.
--
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: Inserting NULL Integer Values
On Tue, October 18, 2005 12:42 pm, [EMAIL PROTECTED] wrote: That should work. You can set it so you can't have NULL, but dont know of anything that tells the database not to accept '' as a value Any database, other than MySQL, is *NOT* going to accept '' as an integer value. Because '' is not an integer by any stretch of the imagination. If you don't care about ever porting your application to something other than MySQL, then you can IGNORE the advice to not use '' on integers. If there's ANY possibility that some day somebody might maybe wanna use a different database with your application, then don't confuse strings with integers. Actually, it might be better for your own education/sanity/comprehension/documentation/code to not confuse strings and integers with '' around integers in the SQL, but if everything ELSE is good in your code, and it's always going to be MySQL then it's fine. Note that: $value2 = 'NULL'; //PHP $value2 is a string $query = insert into (integer_field) values ($value2); //PHP $query is a string, but... The place-holder in $value2 will be just: NULL No quotes. No apostrophes. Not a string. NULL SQL NULL value representing no value -- Like Music? http://l-i-e.com/artists.htm -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: Inserting NULL Integer Values
Hi all,
Thanks for your replies, rather than check each vaule by name I am trying to
produce a more dynamic solution:
foreach ($_POST as $key = $value) {
if ($value == '') {
$_POST[$key] == 'NULL';
}
}
I was expecting $_POST[$key] to be the same as $key, however this isnt the
case:
$key = city
$_POST[$key] = London
$value = London
Any ideas?
Richard Lynch [EMAIL PROTECTED] wrote in message
news:[EMAIL PROTECTED]
On Tue, October 18, 2005 12:42 pm, [EMAIL PROTECTED] wrote:
That should work. You can set it so you can't have NULL, but dont
know of anything that tells the database not to accept '' as a value
Any database, other than MySQL, is *NOT* going to accept '' as an
integer value.
Because '' is not an integer by any stretch of the imagination.
If you don't care about ever porting your application to something
other than MySQL, then you can IGNORE the advice to not use '' on
integers.
If there's ANY possibility that some day somebody might maybe wanna
use a different database with your application, then don't confuse
strings with integers.
Actually, it might be better for your own
education/sanity/comprehension/documentation/code to not confuse
strings and integers with '' around integers in the SQL, but if
everything ELSE is good in your code, and it's always going to be
MySQL then it's fine.
Note that:
$value2 = 'NULL'; //PHP $value2 is a string
$query = insert into (integer_field) values ($value2);
//PHP $query is a string, but...
The place-holder in $value2 will be just: NULL
No quotes.
No apostrophes.
Not a string.
NULL
SQL NULL value representing no value
--
Like Music?
http://l-i-e.com/artists.htm
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: Inserting NULL Integer Values
Sorry everyone, I missed the integer requirement here. I apologize. And yes, '' isn't a good integer value and will throw an error. That's what I get for not reading thoroughly enough :) -TG = = = Original message = = = On Tue, October 18, 2005 12:42 pm, [EMAIL PROTECTED] wrote: That should work. You can set it so you can't have NULL, but dont know of anything that tells the database not to accept '' as a value Any database, other than MySQL, is *NOT* going to accept '' as an integer value. Because '' is not an integer by any stretch of the imagination. If you don't care about ever porting your application to something other than MySQL, then you can IGNORE the advice to not use '' on integers. If there's ANY possibility that some day somebody might maybe wanna use a different database with your application, then don't confuse strings with integers. Actually, it might be better for your own education/sanity/comprehension/documentation/code to not confuse strings and integers with '' around integers in the SQL, but if everything ELSE is good in your code, and it's always going to be MySQL then it's fine. Note that: $value2 = 'NULL'; //PHP $value2 is a string $query = insert into (integer_field) values ($value2); //PHP $query is a string, but... The place-holder in $value2 will be just: NULL No quotes. No apostrophes. Not a string. NULL SQL NULL value representing no value ___ Sent by ePrompter, the premier email notification software. Free download at http://www.ePrompter.com. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: Inserting NULL Integer Values
You're using two =='s for your assignment.
On Tue, 18 Oct 2005 15:15:59 -0400, Shaun [EMAIL PROTECTED]
wrote:
Hi all,
Thanks for your replies, rather than check each vaule by name I am
trying to
produce a more dynamic solution:
foreach ($_POST as $key = $value) {
if ($value == '') {
$_POST[$key] == 'NULL';
}
}
I was expecting $_POST[$key] to be the same as $key, however this isnt
the
case:
$key = city
$_POST[$key] = London
$value = London
Any ideas?
Richard Lynch [EMAIL PROTECTED] wrote in message
news:[EMAIL PROTECTED]
On Tue, October 18, 2005 12:42 pm, [EMAIL PROTECTED] wrote:
That should work. You can set it so you can't have NULL, but dont
know of anything that tells the database not to accept '' as a value
Any database, other than MySQL, is *NOT* going to accept '' as an
integer value.
Because '' is not an integer by any stretch of the imagination.
If you don't care about ever porting your application to something
other than MySQL, then you can IGNORE the advice to not use '' on
integers.
If there's ANY possibility that some day somebody might maybe wanna
use a different database with your application, then don't confuse
strings with integers.
Actually, it might be better for your own
education/sanity/comprehension/documentation/code to not confuse
strings and integers with '' around integers in the SQL, but if
everything ELSE is good in your code, and it's always going to be
MySQL then it's fine.
Note that:
$value2 = 'NULL'; //PHP $value2 is a string
$query = insert into (integer_field) values ($value2);
//PHP $query is a string, but...
The place-holder in $value2 will be just: NULL
No quotes.
No apostrophes.
Not a string.
NULL
SQL NULL value representing no value
--
Like Music?
http://l-i-e.com/artists.htm
--
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Re: Inserting NULL Integer Values
Shaun schrieb:
$qid = mysql_query('INSERT INTO MYTABLE (
column1,
column2,
) VALUES (
'.$value1.',
'.$value2.'
)');
A bit off-topic but important: Always make sure that you check the
contents of $value1 and $value2 before putting them into the query!
With
$value1 = 'xyz,xyz); DELETE FROM MYTABLE;';
you might get surprising results!
This is called SQL injection and it's important to escape all the values
before putting them into the statement.
An even better solution are prepared statements! With PDO (available as
an extension for PHP 5.x) these are natively supported. You prepare the
statements without any of the values and call them with the values. The
engine automatically escapes your data.
OLLi
Bug? That's not a bug, that's a feature.
[T. John Wendel]
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
