'select all' selects all NOOOOOOO!

[black-dog]

Originally Posted by perplexed

At first i thought this is insane as i pasted queries from the internal interface after using it's search/select facility and everything failed. To make matters worse was the bizzarre wording referring to terms i have never seen in php queries such as latin1_swedish_ci etc, also the operatives CONVERT and USING were inserted into the query - example below. It totally threw me.

SELECT *
FROM `table`
WHERE `SONG` LIKE CONVERT( _utf8 'love'
USING latin1 )
COLLATE latin1_swedish_ci
AND `ARTIST` LIKE CONVERT( _utf8 'beatles'
USING latin1 )
COLLATE latin1_swedish_ci
LIMIT 0 , 30

Anyway, once i catch up with things i have to do and get some sleep, i'll report back tomorrow with the solutions. It's a right old mix of removing quotes etc. and leaving some in there.

It really isn't that difficult.
phpMyAdmin is rather pedantic and you can kick most of the stuff out

SELECT *
FROM `table`
WHERE `SONG` LIKE 'love'
AND `ARTIST` LIKE 'beatles'

will be fine. In fact you don't even need the backticks around SONG and ARTIST. Please note though, that the wildcards are missing in this example.

[perplexed]

To wrap this up i'll clarify what has worked.

The simple php statement that i used for so many years will no longer work. Instead the following does work.

PHP Code:

$a=mysql_real_escape_string($_POST['a']);
$b=mysql_real_escape_string($_POST['b']);
$c=mysql_real_escape_string($_POST['c']);
$sql=("SELECT * FROM table WHERE $a LIKE '%$b%' ORDER BY $c"); 


If you glance back at the webpage form i displayed, the key coding points in the above is that the 'radio' button options a & c are written plain whilst the form field has single quotes around it. Also, the 'order by' is written without quotes. No other combination worked at all.

PHP Code:

$d=mysql_real_escape_string($_POST['d']);
$e=mysql_real_escape_string($_POST['e']);
$f=mysql_real_escape_string($_POST['f']);
$g=mysql_real_escape_string($_POST['g']);
$h=mysql_real_escape_string($_POST['h']);
$sql=("select * from table WHERE $d  LIKE '%$e%' AND $f like '%$g%' ORDER BY $h"); 


In the additional example above the same thing goes, radio buttons d & f are plain and both the form fields e & g are single quoted, with again the 'order by' being plain.

Incidentally, when i said i had forgotten the great feeling of getting code to work after so much struggle, i wasn't meaning the code in question was a brainstretcher, it was the fact nothing would work. I have devised code in the past for complex uses with no problem.

Now that the Plesk interface is running ok, i will be able to sort out some upcoming problems without assistance. I'm pretty sure i've still got things to work out because i have web pages that i can place multiple new entries into my database, and also have an editing page where i search and select records on criteria and then edit them and send back to the database in their updated form.

Where the problem will arise is that currently i have had to use the ADDSLASHES feature where commas appear in a song title, for instance, as in 'She's Lost Control'. With this new code i used i removed the STRIPSLASHES when running searches on the database, but, when adding records i presume the comma issue will need to be escaped in some way.

Many thanks for assistance in solving this matter.

[jon123]

As far as non alphanumeric characters i use addslashes for inserting data into the database, and stripslashes on printing. (but also do use mysql_real_escape_string occasionally)

As far as your query, that has me a little confused! black-dog will know best but unless the data is numeric, then it should have single quotes? Maybe i'm wrong, probably am as your query does work.

i wonder though whether it is an issue with the $_POST superglobal? I always double quote my vars: $var=addslashes($_POST["var"]); although i know it doesn't really matter as either can be used.

Umm, would be interested in knowing. I must admit moving on to php5 from php4 and the latest version of mysql really gave me strange results AND errors with some of my queries, took a while to figure out what was going on, especially with vars containing just numbers, where originally it didn't matter if the $var in question had single quotes around it, to now throwing errors if single quotes are used!

[black-dog]

Originally Posted by jon123

As far as non alphanumeric characters i use addslashes for inserting data into the database, and stripslashes on printing. (but also do use mysql_real_escape_string occasionally)

I don't see any point in using addslashes especially if you haven't checked that magic_quotes is off. If magic quotes is on (and in my version of PHP it is) slashes will be added anyway. I use mysql_real_escape_string for text or maybe something like is_int or is_numeric for numeric values. The important thing is not to trust the data and make sure it is safe to insert into your database.

Originally Posted by jon123

As far as your query, that has me a little confused! black-dog will know best but unless the data is numeric, then it should have single quotes? Maybe i'm wrong, probably am as your query does work.

That's right, but that's what he has. The columns are not quoted which is correct (but you'd need backticks if you were using any reserved words for columns)

Originally Posted by jon123

i wonder though whether it is an issue with the $_POST superglobal? I always double quote my vars: $var=addslashes($_POST["var"]); although i know it doesn't really matter as either can be used.

Umm, would be interested in knowing. I must admit moving on to php5 from php4 and the latest version of mysql really gave me strange results AND errors with some of my queries, took a while to figure out what was going on, especially with vars containing just numbers, where originally it didn't matter if the $var in question had single quotes around it, to now throwing errors if single quotes are used!

His issue was the change to register_globals=off, pure and simple. Sadly, I suspect he introduced other errors while trying to fix the script. I always use single quotes for array elements to avoid (my) confusion. The use of quotes in SQL queries is a different issue altogether.

There are differences elsewhere in PHP between single and double quotes to do with interpolation of variables Loads more here PHP: Strings - Manual

[jon123]

Thanks for clearing that up, i was a little confused as have mentioned.

I have always used addslashes but reading your reply i will check the status of magic_quotes.

thx again

[perplexed]

Originally Posted by black-dog

His issue was the change to register_globals=off, pure and simple. Sadly, I suspect he introduced other errors while trying to fix the script. I always use single quotes for array elements to avoid (my) confusion. The use of quotes in SQL queries is a different issue altogether.

There are differences elsewhere in PHP between single and double quotes to do with interpolation of variables Loads more here PHP: Strings - Manual

Clearly when with my previous host the register_globals were switched on

[black-dog]

Originally Posted by perplexed

Clearly when with my previous host the register_globals were switched on

Like I've said, the change has been on the cards for a few years. Your previous host would inevitably change sooner or later

[perplexed]

Another good news update. I've cleared what could have been a couple of major hurdles without difficulty.

I have a web page form where i can enter 20 new records into the DB containing 5 fields. I had two fears over it.

The first being that when inserting a song name containing a comma could be potentially problematic. I left the 'stripslashes' instruction in from the original script and it works. Too be honest, i'm a little puzzled - it's been many years since i constructed the original scripts and have forgotten so much, but, i would have thought when one inserts into a database one would use the 'addslashes'?. Anyway, it works.

Secondly, the first (hidden on the web page) field of each entry is a blank '()' to allow the DB to auto-increment the ID number to each new record. Fortunately it has worked fine and didn't trigger any errors.

Only slight negative was the laborious typing in of extra code with the addition of

PHP Code:

$x=mysql_real_escape_string($_POST['x']); 


which meant that with twenty form records each containing four fields added 80 extra lines of code. Each one had to be unique as well. I was able to copy and paste most of the liine but it still was time-consuming to enter the relevant tags.

I have one more big test to come and if that is carried out successfully all php problems created by moving from my old site/version are cured.

I have a web page where i can enter search terms and call up one or several records, i can then edit any part of the record/s and update them back to the DB. Maybe, that is where my 'addslashes' has got to lol?.

[black-dog]

Originally Posted by perplexed

Another good news update. I've cleared what could have been a couple of major hurdles without difficulty.

I have a web page form where i can enter 20 new records into the DB containing 5 fields. I had two fears over it.

The first being that when inserting a song name containing a comma could be potentially problematic. I left the 'stripslashes' instruction in from the original script and it works. Too be honest, i'm a little puzzled - it's been many years since i constructed the original scripts and have forgotten so much, but, i would have thought when one inserts into a database one would use the 'addslashes'?. Anyway, it works.

Well yes, that's what the 'escape' part is all about.

Originally Posted by perplexed

Secondly, the first (hidden on the web page) field of each entry is a blank '()' to allow the DB to auto-increment the ID number to each new record. Fortunately it has worked fine and didn't trigger any errors.

You don't need a hidden field. In fact I'd actively encourage you to remove it. Just remove it from the insert query. If the field is auto-increment, it will still work

Originally Posted by perplexed

Only slight negative was the laborious typing in of extra code with the addition of

PHP Code:

$x=mysql_real_escape_string($_POST['x']); 


which meant that with twenty form records each containing four fields added 80 extra lines of code. Each one had to be unique as well. I was able to copy and paste most of the liine but it still was time-consuming to enter the relevant tags.

When performing a repetitive task I always think 'loops'. You could have saved a lot of effort by doing:

PHP Code:

foreach($_POST as $key=>$value){
$$key=mysql_real_escape_string($value);
} 


Originally Posted by perplexed

I have one more big test to come and if that is carried out successfully all php problems created by moving from my old site/version are cured.

I have a web page where i can enter search terms and call up one or several records, i can then edit any part of the record/s and update them back to the DB. Maybe, that is where my 'addslashes' has got to lol?.

With mysql_real_escape_string you won't have to. In any case you should only use addslashes in conjunction with get_magic_quotes_gpc() otherwise you end up adding slashes to slashes and get in a real mess.

[perplexed]

I am so going to find the time to totally re-read up on php. A combination of ignorance of changes and forgetfulness of the things i learnt first time around have caused the problems.

At least this thread will help the many others to come who progress on to the updated version of php from older ones.

Originally Posted by black-dog

Well yes, that's what the 'escape' part is all about.

LOL, i thought the 'real string' and 'escape' were solely to prevent malicious code being inserted.

You don't need a hidden field. In fact I'd actively encourage you to remove it. Just remove it from the insert query. If the field is auto-increment, it will still work

OK will do, again this is a legacy of the book i used many years ago which gave the empty field as necessary. I don't know but maybe even then it wasn't out and out crucial?. Often queries can be stripped down a fair bit and still work.

When performing a repetitive task I always think 'loops'. You could have saved a lot of effort by doing:

PHP Code:

foreach($_POST as $key=>$value){
$$key=mysql_real_escape_string($value);
} 


With mysql_real_escape_string you won't have to. In any case you should only use addslashes in conjunction with get_magic_quotes_gpc() otherwise you end up adding slashes to slashes and get in a real mess.

I was so aware of there being a simple function that would allow me to use a 'for each' type string. I'm pretty sure that i indeed do have one operating in other scripts across my website. But, i was just keen and eager to get the thing working.

Funnily enough, although it didn't affect the working of the interaction, i remember at one point in my struggles seeing in the reproduction of the MySQl query double and triple slashes in some of them. I'll go ahead and remove the stripslashes reference i've got installed and if it doesn't stop the script working, i'll remove them all.

One thing for sure i'm going to copy this whole thread for the instructions it holds in case it gets deleted. Many thanks.

[perplexed]

Uh oh! am i still totally unprotected against sql injection?

I had a few minutes spare so thought i'd begin my re-education of all things coding. I decided to Google the 'real escape string'.

As mentioned in the post above, i wrongly thought just adding that to the search form fields scripts was the security measure needed. I realise now it just escapes the commas in my form.

The connection part of my scripts consists of

PHP Code:

$conn=@mysql_connect("localhost", "user", "password") or die("could not connect");
$rs=@mysql_select_db("db_name", $conn) or die("could not connect to database"); 


Do i take it to prevent sql injection i should add the following either before or after it?????

PHP Code:

$user = mysql_real_escape_string($user);
$pwd = mysql_real_escape_string($pwd);

$sql = "SELECT * FROM users WHERE
user='" . $user . "' AND password='" . $pwd . "'" 


And as with all coding examples i see in books or on sites, where if at all, do i replace the user and pwd with the actual words that are the user name and password?

Apologies for the bald spots starting to appear where you are tearing your hair out at me lol.

[black-dog]

Originally Posted by perplexed

Do i take it to prevent sql injection i should add the following either before or after it?????

PHP Code:

$user = mysql_real_escape_string($user);
$pwd = mysql_real_escape_string($pwd);

$sql = "SELECT * FROM users WHERE
user='" . $user . "' AND password='" . $pwd . "'" 


And as with all coding examples i see in books or on sites, where if at all, do i replace the user and pwd with the actual words that are the user name and password?

Apologies for the bald spots starting to appear where you are tearing your hair out at me lol.

Basically, you don't trust anything that comes from outside into your script. This could be POST or GET variable or a COOKIE or SESSION variable. So if you are getting people to log in using a username and password then you will sanitise those before putting them in a query.

This is entirely seperate from your database connection script which should be hard coded, ansituated above the web root. I store it in a file and then include it. Something like this:

PHP Code:

include(../dbconnect.php"); 


[perplexed]

Thank you. I'm fairly sure what you mean by above the web root. I'll Google it anyway lol.

[black-dog]

Originally Posted by perplexed

Thank you. I'm fairly sure what you mean by above the web root. I'll Google it anyway lol.

Your web pages will be in a folder (such as public_html) This is the web root. Folders higher in the directory tree are not accessible via http

[Dan]

Behind the web root is perfect for scheduled task files (cron jobs) and include files, I use this alot