7 semi-noob questions

[kkiely]

I am using MySQL - 4.1.15 & PHP 4 & reading Visual QuickPro PHP/mySQL.

1. About mysql security - when I set up my PHP forms to write to text files I use the PHP functions to strip about any bad characters. For example, $Location=htmlspecialchars(stripslashes($Location)); . Is there a mySQL equivalent? Right now I have them formatted like $city=$_POST['city'];

2. This could be related to the last question. Whenever I enter data thru my forms and the data has brackets ("< >")wrapped around it the brackets get stripped out. Why is this?

3. Is it recommended to keep your mySQL username and password in a separate file outide of your web directory?

4. Is there anything wrong with insert 2 different inputs from a form into 1 column with a query like this? $query = "INSERT INTO listingsgames VALUES ('$tourneydate3-$tourneydate-$tourneydate2')?

5. Is there a way to create a query to move a record to another table then delete the record in the originial table? Someone on google recommended this but it doesnt work.

$query="UPDATE listingsgames SET deletestatus = 'yes' WHERE gameid = '$variable2'";
"INSERT listingsgamesdeleted (gameid, deletestatus) SELECT gameid, deletestatus FROM listingsgames WHERE deletestatus = 'yes'";
"T1 = SELECT COUNT(*) from listingsgames WHERE deletestatus = 'yes'";
"T2 = SELECT COUNT(*) from listingsgamesdeleted WHERE deletestatus = 'yes'";
"if ( T1 == T2 ) DELETE FROM listingsgames WHERE deletestatus = 'yes'";
"UPDATE listingsgamesdeleted SET deletestatus = 'no'";

After my investigating into the subject, I get the impression that I might have have to use a "transaction" which would require me to change the table type to "innoDB" (which i hear are slower). If this is true would it slow the queries down if I have 25,000 records and am doing 80% SELECT queries and 20% INSERT queries? Are there any other downsides - other than foreign keys - to have an innoDB table type instead of MYISAM?

6. I have a field where I would prefer to give people about 700 characters to enter information. Would having the column type as "text(700)" be much slower than "varchar(255)". I would have about 15,000 records.


7. I have people enter information on my site and I want them to be able to edit it. But, for now, I don't want to set up usernames and passwords. My idea is that if they want to change their listing then I have them enter their email into a form and the form emails them an address like mysite.com/editlisting.php?email=user@yahoo.com&pw=65489215469841354456 where the "pw" variable is a random number I generate with each record creation. Are there any security problems with this?

thanks for your time

 

[TonyGroves]

1. You might be able to use the REPLACE function - SET city = REPLACE("$city","/","") . However, if there is more than just slashes to be stripped, that approach gets very ugly; you would need to use program code instead.

2. That looks like a HTML-stripping process, for security reasons. Not a MySQL issue.

3. Do you mean the username/password used by your PHP programs? As long as the file is not world-readable you should be OK.

4. It's very bad practice. It makes it very difficult for queries to process that field, and makes it impossible for the field to be usefully indexed.

5. I don't understand your code. It's quite simple:[tt]
INSERT t2 SELECT * FROM t1 WHERE id=123;
DELETE FROM t2 WHERE id=123;[/tt]
You don't need to use a transaction. Table-locking should be enough.

6. In MySQL 4.1, VARCHAR is limited to 255 characters, so you would need to use TEXT (not "TEXT(700)") which allows up to 65535 characters.

7. That's standard practice on many sites. It guarantees that the address is genuine and belongs to the user. However, as with all e-mail, the message can be snooped.

 

[r937]

1. no mysql equivalent

2. error in your php script

4. syntactically, no -- semantically, probably

5. not a single query, no

two questions: why do you feel the need to move rows to another table? why not just leave them where they are with a deleted status?

and where in the world did you hear that foreign keys are a "downside"???

6. much slower? not at all -- and note that in 5.0.3 and later, you can use VARCHAR up to 65K

7. yes, it's extra coding, and it uses a password which you said you didn't want to use

r937.com | rudy.ca