0

I see that this question has been asked a fair amount of times, but I'm a little confused by the responses. I was hoping that somebody could please take a look at the code below and help me with how to insert text, that contains an apostrophe, into a MySQL database. The two things that are likely to contain apostrophes are full_desc and meta_desc. Thanks!

Also, I realize that this is prone to MySQL Injection, so any pointers on securing it will also be greatly appreciated. Thanks again!

<?php
$con=mysqli_connect("mysql.legfly.com","username","password","database");
// Check connection
if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

//sql

$sql="INSERT INTO races (event_name, event_date ,start_time, entry_fee, sanctioned,location, address, city, state, zipcode, country, sport, special_info, distance, race_url, reg_url, print_url, event_phone, event_email, meta_desc, full_desc, course_info, directions, other_info, perma_url, perma_year)
VALUES
('$_POST[event_name]','$_POST[event_date]','$_POST[start_time]','$_POST[entry_fee]','$_POST[sanctioned]','$_POST[location]','$_POST[address]','$_POST[city]','$_POST[state]','$_POST[zipcode]','$_POST[country]','$_POST[sport]','$_POST[special_info]','$_POST[distance]','$_POST[race_url]','$_POST[reg_url]','$_POST[print_url]','$_POST[event_phone]','$_POST[event_email]','$_POST[meta_desc]','$_POST[full_desc]','$_POST[course_info]','$_POST[directions]','$_POST[other_info]','$_POST[perma_url]','$_POST[perma_year]')";

if (!mysqli_query($con,$sql))
{
die('Error: ' . mysqli_error($con));
} 
echo "Your race is added!";

mysqli_close($con); ?>

Thanks again!

Alex
  • 1
  • 2
    The answer to both questions is the same: http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php – Michael Berkowski Jan 02 '14 at 19:48
  • 1
    [**Have a look at Prepared statements**](http://pk1.php.net/pdo.prepared-statements) – M Khalid Junaid Jan 02 '14 at 19:48
  • Many answers in the linked question focuse mostly on PDO but if you have opted for MySQLi the principle is the same and there are ample examples among the answers as well: http://www.php.net/manual/en/mysqli.prepare.php – Michael Berkowski Jan 02 '14 at 19:49
  • @Alex If you read about 'escaping strings' and learn that basics you will get it. The DB driver primary users ' (apostrophe) to enclose a value to be saved. When your value has an apostrophe in it, it breaks the engine. Use mysqli_real_escape_string($value) on any item before adding it to your SQL string and it will escape each apostrophe with a \ character, making it DB safe. – Rottingham Jan 02 '14 at 19:49
  • 2
    Never make assumptions about what fields are likely to contain an apostrophe; somebody trying an SQL injection against your site won't limit themselves to your assumptions – Mark Baker Jan 02 '14 at 19:52
  • I tried mysql_real_escape_string but ran into some issues. Here's how I added similar to the following: $event_name = mysql_real_escape_string($_POST['event_name']); $event_date = mysql_real_escape_string($_POST['event_date']); and turners the $_POST's into the $fieldname short names. – Alex Jan 02 '14 at 19:55
  • @Rottingham Thanks for the help. I'm still slightly confused. I tried adding $full_desc = mysql_real_escape_string($_POST['full_desc']); but then I receive the following error: Warning: mysql_real_escape_string() [function.mysql-real-escape-string]: Can't connect to local MySQL server through socket '/No-MySQL-hostname-was-specified' (2) in /home/aekehr/legfly.com/admin/insert_race.php on line 11 Warning: mysql_real_escape_string() [function.mysql-real-escape-string]: A link to the server could not be established in /home/aekehr/legfly.com/admin/insert_race.php on line 11 – Alex Jan 02 '14 at 22:02
  • @Alex you need to use mysqli_real_escape_string() (notice the i in mysqli). – Rottingham Jan 02 '14 at 22:39

1 Answers1

0

If you add a back-slash before the apostrophe, it will escape it so it's treated as text rather than as part of the query.

E.g. INSERT INTO tablename (id,text) VALUES ('0','This row\'s the first')

It's only an example for how apostrophes can still be inserted. For a more programmed approach, mysqli_real_escape_string/mysql_real_escape_string does the trick on strings before you use them in a query.

Tiago
  • 1,984
  • 1
  • 21
  • 43
  • this is true, but sloppy, because you have to hard code everything. Refer to Prepared Statements and string escape functions. – Rottingham Jan 02 '14 at 19:50
  • It's only an example for how apostrophes can still be inserted. For a more programmed approach, mysqli_real_escape_string/mysql_real_escape_string does the trick. – Tiago Jan 02 '14 at 19:51