 Noob to SQL please help with my error message. |
  |
| delambo |
 Mar 9 2010, 07:43 PM
Post
#1
|
|
Novice  Group: Members Posts: 26 Joined: 19-November 09 Member No.: 10,378  |
Hi all,
As stated I am a complete noob when it comes to SQL. I have created a table in my database and added fields to it. I have tried to create an address form and resulting database which I have put on my server to test. The form can be found at http://www.sapsuk.com/address%20label%20to...ql%20server.htm and it links to test.php. After filling out the fields and clicking submit I get the error message QUOTE 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 ''address label' (Name, House, HouseNo, Street, Town, County, Postcode, Number, S' at line 1 The code in http://www.sapsuk.com/address%20label%20to...ql%20server.htm is CODE <form name="labelform" action="test.php" method="post"> Name: <input type="text" name="name" /> <br /> House name: <input type="text" name="house" /> and/or number: <input type="text" name="houseno" /> <br /> Street: <input type="text" name="street" /> <br /> Town: <input type="text" name="town" /> <br /> County: <input type="text" name="county" /> <br /> Postcode: <input type="text" name="postcode" /> <br /> Contact telephone number: <input type="text" name="phone" /> <br /> Serial number (if known): <input type="text" name="serial" /> <br /> <br /> Do you need to borrow radio keys from Southern Auto Probelm Solutions? <br /> <br /> <input type="radio" name="keys" value="yes" /> Yes please, post some to me along with the postage labels. <br /> <input type="radio" name="keys" value="no" /> No thanks, I have my own keys and will print my own labels. <br /> <br /> <input type="submit" value="Submit" /> and the code in the resulting page at http://www.sapsuk.com/test.php is CODE <body> <?php $con = mysql_connect("localhost","REMOVED FOR MY SECURITY","ALSO REMOVED FOR MY SECURITY"); if (!$con) { die('Could not connect: ' . mysql_error()); } mysql_select_db("edczcrhp_tbp", $con); $sql="INSERT INTO 'address label' (Name, House, HouseNo, Street, Town, County, Postcode, Number, Serial) VALUES ('$_POST[name]','$_POST[house]','$_POST[houseno]','$_POST[street]','$_POST[town]','$_POST[county]', '$_POST[postcode]','$_POST[phone]','$_POST[serial]')"; if (!mysql_query($sql,$con)) { die('Error: ' . mysql_error()); } echo "1 record added"; mysql_close($con) ?> </body> Any ideas what might be going wrong here? Thanks in advance. This post has been edited by delambo: Mar 9 2010, 07:45 PM |
  |
 
|
| Brian Chandler |
Mar 10 2010, 03:56 AM
Post
#2
|
|
Jocular coder Group: Members Posts: 2,460 Joined: 31-August 06 Member No.: 43 |
QUOTE(delambo @ Mar 10 2010, 09:43 AM)  Hi all, As stated I am a complete noob when it comes to SQL. I have created a table in my database and added fields to it. I have tried to create an address form and resulting database which I have put on my server to test. Sounds like a good start... QUOTE The form can be found at http://www.sapsuk.com/address%20label%20to...ql%20server.htm and it links to test.php. After filling out the fields and clicking submit I get the error message QUOTE 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 ''address label' (Name, House, HouseNo, Street, Town, County, Postcode, Number, S' at line 1 Right. MySQL error messages are frustratingly unhelpful. <rant>But see how happy the Marketing Department must be! In the old days, the message might have been "Syntax error near 'address..." Now the error message is warm, human, sympathetic, etc. It addresses the user as a person, tells them what to do ("consult the manual" indeed, as if programmers were as stupid as Marketing Experts)...<enough rant> But anyway, you can see which mysql query has the problem. First thing is to check _exactly_ what string is being given to MySQL. So before the call to mysql_query($sql,$con), put echo "<p><tt>$sql</tt>" (or similar, but tt helps to make things like this stand out, and aids real checking, because for example commas and full-stops (periods) are distinct). You seem to have a table name ("address label") including spaces... is this really possible? I think it's a very bad idea, even if it isn't the problem; use _ to separate words if you like. Then you don't need to quote table names in general. (And anyway, aren't table names supposed to be quoted with "back-ticks"? (` : see how this is indistinguishable in a typical proportional font)) Hope this helps... and: QUOTE CODE <body> <?php $sql="INSERT INTO 'address label' (Name, House, HouseNo, Street, Town, County, Postcode, Number, Serial) VALUES ('$_POST[name]','$_POST[house]','$_POST[houseno]','$_POST[street]','$_POST[town]','$_POST[county]', '$_POST[postcode]','$_POST[phone]','$_POST[serial]')"; This is Very Bad. You should never copy user input directly to an SQL statement. Suppose the text someone submits as the value of $_POST['serial'] is the following: NULL); DROP TABLE `address label`; SELECT abs(' When that gets spliced into the middle of your query, the outsider has magically gained access to your database, and deleted it. Check each $_POST arg is what you expected, and escape the strings you write into the database fields using this function: http://jp2.php.net/manual/en/function.mysq...cape-string.php Finally: you should quote array indices: $_POST['serial']. In early versions it wasn't necessary, but the PHP manual warns that it may become essential at some stage, because of the clash with constants (serial being a constant value, like 1567, not the string of 6 characters). HTH This post has been edited by Brian Chandler: Mar 10 2010, 04:06 AM |
|
|
|
| delambo |
Mar 12 2010, 12:14 PM
Post
#3
|
|
Novice Group: Members Posts: 26 Joined: 19-November 09 Member No.: 10,378 |
Hi,
Thanks for the reply, I renamed the table address_label and dropped the 's and it all works fin now. I am only experimenting right now having no experience with sql amd only some experience with php. I am happy the table works now and I understand the basics. I have read a little about sql injection and I guess this is what you are talking about with NULL); DROP TABLE `address label`; SELECT abs(' I could only find info from hackers on how to hack databases but couldn't find any reading on how to combat it other than speak to your webmaster!! I guess I better get reading! Cheers. This post has been edited by delambo: Mar 12 2010, 12:15 PM |
|
|
|
|
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:
|
Lo-Fi Version | Time is now: 18th April 2024 - 04:50 AM |
Invision Power Board
© 2024 IPS, Inc.
Licensed to: HTMLHelp.com, LLC