How can I prevent SQL injection in PHP? If user input is inserted without modification into an SQL query, then the application becomes vulnerable to SQL injection, like in the following example: $unsafe_variable = $_POST['user_input']; mysql_query("INSERT INTO `table` (`column`) VALUES ('$unsafe_variable')"); That's because the user can input something like value'); DROP TABLE table;--, and the query becomes: INSERT INTO `table` (`column`) VALUES('value'); DROP TABLE table;--') What can be done to prevent this from happening? Answer: Use prepared statements and parameterized queries. These are SQL statements that
undefined
MySQL/Apache Error in PHP MySQL query I am getting the following error: Access denied for user 'apache'@'localhost' (using password: NO) When using the following code: <?php include("../includes/connect.php"); $query = "SELECT * from story"; $result = mysql_query($query) or die(mysql_error()); echo "<h1>Delete Story</h1>"; if (mysql_num_rows($result) > 0) { while($row = mysql_fetch_row($result)){ echo '<b>'.$row[1].'</b><span align="right"><a href="../process/delete_story.php?id='.$row[0].'">Delete</a></span>'; echo '<br /><i>'.$row[2].'</i>'; } } else { echo "No stories available."; } ?> The connect.php file contains my MySQL connect calls that are working fine with my INSERT queries in
undefined
Is there any list datatype in MySQL stored procedures, or a way to emulate them? I would like to create a stored procedure in MySQL that took a list as argument. For example, say that I would like to be able to set multiple tags for an item in one call, then what I want to do is to define a procedure that takes the ID of the item and a list of tags to set. However, I can't seem to find any
undefined
Select all columns except one in MySQL? I'm trying to use a select statement to get all of the columns from a certain MySQL table except one. Is there a simple way to do this? EDIT: There are 53 columns in this table (NOT MY DESIGN) Answer: Actually there is a way, you need to have permissions of course for doing this ... SET @sql = CONCAT('SELECT ', (SELECT REPLACE(GROUP_CONCAT(COLUMN_NAME), '<columns_to_omit>,', '') FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '<table>' AND TABLE_SCHEMA = '<database>'), '
undefined
Bidirectional outer JOIN Suppose we have a table A: itemid mark 1 5 2 3 and table B: itemid mark 1 3 3 5 I want to join A*B on A.itemid=B.itemid both right and left ways. i.e. result: itemid A.mark B.mark 1 5 3 2 3 NULL 3 NULL 5 Is there a way to do it in one query in MySQL? Best answer : It's called a full outer join and it's not supported natively in MySQL, judging from its docs. You can
undefined
Throw an error in a MySQL trigger If I have a trigger before the update on a table, how can I throw an error that prevents the update on that table? answer : Here is one hack that may work. It isn't clean, but it looks like it might work: Essentially, you just try to update a column that doesn't exist. Source :http://stackoverflow.com/questions/24/throw-an-error-in-a-mysql-trigger
undefined
Binary Data in MySQL How do I store binary data in MySQL? Answer : The answer by phpguy is correct but I think there is a lot of confusion in the additional details there. The basic answer is in a BLOB data type / attribute domain. BLOB is short for Binary Large Object and that column data type is specific for handling binary data. See the relevant manual page for MySQL. source :http://stackoverflow.com/questions/17/binary-data-in-mysql