What do I need to know about SQL injection? minimum things to do? (new to MySQL)

MySQL database
#1

Good day phphelp.com

I am new to MySQL and have been studying basic commands and gathering information regarding SQL injections.

I showed some code that inserts data into table using PHP and MySQL to the programmer in my workplace and the programmer told me that this code

[php]//record data into table
$sql=‘INSERT INTO sample(fname,lname,age,email,gender,os,checked_data,comment) VALUES ("’.$fname.’","’.lname.’","’.age.’","’.email.’","’.$gender.’","’.$os.’","’.$checked_data.’","’.$comment.’")’;
$stmt=$dbh->prepare($sql);
$stmt->execute();[/php]

is vulnerable to SQL injection.

Question is
・what are the minimun things I could do to prevent SQL injections?
・what do I need to know?
・from the above codes what are the things I need to rewrite to prevent SQL injections?

I will provide whole code if necessary thanks! :wink:

#2

Look into PDO or Mysqli with prepared statements.

#3

You might want to read these sites and comments:

http://www.php.net/manual/en/security.database.sql-injection.php

http://www.tizag.com/mysqlTutorial/mysql-php-sql-injection.php

http://blogs.msdn.com/b/brian_swan/archive/2010/03/04/what_2700_s-the-right-way-to-avoid-sql-injection-in-php-scripts_3f00_.aspx

And, mostly read up on mysql_real_escape_string()…

Hope that helps!

#4

Ernie,

It is great you are providing so much detailed help, but why do you insist on sticking to deprecated code references?

And, mostly read up on mysql_real_escape_string()....
This is just plain bad advice. mysql_real_escape_string is [b]DEPRECATED[/b]. You make no references in your posts to that fact and neglect to inform people that should be using PDO or Mysqli with prepared statements.

Per the manual:

Warning This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQL extension should be used.
#5

Again, Kevin. That depends on the originator’s code. You can fix their code by totally replacing all of their MySQL into MySQLi or PDO all at one time. In this case he wanted just some minimum info which I game him.

#6

I see that you put much time and effort into some of your responses. Much more than I have the time or inclination for lately, but it would be better to add one more sentence informing not just the poster, but all who may come to this board about not using deprecated code.

Keep up the good help! :smiley:

#7

Yes, I do agree with your comments on notifying newbies on using newer code. I do find, though, that on most sites that I visit, usually 50 to 100 a day, I find that most posts on them are placed by beginners who are learning how to program in general. They do not know what is right or wrong as yet. They are still learning about data, displays and most have issues with simple HTML not to mention which is a better “form” to use. Most don’t even comment their code.

So, yes, I agree, but, at this point I feel it is more important to help, not attempt to move people to a newer platform and mixed them up further.

I will continue to help as best I can. And, I will attempt to try to move people up to your level of platform standards. Hope that helps…

#8
And, I will attempt to try to move people up to your level of platform standards. Hope that helps...

It’s not “MY” level of platform standards. It is just current coding standards.

#9

All Good Advice…

The last couple of days I spent with the vendors of HP Fortify, they make tools for static code analysis. These tools and other’s you can run against any code base and they will point out possible vulnerabilities. The code the OP showed would be flagged as SQL Injection…

And yes as others have mentioned both

  1. Parameterizing the Query
  2. Validating the variables being passed into the Parameter

Will have to take place in order for it not to flag as SQL Injection in most analysis tools.

#10

Thank you all for giving me ideas and references, I will take time reading all those references and research on what will be the best way to code. I will question again when I’m stuck thanks! :wink:

#11

Hanchan, thanks for the karma! Also, the largest issue with SQL-injection is to make sure that ALL
of your inputs from forms are cleansed of possible code. A hacker will try to do things like enter an
SQL command to “DROP” your table. This would, of course erase your table. But, if you cleanse your
input by using correct validation for the info you want typed in and removing the special codes, you
can protect your database from these types of attacks.

The links I sent are only a small number of things to read about this. Please do not hesitate to ask us
further questions on this subjects.

One last note… Programming a bit “personal”. Everyone has their favorite ways to handle how to
cleanse their input data. Some routines are better than others. Some routines are “prettier” than
others. It does take some time to sort out what is best for your site. If your site has a large number
of forms where user’s input large amounts of data, then you need more protections than if it is just a
simple “contact-us” form. So, think ahead on where your site is going. Always protect your data!

Good luck with your studies…

Sponsor our Newsletter | Privacy Policy | Terms of Service