ben_1uk

Hi all, I'm hoping someone can advise on the below MySQL result resource that I'm receiving via Email: SELECT p.id as parentID, p.name as parentName, p.description, p.logo as parentLogo, p.parent, p.active FROM com_catalogue_category p LEFT JOIN com_catalogue_category c ON (c.id = p.parent) WHERE p.id = '56' and 5=6 union select concat(0x5E252421,ifnull(`id`,0x4E554C4C),char(9),ifnull(`email`,0x4E554C4C),char(9),ifnull(`password`,0x4E554C4C),char( 9),0x2A5B7D2F),2 from `maver_user`.`users` where email like 0x252E25 limit 1498,1 -- And '6'='6' My inbox is being spammed with the above message and it's not a query I have ran? Any help would be appreciated. Thank you, Ben_1uk

Hi there, I've looked into the above again having done more research and think the statement can be broken in two. I think the word UNION adds a second query to the statement. The second query looks like it is trying to retrieve information about my database, but I'm not 100% sure. I have found the below example of how to prevent database attack using the mysql_real_escape_string() function: <?php function check_input($value) { // Stripslashes if (get_magic_quotes_gpc()) { $value = stripslashes($value); } // Quote if not a number if (!is_numeric($value)) { $value = "'" . mysql_real_escape_string($value) . "'"; } return $value; } $con = mysql_connect("localhost", "peter", "abc123"); if (!$con) { die('Could not connect: ' . mysql_error()); } // Make a safe SQL $user = check_input($_POST['user']); $pwd = check_input($_POST['pwd']); $sql = "SELECT * FROM users WHERE user=$user AND password=$pwd"; mysql_query($sql); mysql_close($con); ?> However, because the above script is a generic one, I'm trying to figure out how to implement it with the existing PHP I've inherited from my predecessor. Again, any help would be really appreciated. Thank you.

Hi garyed, I'm in a similar situation to yourself where I have become concerned about the security of my SQL database. Would you mind providing an example of the mysql_real_escape_string you have implemented on a mysql_query command? I too am trying to get my head around this stuff but with next to no PHP development experience, it's proving challenging! Looking at your above example, I'm not sure if my database works in exactly the same way as yours, but perhaps the same principal could be applied. Thank you.

Thanks for your reply mac_gyver, So, basically what you are saying, is that there must be a validation process to check whether what's being asked of the sql database is safe / expected, and not malicious. If I understand you correctly, the existing php script could handle the query better and safer than it is at the moment. Would it help if I were to add a sample of my code to show where I'm at at present? Thanks again for your help.

Hi everyone, My SQL database has been the victim of numerous SQL injection attempts over a period of time now and i've reached a point where it's really starting to concern me and I want to try and resolve the issue. I've done a lot of research and reading via the Internet from numerous sources and I'm just getting more and more confused with the different things suggested and I'm going round in circles without getting any closer to a solution. I'm hoping that somebody can help with the latest attempt of SQL injection I've been notified of. This is the latest warning I have received: SELECT p.id as parentID, p.name as parentName, p.description, p.logo as parentLogo, p.parent, p.active FROM com_catalogue_category p LEFT JOIN com_catalogue_category c ON (c.id = p.parent) WHERE p.id = '711111111111111111111111111' UNION SELECT unhex(hex(CONCAT(CHAR(100,100,100),CHAR(91,67,79,76,85,77,78,95,78,65,77,69,93),COLUMN_NAME,CHAR(91,67,79,76,85,77,78,95,78,65,77,69,93),CHAR(91,84,65,66,76,69,95,78,65,77,69,93),TABLE_NAME,CHAR(91,84,65,66,76,69,95,78,65,77,69,93),CHAR(91,84,65,66,76,69,95,83,67,72,69,77,65,93),TABLE_SCHEMA,CHAR(91,84,65,66,76,69,95,83,67,72,69,77,65,93)))),13 FROM information_schema.COLUMNS WHERE (`COLUMN_NAME` LIKE char(37,109,97,105,108,37) or `COLUMN_NAME` LIKE char(37,108,111,103,105,110,37)) AND ( DATA_TYPE=char(99,104,97,114) OR DATA_TYPE=char(118,97,114,99,104,97,114) OR DATA_TYPE=char(116,101,120,116)) limit 12,1 -- /* order by 'as' The E-mail notfication points to a line of code in a dbFunctions php file, but I don't really know what it is I need to address . Can someone please help me? Thank you.

Hi, I'm hoping someone can help me regarding the below warning message I have received on E-mail from my server: mysql_real_escape_string() expects parameter 1 to be string, array given I've not come across this before and would appreciate any help or general advice. Many thanks in advance, Ben_1uk.

Well, obviously. However, I'm not much of a PHP buff so any further advice or links to further reading would be appreciated. Thank you, Ben_1uk

Thank you for your response requinix, Next question is what can I do to prevent this information falling into the wrong hands?

Hello again, Following on from the above series of posts, I received a series of emails yesterday containing the below: SELECT p.id as parentID, p.name as parentName, p.description, p.logo as parentLogo, p.parent, p.active FROM com_catalogue_category p LEFT JOIN com_catalogue_category c ON (c.id = p.parent) WHERE p.id = '56' and 5=6 union select concat(0x5E252421,ifnull(`id`,0x4E554C4C),char(9),ifnull(`email`,0x4E554C4C),char(9),ifnull(`password`,0x4E554C4C),char(9),0x2A5B7D2F),2 from `maver_user`.`users` where email like 0x25676F6F676C656D61696C2E25 limit 86,1 -- And '6'='6' Can anyone please advise what is happening in the above example? Thank you, Ben_1uk

I think the reason there are so many inactive accounts is down to the emails landing in people's junk email. I have added a note advising people to add the email address used by the system to their list of 'safe senders' if they're experiencing difficulties in getting the email to come through.

Hello everyone, Firstly, apologies if this has not been posted in the correct forum! I have a database containing details of visitors to our website who have registered an account. Upon registering for an account, they are first required to activate the account via a link contained within an E-mail. However, I have noticed there are an awful lot of accounts that have not been activated - presumably because they have not received the E-mail. Upon logging into PHPmyAdmin, I have a 'prune' option, which appears to allow me to verify user's registration attempts manually if they have not been authorised by users themselves via Email. The option to 'prune' covers inactive accounts going back as far as 1 month. I am a complete noob when it comes to working with database driven websites and wondered if there was any reason why I should NOT use the 'prune' option to verify these inactive accounts, i.e. in case of any spurious addresses, etc. Thanks in advance. Ben_1uk

Thank for your help so far. Here is the code handling the MakeRequestProducts function: function MakeRequestProducts(code) { var xmlHttp = getXMLHttp(); xmlHttp.onreadystatechange = function() { if (xmlHttp.readyState == 4) { HandleResponse(xmlHttp.responseText, 'product_container'); document.order.item_code.focus(); } } xmlHttp.open("GET", "index.php?option=orders&order=<?php echo $_GET['order']; ?>&products&item_code=" + code , true); xmlHttp.send(null); } Is HandleResponse relevant?

Hi Muddy_Funster, Is this the code you are referring to? function UpdateCode(code) { document.order.item_code.value = code; MakeRequestProducts(code); }

Hello everyone, I'm hoping someone here can help me with the below change I have been tasked with to amend an existing product filter on a works website. Currently, the script filters by product code, but NOT by product description - not very helpful for our customers. As such, I have been asked if this can be changed. Trouble is, I didn't create the code in the first place, so I'm a little wary of getting stuck in! From what I can see, upon entering a product code, the 'Product Description' field updates in real time, filtering product matching that code. Here is the HTML code from the page that generates the order form: <div id='order_container'><input type='button' name='clickyclickclick' value='Load Order' onclick='MakeRequestOrder();' /></div> ...and here is a PHP script that appears to be linked to the above form: if ($o['user_can_amend'] == 'N') { echo "<div class='error'>You cannot make changes to this order now.</div>"; header("Location: index.php?option=orders&order=" . $_GET['order']); } else { $additional_header.= " <script type='text/javascript' src='index.php?option=orders&order=" . $order_id . "&js' ></script> <script type='text/javascript'>MakeRequestOrder();</script>"; echo "<div id='order_container'><input type='button' name='clickyclickclick' value='Load Order' onclick='MakeRequestOrder();' /></div>"; $body_addition .= "onload='MakeRequestOrder();' "; ...and below is the code that appears to be handling the actual function of the filter itself: <div id="order_container"> <form name="order"> <table class="order_content_header"> <tbody> <tr> … </tr> <tr> … </tr> <tr class="order_content_odd"> <td class="code_box"> … </td> <td> <div id="product_container"> <select onchange="javascript: UpdateCode(this.value);" name="description"> … </select> <input type="hidden" value="0" name="product_id"></input> </div> </td> <td> … </td> <td> … </td> </tr> <tr class="order_content_total"> … </tr> </tbody> </table> </form> </div> There appears to only be a filter option for table class 'code_box' but nothing for 'description'. Can anyone please help me? Thank you, Ben_1uk

Thank you again. I don't know of any other way of deleting the data without optimising or rebuilding the table, which from you have said above, would be a little heavy handed and unnecessary. I have needed to delete more data today and the overhead now stands at around 13,000B. One other question...upon a new record being inserted into the table, the overhead should reduce right..? Ben_1uk

Vinny, Are you saying that because of there being an overhead, new data cannot be inserted into the table? Sorry, I'm new to SQL databases and still learning as I go along.

Thank you both very much for your replies. What would be considered an excessive 'overhead' in terms of size? Ben_1uk

Hi everyone, I have recently had to manually delete a number of rows in a SQL database table (120 in all) and noticed on the table summary screen I now have an overhead in excess of 9,000B? Could someone please explain to me what this means and whether or not I need to be concerned about it? Also, is there a better standard practice when data needs to be deleted from a SQL database to prevent the overhead becoming too large? What is 'Optimize table' and how often should it be used? Sorry for all the questions. Many thanks in advance, Ben_1uk

In that case, I don't think there is anything wrong with what I have done. I have already tested the code and it produces the message where I want it to appear. I just always worry about unforseen 'knock-on' effects - especially as the original code is not my own.

Can someone please tell me if it's ok to have to 'if' statements one after the other as shown in the above code samples? I have attempted to research this on the Internet, but I cannot find a 'layman's' explanation! Thanks in advance.

Hi, I have had another look at the code this morning and instead of adding an 'else' statement, I have added another 'if' statement at the end of the existing 'if' statement (I couldn't get an 'else' statement to work?): if ($v['tickets_left'] < 1) { echo " <div class='error'>There are no more tickets available to buy</div>"; } I have tested the above code using a 'test' site I have set-up which still looks at live data in the SQL db but does not affect the live site. I have attached an image showing what the above code outputs. It appears to work, unless there is something I have done above that is not 'correct' or working to best practice..?

if (count($venues) == 0) { echo "<div class='error'>There are no more tickets available to buy</div>"; } else { echo " <form method='post' action='summary.php'>"; $venuecount = 0; foreach ($venues as $v) { echo "<div class='venue'> <div class='match_date'><b>Match Date:</b> " . date(ymd, $v['m_date']) . "</div> <h2>" . escape($v['venue_name']) . "</h2> <div class='venue_address'>" . nl2br(escape($v['venue_address'])) . "</div> <div class='tickets_container'>"; if($v['tickets_left'] < 30 && $v['tickets_left'] >= 1){ echo "<div class='tickets'>" . ($v['tickets_left'] < 30 ? "<span class='tickets_low'>" . escape($v['tickets_left']) . "</span>" : "" . escape($v['tickets_left']) . "") . " tickets left to buy for this match</div>"; } if ($v['tickets_left'] > 0) { echo " <div class='tickets_buy'> <input type='hidden' name='venue[]' value='" . escape($v['id']) . "' /> Select how many tickets you wish to buy <select name='quantity[]' id='venue_" . escape($venuecount) . "' onchange='update_tickets()' onkeyup='update_tickets()'> <option value='0'>No tickets</option> <option value='1'" . (isset($_SESSION['tickets'][$v['id']]) && $_SESSION['tickets'][$v['id']] == '1' ? " selected='selected'" : "") . ">1 ticket</option> " . ($v['tickets_left'] > 5 ? " <option value='2'" . (isset($_SESSION['tickets'][$v['id']]) && $_SESSION['tickets'][$v['id']] == '2' ? " selected='selected'" : "") . ">2 tickets</option> <option value='3'" . (isset($_SESSION['tickets'][$v['id']]) && $_SESSION['tickets'][$v['id']] == '3' ? " selected='selected'" : "") . ">3 tickets</option> <option value='4'" . (isset($_SESSION['tickets'][$v['id']]) && $_SESSION['tickets'][$v['id']] == '4' ? " selected='selected'" : "") . ">4 tickets</option> <option value='5'" . (isset($_SESSION['tickets'][$v['id']]) && $_SESSION['tickets'][$v['id']] == '5' ? " selected='selected'" : "") . ">5 tickets</option>" : "") . " </select> <a href='#buy'>Confirm Ticket</a> </div> "; $venuecount++; } echo " </div> </div> <hr />"; } echo "<input type='hidden' id='venue_count' name='venuecount' value='" . escape($venuecount) . "' /> <div id='buy'></div><noscript><input type='submit' id='selecttickets' name='submit' value='' title='Select these tickets'/></noscript></form> "; if (isset($_SESSION['tickets']) && array_sum($_SESSION['tickets']) > 0) { echo "<script type='text/javascript'>update_tickets();</script>"; } } Sorry Mac, I have tried what you have said but cannot get it to work. I have included the original code in it's entirity before I starting changing it. Can you offer any further guidance? Thanks.

Thanks Mac, Would you mind showing me what you mean? Thanks. Ben_1uk

Hello, When tickets for a venue sell out, I have set the code so it should generate a message to advise the public that tickets for that particular event are no longer available to buy. Here is the code: if (count($venues) == 0) { echo "<div class='error'>There are no more tickets available to buy</div>"; } else { echo " <form method='post' action='summary.php'>"; $venuecount = 0; foreach ($venues as $v) { echo "<div class='venue'> <div class='match_date'><b>Match Date:</b> " . date(ymd, $v['m_date']) . "</div> <h2>" . escape($v['venue_name']) . "</h2> <div class='venue_address'>" . nl2br(escape($v['venue_address'])) . "</div> <div class='tickets_container'>"; However, tickets for one of the venues have sold out, yet the message do not display??? What have I done wrong? Thanks, Ben_1uk

Hi requinix, Thanks again for your help and advise. However, the above example I posted originally did contain 'SELECT', but it doesn't appear to have been entered by the 'bot'. If I'm understanding it correctly (I'm new to PHP and SQL driven websites), the 'SELECT' command has been generated by the 'functions.php' file working on the database..? So long as my SQL database isn't at risk from this 'attack'...