Inserting into MySql

Discussions of secure PHP coding. Security in software is important, so don't be afraid to ask. And when answering: be anal. Nitpick. No security vulnerability is too small.

Moderator: General Moderators

Post Reply
YoussefSiblini
Forum Contributor
Posts: 206
Joined: Thu Jul 21, 2011 1:51 pm

Inserting into MySql

Post by YoussefSiblini »

Hi,
I am creating a registration system, is this secure enough before I add the into my database:

Code: Select all

$name = mysql_real_escape_string($name);
Or do I need to add more code?

Youssef
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Inserting into MySql

Post by Celauran »

You'd do better to use prepared statements.
YoussefSiblini
Forum Contributor
Posts: 206
Joined: Thu Jul 21, 2011 1:51 pm

Re: Inserting into MySql

Post by YoussefSiblini »

Thank you for your reply,

Sorry but what you mean, as I am still new to php?
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Inserting into MySql

Post by Celauran »

Prepared statements:
MySQLi
PDO
YoussefSiblini
Forum Contributor
Posts: 206
Joined: Thu Jul 21, 2011 1:51 pm

Re: Inserting into MySql

Post by YoussefSiblini »

thank you
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: Inserting into MySql

Post by social_experiment »

I haven't used prepared statements myself so i might be missing this point; there doesn't seem to be any escaping of data using mysqli_real_escape_string() in the mysqli::prepare examples. In both examples, imo, it would still advisable to have some sort of security check, if a value is empty or of a specific type. Maybe it was left out of the example just for the sake of brevity?
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
YoussefSiblini
Forum Contributor
Posts: 206
Joined: Thu Jul 21, 2011 1:51 pm

Re: Inserting into MySql

Post by YoussefSiblini »

Hi guys,

Does that mean

Code: Select all

$name = mysql_real_escape_string($name);
is not secure enough to use?
As I never used prepared statements before.


Youssef
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: Inserting into MySql

Post by social_experiment »

It is secure as you have it;
http://mattbango.com/notebook/web-devel ... nd-mysqli/
Article about prepared statements; going to read it myself
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
YoussefSiblini
Forum Contributor
Posts: 206
Joined: Thu Jul 21, 2011 1:51 pm

Re: Inserting into MySql

Post by YoussefSiblini »

Thank you,
That is a good link to get me started with it :)
User avatar
Mordred
DevNet Resident
Posts: 1579
Joined: Sun Sep 03, 2006 5:19 am
Location: Sofia, Bulgaria

Re: Inserting into MySql

Post by Mordred »

THe SQLi article in my sig has the details on when escaping is not enough. For some cases it also applies to prepared statements (when issuing them) as well.
priyankagound
Forum Commoner
Posts: 27
Joined: Thu Sep 19, 2013 2:53 am

Re: Inserting into MySql

Post by priyankagound »

Insert Data Into a Database Table
The INSERT INTO statement is used to add new records to a database table.

Syntax

It is possible to write the INSERT INTO statement in two forms.

The first form doesn't specify the column names where the data will be inserted, only their values:

INSERT INTO table_name
VALUES (value1, value2, value3,...)
The second form specifies both the column names and the values to be inserted:

INSERT INTO table_name (column1, column2, column3,...)
VALUES (value1, value2, value3,...)


Example:

Code: Select all

<?php
$con=mysqli_connect("example.com","peter","abc123","my_db");
// Check connection
if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }

$sql="INSERT INTO Persons (FirstName, LastName, Age)
VALUES
('$_POST[firstname]','$_POST[lastname]','$_POST[age]')";

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

mysqli_close($con);
?>
Hope this helps you.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Inserting into MySql

Post by Christopher »

Do not do put unescaped values from the request into SQL:

Code: Select all

$sql="INSERT INTO Persons (FirstName, LastName, Age)
VALUES
('$_POST[firstname]','$_POST[lastname]','$_POST[age]')";   // DANGEROUS!!!
Use prepared statements:

Code: Select all

$sql="INSERT INTO Persons (FirstName, LastName, Age)
VALUES
(?,?,?)";
(#10850)
Post Reply