Tycoon Talk
Become a Big fish!
The number 1 forum for online business!
Post topics, ask questions, share your knowledge.
Tycoon Talk is part of Freelancer.com - find skilled workers online at a fraction of the cost.

PHP Forum


You are currently viewing our PHP Forum as a guest. Please register to participate.
Login



Freelance Jobs

Reply
quote_smart Insert into MySQL table syntax error.
Old 01-03-2006, 05:48 AM quote_smart Insert into MySQL table syntax error.
Average Talker

Posts: 28
Location: Australia
Trades: 0
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>
GlyndwrBartlett is offline
Reply With Quote
View Public Profile
 
 
Register now for full access!
Old 01-04-2006, 06:27 AM
Average Talker

Posts: 28
Location: Australia
Trades: 0
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
GlyndwrBartlett is offline
Reply With Quote
View Public Profile
 
Old 01-05-2006, 07:06 AM
glyn's Avatar
Skilled Talker

Posts: 56
Location: Liverpool, UK
Trades: 0
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
glyn is offline
Reply With Quote
View Public Profile
 
Old 01-06-2006, 05:15 PM
Average Talker

Posts: 28
Location: Australia
Trades: 0
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
GlyndwrBartlett is offline
Reply With Quote
View Public Profile
 
Reply     « Reply to quote_smart Insert into MySQL table syntax error.
 

Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are Off
Pingbacks are Off
Refbacks are Off





   
RSS Feed  Feeds: RSS   JS   XML
RSS Feed  Feeds for this forum: RSS   JS   XML



Page generated in 0.13711 seconds with 12 queries