I am trying to perform an Insert into a MySQL table using quote_smart to ensure the string is safe. However, I get the following error:

Couldn't insert into tbl_name: 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 'Mr' ', ' Glyndwr ', ' at line 10

The code is:

PHP Code:

//Function to Quote variable to make safe.
function quote_smart($value)
{
//Strip slashes
if (get_magic_quotes_gpc()) {
$value = stripslashes($value);
}
//Quote if not integer
if (!is_numeric($value)) {
$value = "'" . mysql_real_escape_string($value) . "'";
}
return $value;
}
 
//All required fields have been populated so update the databases.
//Connect
$link=mysql_connect("127.0.0.1","root")
OR die("Couldn't connect to MySQL");
$db=mysql_select_db("db1",$link)
OR die("Couldn't open db1: " .mysql_error());
//Store the current date and time.
$CurrentDateTime = date("Y-m-d H:i:s");
//Add the person's name to tbl_name as a customer.
echo quote_smart($_POST[Title]);
$sql_query1 = mysql_query("INSERT INTO tbl_name(Name_Type, 
Title, 
First_Name, 
Middle_Name, 
Last_Name, 
Preferred_Name, 
Created_By, 
Created_Date)
VALUES('Customer', 
' ".quote_smart($_POST[Title])." ', 
' ".$_POST[FirstName]." ', 
' ".$_POST[MiddleName]." ', 
' ".$_POST[LastName]." ', 
' ".$_POST[PreferredName]." ', 
' ".$_POST[UserName]." ',
' ".$CurrentDateTime." ')")
OR die("Couldn't insert into tbl_name: " .mysql_error()); 


The objective is to prevent someone hacking into my DB.This is just a test, I intend to use quote_smart on all the values once I have it working.

1) What is wrong with my code?
2) Is there a better way to do this (i.e., is there a more secure way to prevent hacking)?
3) This will result in all the non numeric values inserted into the DB being enclosed in single quotes. Does someone have some code to remove them when I read (Select) from the DB(i.e., if I extract an address to do a mailing I do not want it enclosed in quotes)?

Your help is greatly appreciated.

Regards,

Glyn
</SPAN>

For anyone who is interested I have been able to resolve this problem. The resolution to this is:

PHP Code:

//Add the person's name to tbl_name as a customer.
$sql_query1 = sprintf("INSERT INTO tbl_name (Name_Type, 
Title, 
First_Name, 
Middle_Name, 
Last_Name, 
Preferred_Name,
Updated_By,
Created_By, 
Created_Date)
VALUES ('Customer', %s, %s, %s, %s, %s, %s, %s, '$CurrentDateTime')",
quote_smart($_POST['Title']),
quote_smart($_POST['FirstName']), 
quote_smart($_POST['MiddleName']), 
quote_smart($_POST['LastName']), 
quote_smart($_POST['PreferredName']),
quote_smart($_POST['UserName']), 
quote_smart($_POST['UserName']));
mysql_query($sql_query1) OR die("Couldn't insert into tbl_name: " .mysql_error()); 


</SPAN>

Now, can anyone tell me of any security flaw to this aproach?
Is it possible for anyone to enter any data into the page that will cause me problems?

Your assistance is greatly appreciated,

Glyn

Firstly Great name. A True Glyn is rare to find.
Secondly, you code is fine and is similar to what is used in industry. By no means can you say your database is secure due to so many methods of accessing the information hackers use.
You've shown that you've covered the obvious methods of protection and I wouldn't consider further security unless you are holding very important information.

Glyn

Hi Glyn,

Thanks for the compliment and the advise. Greatly appreciated. I am a novice learning WAMP to improve my ability to comunicate with both business people and techies. I am a business analyst by trade from a techie back ground (COBOL). I am enjoying it as I am still a techie at heart.

Please see my next post; a request for help with arrays.

Regards,

Glyn