Home >Backend Development >PHP Tutorial >Specific code examples for protecting databases with PHP_PHP tutorial
There are many examples of data loss due to poor database management, causing losses to oneself. We are going to talk about it this timeThe following code shows a sample script to run a SQL statement. In this case, the SQL statement is a dynamic statement that allows the same attack. The owner of this form may think the form is safe because they have restricted the column names to select lists. However, the code misses one last tip about form cheating — just because the code limits the options to a drop-down box doesn't mean that someone else can't post the form with the required content (including the asterisk [*]).
<ol class="dp-xml"> <li class="alt"><span><strong><font color="#006699"><span class="tag"><</SPAN><SPAN class=tag-name>html</SPAN><SPAN class=tag>></span></font></strong><span> </span></span></li> <li class=""> <span></span><strong><font color="#006699"><span class="tag"><</SPAN><SPAN class=tag-name>head</SPAN><SPAN class=tag>></span></font></strong><span> </span> </li> <li class="alt"> <span></span><strong><font color="#006699"><span class="tag"><</SPAN><SPAN class=tag-name>title</SPAN><SPAN class=tag>></span></font></strong><span>SQL Injection Example</span><strong><font color="#006699"><span class="tag"></</SPAN><SPAN class=tag-name>title</SPAN><SPAN class=tag>></span></font></strong><span> </span> </li> <li class=""> <span></span><strong><font color="#006699"><span class="tag"></</SPAN><SPAN class=tag-name>head</SPAN><SPAN class=tag>></span></font></strong><span> </span> </li> <li class="alt"> <span></span><strong><font color="#006699"><span class="tag"><</SPAN><SPAN class=tag-name>body</SPAN><SPAN class=tag>></span></font></strong><span> </span> </li> <li class=""> <span></span><strong><font color="#006699"><span class="tag"><</SPAN><SPAN class=tag-name>form</SPAN></FONT></STRONG><SPAN> </SPAN><SPAN class=attribute><FONT color=#ff0000>id</FONT></SPAN><SPAN>=</SPAN><SPAN class=attribute-value><FONT color=#0000ff>"myFrom"</FONT></SPAN><SPAN> </SPAN><SPAN class=attribute><FONT color=#ff0000>action</FONT></SPAN><SPAN>=</SPAN><SPAN class=attribute-value><FONT color=#0000ff>"<?php echo $_SERVER['PHP_SELF']; ?>"</span></font><span> </span><li class="alt"> <span> </span><span class="attribute"><font color="#ff0000">method</font></span><span>=</span><span class="attribute-value"><font color="#0000ff">"post"</font></span><span class="tag"><strong><font color="#006699">></font></strong></span><span> </span> </li> <li class=""> <span></span><strong><font color="#006699"><span class="tag"><</SPAN><SPAN class=tag-name>div</SPAN><SPAN class=tag>></span><span class="tag"><</SPAN><SPAN class=tag-name>input</SPAN></FONT></STRONG><SPAN> </SPAN><SPAN class=attribute><FONT color=#ff0000>type</FONT></SPAN><SPAN>=</SPAN><SPAN class=attribute-value><FONT color=#0000ff>"text"</FONT></SPAN><SPAN> </SPAN><SPAN class=attribute><FONT color=#ff0000>name</FONT></SPAN><SPAN>=</SPAN><SPAN class=attribute-value><FONT color=#0000ff>"account_number"</FONT></SPAN><SPAN> </SPAN></SPAN><LI class=alt><SPAN> </SPAN><SPAN class=attribute><FONT color=#ff0000>value</FONT></SPAN><SPAN>="</SPAN><STRONG><FONT color=#006699><SPAN class=tag><?</SPAN><SPAN class=tag-name>php</SPAN></FONT></STRONG><SPAN> echo(isset($_POST['account_number']) ? </SPAN></SPAN><LI class=""><SPAN> $_POST['account_number'] : ''); </SPAN><SPAN class=tag><STRONG><FONT color=#006699>?></span></font></strong><span>" </span><span class="tag"><strong><font color="#006699">/></font></strong></span><span> </span> </li> <li class="alt"> <span></span><strong><font color="#006699"><span class="tag"><</SPAN><SPAN class=tag-name>select</SPAN></FONT></STRONG><SPAN> </SPAN><SPAN class=attribute><FONT color=#ff0000>name</FONT></SPAN><SPAN>=</SPAN><SPAN class=attribute-value><FONT color=#0000ff>"col"</FONT></SPAN><SPAN class=tag><STRONG><FONT color=#006699>></span></font></strong><span> </span> </li> <li class=""> <span></span><strong><font color="#006699"><span class="tag"><</SPAN><SPAN class=tag-name>option</SPAN></FONT></STRONG><SPAN> </SPAN><SPAN class=attribute><FONT color=#ff0000>value</FONT></SPAN><SPAN>=</SPAN><SPAN class=attribute-value><FONT color=#0000ff>"account_number"</FONT></SPAN><SPAN class=tag><STRONG><FONT color=#006699>></span></font></strong><span>Account Number</span><strong><font color="#006699"><span class="tag"></</SPAN><SPAN class=tag-name>option</SPAN><SPAN class=tag>></span></font></strong><span> </span> </li> <li class="alt"> <span></span><strong><font color="#006699"><span class="tag"><</SPAN><SPAN class=tag-name>option</SPAN></FONT></STRONG><SPAN> </SPAN><SPAN class=attribute><FONT color=#ff0000>value</FONT></SPAN><SPAN>=</SPAN><SPAN class=attribute-value><FONT color=#0000ff>"name"</FONT></SPAN><SPAN class=tag><STRONG><FONT color=#006699>></span></font></strong><span>Name</span><strong><font color="#006699"><span class="tag"></</SPAN><SPAN class=tag-name>option</SPAN><SPAN class=tag>></span></font></strong><span> </span> </li> <li class=""> <span></span><strong><font color="#006699"><span class="tag"><</SPAN><SPAN class=tag-name>option</SPAN></FONT></STRONG><SPAN> </SPAN><SPAN class=attribute><FONT color=#ff0000>value</FONT></SPAN><SPAN>=</SPAN><SPAN class=attribute-value><FONT color=#0000ff>"address"</FONT></SPAN><SPAN class=tag><STRONG><FONT color=#006699>></span></font></strong><span>Address</span><strong><font color="#006699"><span class="tag"></</SPAN><SPAN class=tag-name>option</SPAN><SPAN class=tag>></span></font></strong><span> </span> </li> <li class="alt"> <span></span><strong><font color="#006699"><span class="tag"></</SPAN><SPAN class=tag-name>select</SPAN><SPAN class=tag>></span></font></strong><span> </span> </li> <li class=""> <span></span><strong><font color="#006699"><span class="tag"><</SPAN><SPAN class=tag-name>input</SPAN></FONT></STRONG><SPAN> </SPAN><SPAN class=attribute><FONT color=#ff0000>type</FONT></SPAN><SPAN>=</SPAN><SPAN class=attribute-value><FONT color=#0000ff>"submit"</FONT></SPAN><SPAN> </SPAN><SPAN class=attribute><FONT color=#ff0000>value</FONT></SPAN><SPAN>=</SPAN><SPAN class=attribute-value><FONT color=#0000ff>"Save"</FONT></SPAN><SPAN> </SPAN><SPAN class=attribute><FONT color=#ff0000>name</FONT></SPAN><SPAN>=</SPAN><SPAN class=attribute-value><FONT color=#0000ff>"submit"</FONT></SPAN><SPAN> </SPAN><STRONG><FONT color=#006699><SPAN class=tag>/></span><span class="tag"></</SPAN><SPAN class=tag-name>div</SPAN><SPAN class=tag>></span></font></strong><span> </span> </li> <li class="alt"> <span></span><strong><font color="#006699"><span class="tag"></</SPAN><SPAN class=tag-name>form</SPAN><SPAN class=tag>></span></font></strong><span> </span> </li> <li class=""> <span></span><strong><font color="#006699"><span class="tag"><?</SPAN><SPAN class=tag-name>php</SPAN></FONT></STRONG><SPAN> </SPAN></SPAN><LI class=alt><SPAN>if ($_POST['submit'] == 'Save') { </SPAN><LI class=""><SPAN> /* do the form processing */ </SPAN><LI class=alt><SPAN> $</SPAN><SPAN class=attribute><FONT color=#ff0000>link</FONT></SPAN><SPAN> = </SPAN><SPAN class=attribute-value><FONT color=#0000ff>mysql_connect</FONT></SPAN><SPAN>('hostname', 'user', 'password') or </SPAN></SPAN><LI class=""><SPAN> die ('Could not connect' . mysql_error()); </SPAN><LI class=alt><SPAN> mysql_select_db('test', $link); </SPAN><LI class=""><SPAN> </SPAN><LI class=alt><SPAN> $</SPAN><SPAN class=attribute><FONT color=#ff0000>col</FONT></SPAN><SPAN> = $_POST['col']; </SPAN></SPAN><LI class=""><SPAN> </SPAN><LI class=alt><SPAN> $</SPAN><SPAN class=attribute><FONT color=#ff0000>select</FONT></SPAN><SPAN> = </SPAN><SPAN class=attribute-value><FONT color=#0000ff>"SELECT "</FONT></SPAN><SPAN> . $col . " FROM account_data WHERE </SPAN><SPAN class=attribute><FONT color=#ff0000>account_number</FONT></SPAN><SPAN> = " </SPAN></SPAN><LI class=""><SPAN> . $_POST['account_number'] . ";" ; </SPAN><LI class=alt><SPAN> echo '</SPAN><STRONG><FONT color=#006699><SPAN class=tag><</SPAN><SPAN class=tag-name>p</SPAN><SPAN class=tag>></span></font></strong><span>' . $select . '</span><strong><font color="#006699"><span class="tag"></</SPAN><SPAN class=tag-name>p</SPAN><SPAN class=tag>></span></font></strong><span>'; </span> </li> <li class=""><span> </span></li> <li class="alt"> <span> $</span><span class="attribute"><font color="#ff0000">result</font></span><span> = </span><span class="attribute-value"><font color="#0000ff">mysql_query</font></span><span>($select) or die('</span><strong><font color="#006699"><span class="tag"><</SPAN><SPAN class=tag-name>p</SPAN><SPAN class=tag>></span></font></strong><span>' . mysql_error() . '</span><strong><font color="#006699"><span class="tag"></</SPAN><SPAN class=tag-name>p</SPAN><SPAN class=tag>></span></font></strong><span>'); </span> </li> <li class=""><span> </span></li> <li class="alt"> <span> echo '</span><strong><font color="#006699"><span class="tag"><</SPAN><SPAN class=tag-name>table</SPAN><SPAN class=tag>></span></font></strong><span>'; </span> </li> <li class=""> <span> while ($</span><span class="attribute"><font color="#ff0000">row</font></span><span> = </span><span class="attribute-value"><font color="#0000ff">mysql_fetch_assoc</font></span><span>($result)) { </span> </li> <li class="alt"> <span> echo '</span><strong><font color="#006699"><span class="tag"><</SPAN><SPAN class=tag-name>tr</SPAN><SPAN class=tag>></span></font></strong><span>'; </span> </li> <li class=""> <span> echo '</span><strong><font color="#006699"><span class="tag"><</SPAN><SPAN class=tag-name>td</SPAN><SPAN class=tag>></span></font></strong><span>' . $row[$col] . '</span><strong><font color="#006699"><span class="tag"></</SPAN><SPAN class=tag-name>td</SPAN><SPAN class=tag>></span></font></strong><span>'; </span> </li> <li class="alt"> <span> echo '</span><strong><font color="#006699"><span class="tag"></</SPAN><SPAN class=tag-name>tr</SPAN><SPAN class=tag>></span></font></strong><span>'; </span> </li> <li class=""><span> } </span></li> <li class="alt"> <span> echo '</span><strong><font color="#006699"><span class="tag"></</SPAN><SPAN class=tag-name>table</SPAN><SPAN class=tag>></span></font></strong><span>'; </span> </li> <li class=""><span> </span></li> <li class="alt"><span> mysql_close($link); </span></li> <li class=""><span>} </span></li> <li class="alt"> <span></span><span class="tag"><strong><font color="#006699">?></font></strong></span><span> </span> </li> <li class=""> <span></span><strong><font color="#006699"><span class="tag"></</SPAN><SPAN class=tag-name>body</SPAN><SPAN class=tag>></span></font></strong><span> </span> </li> <li class="alt"> <span></span><strong><font color="#006699"><span class="tag"></</SPAN><SPAN class=tag-name>html</SPAN><SPAN class=tag>></span></font></strong><span> </span> </li></strong> </li> </ol>
Therefore, to form PHP's habit of protecting the database, please avoid using dynamic SQL code as much as possible. If you cannot avoid dynamic SQL code, do not use input directly on a column. Below shows how, in addition to using a static column, you can add a simple validation routine to the account number field to ensure that the input value is not a non-numeric value.
<ol class="dp-xml"> <li class="alt"><span><strong><font color="#006699"><span class="tag"><</SPAN><SPAN class=tag-name>html</SPAN><SPAN class=tag>></span></font></strong><span> </span></span></li> <li class=""> <span></span><strong><font color="#006699"><span class="tag"><</SPAN><SPAN class=tag-name>head</SPAN><SPAN class=tag>></span></font></strong><span> </span> </li> <li class="alt"> <span></span><strong><font color="#006699"><span class="tag"><</SPAN><SPAN class=tag-name>title</SPAN><SPAN class=tag>></span></font></strong><span>SQL Injection Example</span><strong><font color="#006699"><span class="tag"></</SPAN><SPAN class=tag-name>title</SPAN><SPAN class=tag>></span></font></strong><span> </span> </li> <li class=""> <span></span><strong><font color="#006699"><span class="tag"></</SPAN><SPAN class=tag-name>head</SPAN><SPAN class=tag>></span></font></strong><span> </span> </li> <li class="alt"> <span></span><strong><font color="#006699"><span class="tag"><</SPAN><SPAN class=tag-name>body</SPAN><SPAN class=tag>></span></font></strong><span> </span> </li> <li class=""> <span></span><strong><font color="#006699"><span class="tag"><</SPAN><SPAN class=tag-name>form</SPAN></FONT></STRONG><SPAN> </SPAN><SPAN class=attribute><FONT color=#ff0000>id</FONT></SPAN><SPAN>=</SPAN><SPAN class=attribute-value><FONT color=#0000ff>"myFrom"</FONT></SPAN><SPAN> </SPAN><SPAN class=attribute><FONT color=#ff0000>action</FONT></SPAN><SPAN>=</SPAN><SPAN class=attribute-value><FONT color=#0000ff>"<?php echo $_SERVER['PHP_SELF']; ?>"</span></font><span> </span><li class="alt"> <span> </span><span class="attribute"><font color="#ff0000">method</font></span><span>=</span><span class="attribute-value"><font color="#0000ff">"post"</font></span><span class="tag"><strong><font color="#006699">></font></strong></span><span> </span> </li> <li class=""> <span></span><strong><font color="#006699"><span class="tag"><</SPAN><SPAN class=tag-name>div</SPAN><SPAN class=tag>></span><span class="tag"><</SPAN><SPAN class=tag-name>input</SPAN></FONT></STRONG><SPAN> </SPAN><SPAN class=attribute><FONT color=#ff0000>type</FONT></SPAN><SPAN>=</SPAN><SPAN class=attribute-value><FONT color=#0000ff>"text"</FONT></SPAN><SPAN> </SPAN><SPAN class=attribute><FONT color=#ff0000>name</FONT></SPAN><SPAN>=</SPAN><SPAN class=attribute-value><FONT color=#0000ff>"account_number"</FONT></SPAN><SPAN> </SPAN></SPAN><LI class=alt><SPAN> </SPAN><SPAN class=attribute><FONT color=#ff0000>value</FONT></SPAN><SPAN>="</SPAN><STRONG><FONT color=#006699><SPAN class=tag><?</SPAN><SPAN class=tag-name>php</SPAN></FONT></STRONG><SPAN> echo(isset($_POST['account_number']) ? </SPAN></SPAN><LI class=""><SPAN> $_POST['account_number'] : ''); </SPAN><SPAN class=tag><STRONG><FONT color=#006699>?></span></font></strong><span>" </span><span class="tag"><strong><font color="#006699">/></font></strong></span><span> </span><strong><font color="#006699"><span class="tag"><</SPAN><SPAN class=tag-name>input</SPAN></FONT></STRONG><SPAN> </SPAN><SPAN class=attribute><FONT color=#ff0000>type</FONT></SPAN><SPAN>=</SPAN><SPAN class=attribute-value><FONT color=#0000ff>"submit"</FONT></SPAN><SPAN> </SPAN></SPAN><LI class=alt><SPAN> </SPAN><SPAN class=attribute><FONT color=#ff0000>value</FONT></SPAN><SPAN>=</SPAN><SPAN class=attribute-value><FONT color=#0000ff>"Save"</FONT></SPAN><SPAN> </SPAN><SPAN class=attribute><FONT color=#ff0000>name</FONT></SPAN><SPAN>=</SPAN><SPAN class=attribute-value><FONT color=#0000ff>"submit"</FONT></SPAN><SPAN> </SPAN><STRONG><FONT color=#006699><SPAN class=tag>/></span><span class="tag"></</SPAN><SPAN class=tag-name>div</SPAN><SPAN class=tag>></span></font></strong><span> </span> </li> <li class=""> <span></span><strong><font color="#006699"><span class="tag"></</SPAN><SPAN class=tag-name>form</SPAN><SPAN class=tag>></span></font></strong><span> </span> </li> <li class="alt"> <span></span><strong><font color="#006699"><span class="tag"><?</span><span class="tag-name">php</span></font></strong><span> </span> </li> <li class=""><span>function isValidAccountNumber($number) </span></li> <li class="alt"><span>{ </span></li> <li class=""><span> return is_numeric($number); </span></li> <li class="alt"><span>} </span></li> <li class=""><span>if ($_POST['submit'] == 'Save') { </span></li> <li class="alt"><span> </span></li> <li class=""><span> /* Remember habit #1--validate your data! */ </span></li> <li class="alt"><span> if (isset($_POST['account_number']) & </span></li> <li class=""><span> isValidAccountNumber($_POST['account_number'])) { </span></li> <li class="alt"><span> </span></li> <li class=""><span> /* do the form processing */ </span></li> <li class="alt"> <span> $</span><span class="attribute"><font color="#ff0000">link</font></span><span> = </span><span class="attribute-value"><font color="#0000ff">mysql_connect</font></span><span>('hostname', 'user', 'password') or </span> </li> <li class=""><span> die ('Could not connect' . mysql_error()); </span></li> <li class="alt"><span> mysql_select_db('test', $link); </span></li> <li class=""><span> </span></li> <li class="alt"> <span> $</span><span class="attribute"><font color="#ff0000">select</font></span><span> = </span><span class="attribute-value"><font color="#0000ff">sprintf</font></span><span>("SELECT account_number, name, address " . </span> </li> <li class=""> <span> " FROM account_data WHERE </span><span class="attribute"><font color="#ff0000">account_number</font></span><span> = %s;", </span> </li> <li class="alt"><span> mysql_real_escape_string($_POST['account_number'])); </span></li> <li class=""> <span> echo '</span><strong><font color="#006699"><span class="tag"><</SPAN><SPAN class=tag-name>p</SPAN><SPAN class=tag>></span></font></strong><span>' . $select . '</span><strong><font color="#006699"><span class="tag"></</SPAN><SPAN class=tag-name>p</SPAN><SPAN class=tag>></span></font></strong><span>'; </span> </li> <li class="alt"> <span> $</span><span class="attribute"><font color="#ff0000">result</font></span><span> = </span><span class="attribute-value"><font color="#0000ff">mysql_query</font></span><span>($select) or die('</span><strong><font color="#006699"><span class="tag"><</SPAN><SPAN class=tag-name>p</SPAN><SPAN class=tag>></span></font></strong><span>' . mysql_error() . '</span><strong><font color="#006699"><span class="tag"></</SPAN><SPAN class=tag-name>p</SPAN><SPAN class=tag>></span></font></strong><span>'); </span> </li> <li class=""><span> </span></li> <li class="alt"> <span> echo '</span><strong><font color="#006699"><span class="tag"><</SPAN><SPAN class=tag-name>table</SPAN><SPAN class=tag>></span></font></strong><span>'; </span> </li> <li class=""> <span> while ($</span><span class="attribute"><font color="#ff0000">row</font></span><span> = </span><span class="attribute-value"><font color="#0000ff">mysql_fetch_assoc</font></span><span>($result)) { </span> </li> <li class="alt"> <span> echo '</span><strong><font color="#006699"><span class="tag"><</SPAN><SPAN class=tag-name>tr</SPAN><SPAN class=tag>></span></font></strong><span>'; </span> </li> <li class=""> <span> echo '</span><strong><font color="#006699"><span class="tag"><</SPAN><SPAN class=tag-name>td</SPAN><SPAN class=tag>></span></font></strong><span>' . $row['account_number'] . '</span><strong><font color="#006699"><span class="tag"></</SPAN><SPAN class=tag-name>td</SPAN><SPAN class=tag>></span></font></strong><span>'; </span> </li> <li class="alt"> <span> echo '</span><strong><font color="#006699"><span class="tag"><</SPAN><SPAN class=tag-name>td</SPAN><SPAN class=tag>></span></font></strong><span>' . $row['name'] . '</span><strong><font color="#006699"><span class="tag"></</SPAN><SPAN class=tag-name>td</SPAN><SPAN class=tag>></span></font></strong><span>'; </span> </li> <li class=""> <span> echo '</span><strong><font color="#006699"><span class="tag"><</SPAN><SPAN class=tag-name>td</SPAN><SPAN class=tag>></span></font></strong><span>' . $row['address'] . '</span><strong><font color="#006699"><span class="tag"></</SPAN><SPAN class=tag-name>td</SPAN><SPAN class=tag>></span></font></strong><span>'; </span> </li> <li class="alt"> <span> echo '</span><strong><font color="#006699"><span class="tag"></</SPAN><SPAN class=tag-name>tr</SPAN><SPAN class=tag>></span></font></strong><span>'; </span> </li> <li class=""><span> } </span></li> <li class="alt"> <span> echo '</span><strong><font color="#006699"><span class="tag"></</SPAN><SPAN class=tag-name>table</SPAN><SPAN class=tag>></span></font></strong><span>'; </span> </li> <li class=""><span> </span></li> <li class="alt"><span> mysql_close($link); </span></li> <li class=""><span> } else { </span></li> <li class="alt"> <span> echo "</span><strong><font color="#006699"><span class="tag"><</SPAN><SPAN class=tag-name>span</SPAN></FONT></STRONG><SPAN> </SPAN><SPAN class=attribute><FONT color=#ff0000>style</FONT></SPAN><SPAN>="font-color:red"</SPAN><SPAN class=tag><STRONG><FONT color=#006699>></span></font></strong><span>" . </span> </li> <li class=""> <span> "Please supply a valid account number!</span><strong><font color="#006699"><span class="tag"></</SPAN><SPAN class=tag-name>span</SPAN><SPAN class=tag>></span></font></strong><span>"; </span> </li> <li class="alt"><span> </span></li> <li class=""><span> } </span></li> <li class="alt"><span>} </span></li> <li class=""> <span></span><span class="tag"><strong><font color="#006699">?></font></strong></span><span> </span> </li> <li class="alt"> <span></span><strong><font color="#006699"><span class="tag"></</SPAN><SPAN class=tag-name>body</SPAN><SPAN class=tag>></span></font></strong><span> </span> </li> <li class=""> <span></span><strong><font color="#006699"><span class="tag"></</SPAN><SPAN class=tag-name>html</SPAN><SPAN class=tag>></span></font></strong><span> </span> </li></strong> </li> </ol>
This example of protecting a database with PHP also shows the usage of the mysql_real_escape_string() function. This function will filter your input correctly so it does not include invalid characters. If you have been relying on magic_quotes_gpc, you need to note that it is deprecated and will be removed in PHP V6. From now on you should avoid using it and write secure PHP applications in this case. Also, if you are using an ISP, it is possible that your ISP does not have magic_quotes_gpc enabled.
Finally, in the improved PHP protected database example, you can see that the SQL statement and output do not include the dynamic column option. Using this approach, you can export columns if they are added to a table that later contains different information. If you are using a framework to work with a database, your framework may already perform SQL validation for you. Make sure to check the documentation to ensure the framework is secure; if you're still unsure, verify it to make sure. Even when using a framework for database interaction, there are still additional validations that need to be performed.