Page 1 of 2
works on mysql 5 not on 4.1.20
Posted: Fri Dec 15, 2006 3:20 pm
by sarris
Hi there.Do you know why this would work on my pc where i have mysql 5.0 and why it wouldnt work on 4.1.20 which is installed on the web hosting server?(dont even know why i hosted on that server with so old mysql...didnt check it out before paying...)
$sql = "INSERT INTO home
(id,address,size,rent,rent_price,sale,sale_price,area_id,owner_id,beds_num,baths_num,kitch
ens_num,parking_spaces,yard,yard_size,balcony,balcony_size,furnitured,property_built,prop
erty_renovated,comments) VALUES (id, '$address', $size,$rent,$rentprice,$sale,$saleprice,
$areaid,$ownerid,$beds,$baths,$kitchens,$parkings,$yard,$yardsize,$balcony,$balconysiz
e,$furnished,$propertybuilt,$propertyrenovated,'$comments')";
address and comments get their value from a text box and may contain more than one word.
rentprice saleprice yardsize balconysize propertybuilt and propertyrenovated take their value from a text box as well but contain only one number...
I guess somewhere there is the conflict...If you can find the problem it would be really helpfull.
Thank you in advance
Posted: Fri Dec 15, 2006 3:49 pm
by onion2k
There's nothing in your SQL that would stop the statement working on MySQL 4. Have you escaped all the input?
Also, MySQL 4.1 is fairly standard for web hosting. You'd be hard pushed to find a host that supports MySQL 5. Unless you're using some of the more advanced features you'll find that 4.1 is more than good enough.
Posted: Fri Dec 15, 2006 3:58 pm
by sarris
well i dont know...What i know is that
A) works on my pc 100% ok and with the same exactly files gives an sql error on the querry on the webhosting server.
B) its super weird for me that the querry needed quotes for the 'address' and 'comments' strings and not for the other data obtained from text boxes. If someone can explain this to me i will be glad.
Thanks
Posted: Fri Dec 15, 2006 6:48 pm
by John Cartwright
sarris wrote:A) works on my pc 100% ok and with the same exactly files gives an sql error on the querry on the webhosting server.
Well what is the error? Please post other relevant code, as in where you sanitize and gather the input.
B) its super weird for me that the querry needed quotes for the 'address' and 'comments' strings and not for the other data obtained from text boxes. If someone can explain this to me i will be glad.[/quote]
Typically you should quote everything except integers. But to answer your question, it is so sql knows that everything contained within the quotes is a single value.
Posted: Fri Dec 15, 2006 7:13 pm
by sarris
Jcart | Please use Code: Select all
and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
Code: Select all
<?php
$conn = mysql_connect("localhost","user","pass");
if (!$conn)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("dbname", $conn);
$address = $_POST[Address];
$size = $_POST[Size];
if ($R1 == "V1"){
$rent = 1;
$sale = 0;
$rentprice = $_POST[rent_price];
$saleprice = 'null';
}
if ($R1 == "V2"){
$rent = 0;
$sale = 1;
$rentprice = 'null';
$saleprice = $_POST[sale_price];
}
$areaid = $_POST[Area];
$baths = $_POST[baths_num];
$beds = $_POST[beds_num];
$kitchens = $_POST[kitchens_num];
$parkings = $_POST[parkings_num];
if ($R2 == "V1"){
$yard = 1;
$yardsize = $_POST[yard_size];
}
if($R2 == "V2"){
$yard = 0;
$yardsize = 'null';
}
if ($R3 == "V1"){
$balcony = 1;
$balconysize = $_POST[balcony_size];
}
if($R3 == "V2"){
$balcony = 0;
$balconysize = 'null';
}
if ($R4 == "V1"){
$furnished = 1;
}
if($R4 == "V2"){
$furnished = 0;
}
$built = $_POST[propertybuilt];
$renovated = $_POST[propertyrenovated];
$comments = $_POST[S1];
$ownerid = 'null';
$sql = "INSERT INTO home (id,address,size,rent,rent_price,sale,sale_price,area_id,owner_id,beds_num,baths_num,kitchens_num,
parking_spaces,yard,yard_size,balcony,balcony_size,furnitured,property_built,property_renovated,comments)
VALUES (id, '$address', $size,$rent,$rentprice,$sale,$saleprice, $areaid,$ownerid,$beds,$baths,$kitchens,$parkings,$yard,$yardsize,$balcony,$balconysize,$furnished,$propertybuilt,
$propertyrenovated,'$comments')";
if (!mysql_query($sql,$conn))
{
die('Error: ' . mysql_error());
}
echo "1 record added";
mysql_close($conn)
?>
Thats the whole code.
The error is
Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ',,, 5,null,3,2,1,null,,,,,,,,'New apartment in Kifisia')' at line 1
Still its works all fine when i run it on my pc which runs mysql 5.0
Moreover another question is that...If i leave the text box of property_built for example empty, the querry crashes...How can i do it so when the text box is empty, a NULL value is passed??
Jcart | Please use Code: Select all
and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
Posted: Fri Dec 15, 2006 7:58 pm
by John Cartwright
and where are $R1, $R2, $R3, and $R4 being set?
For the sake of clarity, run the following in a new file and tell us the results of both servers please .
Code: Select all
<?php
$neg = array('off', 0, false, '', null);
$flags = array(
'Register Globals' => 'register_globals',
'Short Tags' => 'short_open_tag',
'Display Errors' => 'display_errors',
'Magic Quotes GPC' => 'magic_quotes_gpc',
'Magic Quotes Runtime' => 'magic_quotes_runtime',
'Magic Quotes Sybase' => 'magic_quotes_sybase',
);
$ve = phpversion();
$os = PHP_OS;
$er = intval(error_reporting());
foreach ($flags as $n => $v)
{
$flags[$n] = (in_array(strtolower(ini_get($v)), $neg) ? 'Off' : 'On');
}
$cli = (php_sapi_name() == 'cli');
$eol = "\n";
$gle = get_loaded_extensions();
$rows = array();
$le = '';
$wide = 4;
$j = count($gle);
$pad = $wide - $j % $wide;
$len = max(array_map('strlen', $gle));
$func = create_function('$a', 'return str_pad($a, ' . intval($len) . ');');
$gle = array_map($func, $gle);
for($i = 0; $i < $j; $i += $wide)
{
$le .= ' ' . implode(' ', array_slice($gle, $i, $wide)) . $eol;
}
$ec = array(
'E_STRICT' => 2048, 'E_ALL' => 2047, 'E_USER_NOTICE' => 1024,
'E_USER_WARNING' => 512, 'E_USER_ERROR' => 256, 'E_COMPILE_WARNING' => 128,
'E_COMPILE_ERROR' => 64, 'E_CORE_WARNING' => 32, 'E_CORE_ERROR' => 16,
'E_NOTICE' => 8, 'E_PARSE' => 4, 'E_WARNING' => 2, 'E_ERROR' => 1,
);
$e = array();
$t = $er;
foreach ($ec as $n => $v)
{
if (($t & $v) == $v)
{
$e[] = $n;
$t ^= $v;
}
}
if (ceil(count($ec) / 2) + 1 < count($e))
{
$e2 = array();
foreach ($ec as $n => $v)
{
if (!in_array($n, $e) and $n != 'E_ALL')
{
$e2[] = $n;
}
}
$er = $er . ' ((E_ALL | E_STRICT) ^ ' . implode(' ^ ', $e2) . '))';
}
else
{
$er = $er . ' (' . implode(' | ', $e) . ')';
}
if (!$cli)
{
echo '<html><head><title>quick info</title></head><body><pre>', $eol;
}
echo 'PHP Version: ', $ve, $eol;
echo 'PHP OS: ', $os, $eol;
echo 'Error Reporting: ', $er, $eol;
foreach ($flags as $n => $v)
{
echo $n, ': ', $v, $eol;
}
echo 'Loaded Extensions:', $eol, $le, $eol;
if (!$cli)
{
echo '</pre></body></html>', $eol;
}
?>
as for
Moreover another question is that...If i leave the text box of property_built for example empty, the querry crashes...How can i do it so when the text box is empty, a NULL value is passed??
Typically this is done with something like
Code: Select all
$property = !empty($_POST['property']) ? $_POST['property'] : '';
Basically, if property is not set, assign it a blank value.
In terms of security, you should at minimum pass all your user defined variables through mysql_real_escape_string() to avoid sql injection. For instance, add an apostraphe to one of the input fields values.
Posted: Fri Dec 15, 2006 8:34 pm
by sarris
****THIS IS WHAT I GET WHEN RUNNING IT ON MY PC*****
PHP Version: 5.2.0
PHP OS: WINNT
Error Reporting: 6143 (E_ALL)
Register Globals: On
Short Tags: Off
Display Errors: Off
Magic Quotes GPC: Off
Magic Quotes Runtime: Off
Magic Quotes Sybase: Off
Loaded Extensions:
bcmath calendar com_dotnet ctype
session filter ftp hash
iconv json odbc pcre
Reflection date libxml standard
tokenizer zlib SimpleXML dom
SPL wddx xml xmlreader
xmlwriter apache2handler mbstring mysql
mysqli
****THIS IS WHAT I GET WHEN RUNNING IT ON THE WEB HOSTING SERVER*****
PHP Version: 4.3.9
PHP OS: Linux
Error Reporting: 1 (E_ERROR)
Register Globals: Off
Short Tags: On
Display Errors: On
Magic Quotes GPC: On
Magic Quotes Runtime: Off
Magic Quotes Sybase: Off
Loaded Extensions:
yp xml wddx tokenizer
sysvshm sysvsem standard sockets
shmop session pspell posix
pcre overload mime_magic iconv
gmp gettext ftp exif
dio dbx dba curl
ctype calendar bz2 bcmath
zlib openssl apache2handler domxml
gd imap ldap mbstring
mysql ncurses odbc pgsql
sitebuilder snmp xmlrpc zip
ionCube Loader Zend Optimizer
Refering to the apostrophe adding...That sounds as a good solution.But cant i instead assign a NULL value?
R1 R2 R3 etc are Option Button Groups.
Posted: Fri Dec 15, 2006 8:38 pm
by John Cartwright
As I suspected, register globals is set to ON on your local server, which is a bad thing... I believe any php version (> 4.3) has it disabled by default so I am assuming you used an installation kit that turned it on. All you'll have to do is refer to $R1, $R2, etc as $_POST['R1'], $_POST['R2'], etc just like your other form variables.
But cant i instead assign a NULL value?
You can assign it to whatever as long as it is being assigned.
Posted: Fri Dec 15, 2006 9:13 pm
by sarris
Wow you good...
Ok doesnt really work 100% but seems that the problem is getting simpler
I changed all $R1 etc to $_POST['R1'] and now the error seems to be concentrated on the '$comments' value
Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ','New apartment in Kifisia')' at line 1
Thing is i havnt really understood where i shoud use quotes and where not, and where to use single ones and were double ones...
Many tutorials on the net suggest to use $_POST['value'], others $_POST[value] and others $_POST["value"] to get a value
And then when you refer to a value sometimes guotes are used and sometimes not......
Kind of confused there
By the way thanks a lot for all the help so far
Posted: Fri Dec 15, 2006 9:49 pm
by John Cartwright
After a quick glance, I didn't see anything wrong per se. Just a tip, when using long insert queries, I find it better to use the SET keyword.
Code: Select all
$sql = 'INSERT INTO
`home`
SET
`address` = \''. mysql_real_escape_string($address).'\',
`price` = '. intval($price).'
...
';
Some other things to keep in mind..
1. Quote your string values, integers don't need quotes
2. Always use backticks on column names to avoid possible clases of reserved words (and future updates)
3. Always use mysql_real_escape_string() on your string values, and intval() or typecasting on integers
Also, you might be interested to turn on error reporting, simply add this to the top of your script
You'll notice a bunch of undefined variables, simply meaning your trying to use variables that don't exist yet. This is not neccesarily going to break your script but it is poor coding practice. As mentioned previously, it is common to assign defeault values to variables you are expecting.
Posted: Sat Dec 16, 2006 9:37 am
by sarris
Ok i'll try this out...
What do i do with the auto_increment id field?
Posted: Sat Dec 16, 2006 9:38 am
by John Cartwright
Don't do anything.

Posted: Sat Dec 16, 2006 9:41 am
by sarris
Ok cool...And another thing.Does intval work ok if it gets 'null' as parameter?
Posted: Sat Dec 16, 2006 9:55 am
by John Cartwright
sarris wrote:Ok cool...And another thing.Does intval work ok if it gets 'null' as parameter?
Helps to read the manual.

Posted: Sat Dec 16, 2006 10:14 am
by sarris
Jcart | Please use Code: Select all
and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
Changed my querry to that and works perfectly...thank you!!!I didnt really get what all those ` and ' and \" are but will find out on my own.
Thanks again.
php:
Code: Select all
$sql = 'INSERT INTO
`home`
SET
`address` = \''. mysql_real_escape_string($address).'\',
`size` = '. intval($size).',
`rent` = '. intval($rent).',
`rent_price` = '. intval($rentprice).',
`sale` = '. intval($sale).',
`sale_price` = '. intval($saleprice).',
`area_id` = '. intval($areaid).',
`owner_id` = '. intval($ownerid).',
`beds_num` = '. intval($beds).',
`baths_num` = '. intval($baths).',
`kitchens_num` = '. intval($kitchens).',
`parking_spaces` = '. intval($parkings).',
`yard` = '. intval($yard).',
`yard_size` = '. intval($yardsize).',
`balcony` = '. intval($balcony).',
`balcony_size` = '. intval($balcony_size).',
`furnitured` = '. intval($furnished).',
`property_built` = '. intval($propertybuilt).',
`property_renovated` = '. intval($propertyrenovated).',
`comments` = \''. mysql_real_escape_string($address).'\'
';
In the manual for intval, the word NULL doesnt even exist...Anyway i found out that 0 values
are passed for null values entered
There is one issue though...i dont really want that, as when i show the data, i'd rather have no
indication of value if its null, instead of having a zero...
Any ideas?
Jcart | Please use Code: Select all
and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]