{"id":2683,"date":"2010-05-19T10:00:01","date_gmt":"2010-05-19T08:00:01","guid":{"rendered":"http:\/\/localhost\/helpcentre\/?p=2683"},"modified":"2022-11-15T13:38:45","modified_gmt":"2022-11-15T11:38:45","slug":"secure-your-database","status":"publish","type":"post","link":"https:\/\/xneelo.co.za\/help-centre\/website\/secure-your-database\/","title":{"rendered":"How to secure your database"},"content":{"rendered":"

There are scenarios where databases can become vulnerable to hackers, for example, taking raw data and inserting it into a database table creates a security vulnerability called SQL<\/strong>\u00a0injection<\/b>. These situations can be prevented by securing scripts and database statements.<\/p>\n

SQL<\/span> injection is done without the administrator\u2019s knowledge or permission by inserting a database statement into the database. An example of how this is done; when requesting user input i.e. \u2018customer id\u2019, instead of providing this information the hacker inserts a database statement that is then executed without you being aware.<\/p>\n

Example:<\/b><\/p>\n

Here is an example of a string displaying the difference between regular interaction and SQL<\/span> injection; this allows the hacker to gain access to records:<\/p>\n

The user is requested to provide their customer pin, this is interpreted into a SELECT statement providing the necessary information.<\/p>\n


\n\/\/ regular interaction customer pin
\n<\/span>$pin <\/span>= <\/span>\"12345\"<\/span>;
\n<\/span>$query <\/span>= <\/span>\"SELECT * FROM customers WHERE pin = '<\/span>$number<\/span>\"<\/span>;
\necho <\/span>\"Normal: \" <\/span>. <\/span>$query <\/span>. <\/span>\"<br \/>\"<\/span>;<\/span><\/span><\/code><\/code>\/\/ SQL Injection
\n<\/span>$pin_bad <\/span>= <\/span>\"' OR 1'\"<\/span>;<\/span><\/span><\/code><\/code>\/\/ MySQL query builder \u2013 not very secure
\n<\/span>$query_bad <\/span>= <\/span>\"SELECT * FROM customers WHERE pin = '<\/span>$pin_bad<\/span>'\"<\/span>;<\/span><\/span><\/code><\/code>\/\/ show the query with injection
\n<\/span>echo <\/span>\"Injection: \" <\/span>. <\/span>$query_bad<\/span>; <\/span>
\n<\/span>
\n<\/code><\/p>\n<\/div>\n

Display:<\/b><\/p>\n


\nNormal<\/span>: <\/span>SELECT <\/span>* <\/span>FROM customers WHERE pin <\/span>= <\/span>'12345'
\n<\/span>Injection<\/span>: <\/span>SELECT <\/span>* <\/span>FROM customers WHERE pin <\/span>= <\/span>'' <\/span>OR <\/span>1<\/span>'' <\/span>
\n<\/span>
\n<\/code><\/div>\n

The regular interaction does not create a problem, given that the database statement will choose information from customers that have a pin equivalent to 12345.<\/p>\n

The SQL<\/span> injection caused the query to behave in a way that was not intended via a single quote (\u2019) the string part of the database query was brought to an end.<\/p>\n


\npin <\/span>= <\/span>' '
\n<\/span>and <\/span>then added on to our WHERE statement with an <\/span>OR <\/span>clause of 1 <\/span>(<\/span>always true<\/span>).
\n<\/span>pin <\/span>= <\/span>' ' <\/span>OR <\/span>1 <\/span>
\n<\/span>
\n<\/code><\/div>\n

All<\/b> entries in the \u201ccustomers\u201d table selected as a result of this statement because OR clause of 1 is true.<\/p>\n

Example 2:<\/b><\/p>\n

DELETE statement: Below is an example of where a hacker can remove all information from the \u201ccustomers\u201d table.<\/p>\n

$id_evil = \u201c\u2018; DELETE FROM customers WHERE 1 or userid = \u2018\u201c;<\/p>\n


\n\/\/ SQL injection to be detected by the MySQL query builder
\n<\/span>$query_evil <\/span>= <\/span>\"SELECT * FROM customers WHERE userid = '<\/span>$id_evil<\/span>'\"<\/span>;<\/span><\/span><\/code><\/code>\/\/ DELETE statement should form part of the new evil injection query
\n<\/span>echo <\/span>\"Injection: \" <\/span>. <\/span>$query_evil<\/span>; <\/span>
\n<\/span>
\n<\/code><\/div>\n

Display:<\/b><\/p>\n


\nSELECT <\/span>* <\/span>FROM customers WHERE userid <\/span>= <\/span>' '<\/span>;
\n<\/span>DELETE FROM customers WHERE 1 <\/span>or <\/span>userid <\/span>= <\/span>' ' <\/span>
\n<\/span>
\n<\/code><\/div>\n

Prevention:<\/b><\/p>\n

PHP has a function to assist in the prevention of this known problem: mysql_real_escape_string. This function acts by replacing the (\u2019) quotes safe alternative i.e. (\u2019) known as an escaped quote.
\nThe example below demonstrates how the function can be used to prevent example 1 and 2:<\/p>\n


\n\/\/Note: To use the function please ensure you are connected to your database.<\/span><\/span><\/code><\/code>$id_bad <\/span>= <\/span>\"' OR 1'\"<\/span>;<\/span><\/span><\/code><\/code>$id_bad <\/span>= <\/span>mysql_real_escape_string<\/span>(<\/span>$id_bad<\/span>);<\/span><\/span><\/code><\/code>$query_bad <\/span>= <\/span>\"SELECT * FROM customers WHERE userid = '<\/span>$id_bad<\/span>'\"<\/span>;
\necho <\/span>\"Escaped Bad Injection: <br \/>\" <\/span>. <\/span>$query_bad <\/span>. <\/span>\"<br \/>\"<\/span>;<\/span><\/span><\/code><\/code><\/p>\n

$id_evil <\/span>= <\/span>\"'; DELETE FROM customers WHERE 1 or userid = '\"<\/span>;<\/span><\/span><\/code><\/code><\/p>\n

$id_evil <\/span>= <\/span>mysql_real_escape_string<\/span>(<\/span>$id_evil<\/span>);<\/span><\/span><\/code><\/code><\/p>\n

$query_evil <\/span>= <\/span>\"SELECT * FROM customers WHERE userid = '<\/span>$id_evil<\/span>'\"<\/span>;
\necho <\/span>\"Escaped Evil Injection: <br \/>\" <\/span>. <\/span>$query_evil<\/span>; <\/span>
\n<\/span>
\n<\/code><\/p>\n<\/div>\n

Display:<\/b><\/p>\n


\nEscaped Bad Injection<\/span>:
\n<\/span>SELECT <\/span>* <\/span>FROM customers WHERE userid <\/span>= <\/span>'' <\/span>OR <\/span>1<\/span>''
\n<\/span>Escaped Evil Injection<\/span>:
\n<\/span>SELECT <\/span>* <\/span>FROM customers WHERE userid <\/span>= <\/span>''<\/span>;
\n<\/span>DELETE FROM customers WHERE 1 <\/span>or <\/span>userid <\/span>= <\/span>'' <\/span>
\n<\/span>
\n<\/code><\/div>\n

The SQL<\/span> injection attack has been prevented i.e. the backslash ensures that the evil quotes have been escaped and the remaining queries will be looking for a nonsensical userid:<\/p>\n


\nBad<\/span>: <\/span>' OR 1'
\n<\/span>Evil<\/span>: <\/span>'; DELETE FROM customers WHERE 1 or userid = ' <\/span>
\n<\/span>
\n<\/code><\/div>\n","protected":false,"plain":"There are scenarios where databases can become vulnerable to hackers, for example, taking raw data and inserting it into a database table creates a security vulnerability called SQL<\/strong>\u00a0injection<\/b>. These situations can be prevented by securing scripts and database statements.\r\n\r\nSQL<\/span> injection is done without the administrator\u2019s knowledge or permission by inserting a database statement into the database. An example of how this is done; when requesting user input i.e. \u2018customer id\u2019, instead of providing this information the hacker inserts a database statement that is then executed without you being aware.\r\n\r\nExample:<\/b>\r\n\r\nHere is an example of a string displaying the difference between regular interaction and SQL<\/span> injection; this allows the hacker to gain access to records:\r\n\r\nThe user is requested to provide their customer pin, this is interpreted into a SELECT statement providing the necessary information.\r\n
\r\n\/\/ regular interaction customer pin\r\n<\/span>$pin <\/span>= <\/span>\"12345\"<\/span>;\r\n<\/span>$query <\/span>= <\/span>\"SELECT * FROM customers WHERE pin = '<\/span>$number<\/span>\"<\/span>;\r\necho <\/span>\"Normal: \" <\/span>. <\/span>$query <\/span>. <\/span>\"<br \/>\"<\/span>;<\/span><\/span><\/code><\/code>\/\/ SQL Injection\r\n<\/span>$pin_bad <\/span>= <\/span>\"' OR 1'\"<\/span>;<\/span><\/span><\/code><\/code>\/\/ MySQL query builder \u2013 not very secure\r\n<\/span>$query_bad <\/span>= <\/span>\"SELECT * FROM customers WHERE pin = '<\/span>$pin_bad<\/span>'\"<\/span>;<\/span><\/span><\/code><\/code>\/\/ show the query with injection\r\n<\/span>echo <\/span>\"Injection: \" <\/span>. <\/span>$query_bad<\/span>; <\/span>\r\n<\/span>\r\n<\/code>\r\n\r\n<\/div>\r\nDisplay:<\/b>\r\n
\r\nNormal<\/span>: <\/span>SELECT <\/span>* <\/span>FROM customers WHERE pin <\/span>= <\/span>'12345'\r\n<\/span>Injection<\/span>: <\/span>SELECT <\/span>* <\/span>FROM customers WHERE pin <\/span>= <\/span>'' <\/span>OR <\/span>1<\/span>'' <\/span>\r\n<\/span>\r\n<\/code><\/div>\r\nThe regular interaction does not create a problem, given that the database statement will choose information from customers that have a pin equivalent to 12345.\r\n\r\nThe SQL<\/span> injection caused the query to behave in a way that was not intended via a single quote (\u2019) the string part of the database query was brought to an end.\r\n
\r\npin <\/span>= <\/span>' '\r\n<\/span>and <\/span>then added on to our WHERE statement with an <\/span>OR <\/span>clause of 1 <\/span>(<\/span>always true<\/span>).\r\n<\/span>pin <\/span>= <\/span>' ' <\/span>OR <\/span>1 <\/span>\r\n<\/span>\r\n<\/code><\/div>\r\nAll<\/b> entries in the \u201ccustomers\u201d table selected as a result of this statement because OR clause of 1 is true.\r\n\r\nExample 2:<\/b>\r\n\r\nDELETE statement: Below is an example of where a hacker can remove all information from the \u201ccustomers\u201d table.\r\n\r\n$id_evil = \u201c\u2018; DELETE FROM customers WHERE 1 or userid = \u2018\u201c;\r\n
\r\n\/\/ SQL injection to be detected by the MySQL query builder\r\n<\/span>$query_evil <\/span>= <\/span>\"SELECT * FROM customers WHERE userid = '<\/span>$id_evil<\/span>'\"<\/span>;<\/span><\/span><\/code><\/code>\/\/ DELETE statement should form part of the new evil injection query\r\n<\/span>echo <\/span>\"Injection: \" <\/span>. <\/span>$query_evil<\/span>; <\/span>\r\n<\/span>\r\n<\/code><\/div>\r\nDisplay:<\/b>\r\n
\r\nSELECT <\/span>* <\/span>FROM customers WHERE userid <\/span>= <\/span>' '<\/span>;\r\n<\/span>DELETE FROM customers WHERE 1 <\/span>or <\/span>userid <\/span>= <\/span>' ' <\/span>\r\n<\/span>\r\n<\/code><\/div>\r\nPrevention:<\/b>\r\n\r\nPHP has a function to assist in the prevention of this known problem: mysql_real_escape_string. This function acts by replacing the (\u2019) quotes safe alternative i.e. (\u2019) known as an escaped quote.\r\nThe example below demonstrates how the function can be used to prevent example 1 and 2:\r\n
\r\n\/\/Note: To use the function please ensure you are connected to your database.<\/span><\/span><\/code><\/code>$id_bad <\/span>= <\/span>\"' OR 1'\"<\/span>;<\/span><\/span><\/code><\/code>$id_bad <\/span>= <\/span>mysql_real_escape_string<\/span>(<\/span>$id_bad<\/span>);<\/span><\/span><\/code><\/code>$query_bad <\/span>= <\/span>\"SELECT * FROM customers WHERE userid = '<\/span>$id_bad<\/span>'\"<\/span>;\r\necho <\/span>\"Escaped Bad Injection: <br \/>\" <\/span>. <\/span>$query_bad <\/span>. <\/span>\"<br \/>\"<\/span>;<\/span><\/span><\/code><\/code>\r\n\r\n$id_evil <\/span>= <\/span>\"'; DELETE FROM customers WHERE 1 or userid = '\"<\/span>;<\/span><\/span><\/code><\/code>\r\n\r\n$id_evil <\/span>= <\/span>mysql_real_escape_string<\/span>(<\/span>$id_evil<\/span>);<\/span><\/span><\/code><\/code>\r\n\r\n$query_evil <\/span>= <\/span>\"SELECT * FROM customers WHERE userid = '<\/span>$id_evil<\/span>'\"<\/span>;\r\necho <\/span>\"Escaped Evil Injection: <br \/>\" <\/span>. <\/span>$query_evil<\/span>; <\/span>\r\n<\/span>\r\n<\/code>\r\n\r\n<\/div>\r\nDisplay:<\/b>\r\n
\r\nEscaped Bad Injection<\/span>:\r\n<\/span>SELECT <\/span>* <\/span>FROM customers WHERE userid <\/span>= <\/span>'' <\/span>OR <\/span>1<\/span>''\r\n<\/span>Escaped Evil Injection<\/span>:\r\n<\/span>SELECT <\/span>* <\/span>FROM customers WHERE userid <\/span>= <\/span>''<\/span>;\r\n<\/span>DELETE FROM customers WHERE 1 <\/span>or <\/span>userid <\/span>= <\/span>'' <\/span>\r\n<\/span>\r\n<\/code><\/div>\r\nThe SQL<\/span> injection attack has been prevented i.e. the backslash ensures that the evil quotes have been escaped and the remaining queries will be looking for a nonsensical userid:\r\n
\r\nBad<\/span>: <\/span>' OR 1'\r\n<\/span>Evil<\/span>: <\/span>'; DELETE FROM customers WHERE 1 or userid = ' <\/span>\r\n<\/span>\r\n<\/code><\/div>"},"excerpt":{"rendered":"

There are scenarios where databases can become vulnerable to hackers. These situations can be prevented by securing scripts and MySQL statements.<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"lsx_disable_title":"0","_relevanssi_hide_post":"","_relevanssi_hide_content":"","_relevanssi_pin_for_all":"","_relevanssi_pin_keywords":"","_relevanssi_unpin_keywords":"","_relevanssi_related_keywords":"","_relevanssi_related_include_ids":"","_relevanssi_related_exclude_ids":"","_relevanssi_related_no_append":"","_relevanssi_related_not_related":"","_relevanssi_related_posts":"","_relevanssi_noindex_reason":"","footnotes":""},"categories":[168,180,166,188],"tags":[18336,55],"topics":[10405,10377],"class_list":["post-2683","post","type-post","status-publish","format-standard","hentry","category-managing-website","category-mysql","category-website","category-website-security","tag-sql-injection","tag-website_security","topics-databases","topics-website-security"],"acf":[],"additional_meta":{"category_title":[{"term_id":168,"name":"Managing your Website","slug":"managing-website","term_group":0,"term_taxonomy_id":168,"taxonomy":"category","description":"Managing your Website","parent":166,"count":52,"filter":"raw","term_order":"83","cat_ID":168,"category_count":52,"category_description":"Managing your Website","cat_name":"Managing your Website","category_nicename":"managing-website","category_parent":166},{"term_id":180,"name":"MySQL","slug":"mysql","term_group":0,"term_taxonomy_id":180,"taxonomy":"category","description":"Using MySQL for web applications ","parent":168,"count":9,"filter":"raw","term_order":"92","cat_ID":180,"category_count":9,"category_description":"Using MySQL for web applications ","cat_name":"MySQL","category_nicename":"mysql","category_parent":168},{"term_id":166,"name":"Website","slug":"website","term_group":0,"term_taxonomy_id":166,"taxonomy":"category","description":"About your Website(s)","parent":0,"count":169,"filter":"raw","term_order":"120","cat_ID":166,"category_count":169,"category_description":"About your Website(s)","cat_name":"Website","category_nicename":"website","category_parent":0},{"term_id":188,"name":"Website Security","slug":"website-security","term_group":0,"term_taxonomy_id":188,"taxonomy":"category","description":"Securing your website","parent":168,"count":15,"filter":"raw","term_order":"122","cat_ID":188,"category_count":15,"category_description":"Securing your website","cat_name":"Website Security","category_nicename":"website-security","category_parent":168}],"tag_title":[{"term_id":18336,"name":"SQL injection","slug":"sql-injection","term_group":0,"term_taxonomy_id":18336,"taxonomy":"post_tag","description":"","parent":0,"count":1,"filter":"raw","term_order":"2022"},{"term_id":55,"name":"Website Security","slug":"website_security","term_group":0,"term_taxonomy_id":55,"taxonomy":"post_tag","description":"","parent":0,"count":5,"filter":"raw","term_order":"3037"}]},"featured_image_src":null,"author_info":{"display_name":"marketing","author_link":"https:\/\/xneelo.co.za\/help-centre\/author\/marketing\/","author_avatar":"https:\/\/secure.gravatar.com\/avatar\/a6ea315e112423b2b955cb020fbce2b0835956c6ad85ff0f13f1db298977eaaa?s=96&d=mm&r=g"},"_links":{"self":[{"href":"https:\/\/xneelo.co.za\/help-centre\/wp-json\/wp\/v2\/posts\/2683","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/xneelo.co.za\/help-centre\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/xneelo.co.za\/help-centre\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/xneelo.co.za\/help-centre\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/xneelo.co.za\/help-centre\/wp-json\/wp\/v2\/comments?post=2683"}],"version-history":[{"count":0,"href":"https:\/\/xneelo.co.za\/help-centre\/wp-json\/wp\/v2\/posts\/2683\/revisions"}],"wp:attachment":[{"href":"https:\/\/xneelo.co.za\/help-centre\/wp-json\/wp\/v2\/media?parent=2683"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/xneelo.co.za\/help-centre\/wp-json\/wp\/v2\/categories?post=2683"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/xneelo.co.za\/help-centre\/wp-json\/wp\/v2\/tags?post=2683"},{"taxonomy":"topics","embeddable":true,"href":"https:\/\/xneelo.co.za\/help-centre\/wp-json\/wp\/v2\/topics?post=2683"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}