1

i am trying to anonymize some student form our moodle.i am using mysql skript. and I get the error . Can someone kindly help me resolve this issue.

ANZ=`mysql -u $USER -p$PASSWD -h$HOST -D $DATABASE -e "SELECT count(id) FROM $TABLE WHERE deleted=1 and not lastname='anonym';" --skip-column-names -B;`;

if [ $((ANZ)) -gt 0 ] then mysql -u $USER -p$PASSWD -h$HOST -D $DATABASE -e "SELECT id, username FROM $TABLE WHERE deleted=1 and not lastname='anonym' and username like '%@%';" --skip-column-names -B > $FILE; cat $FILE | while read TMP

do

    UserID=${TMP%   *};
echo $UserID >> $RES_FILE;
echo "User $UserID wird anonymisiert...";

mysql -u $USER -p$PASSWD -h$HOST -D $DATABASE -e "UPDATE $TABLE SET username='$A$UserID', email='a@nonym.de', firstname='$A', lastname='$A', icq='', skype='', yahoo='', aim='', msn='', phone1='', phone2='', institution='', department='', address='', city='', country='', url='', WHERE AND deleted =1 AND id=$UserID;";

    `mysql -u $USER -p$PASSWD -h$HOST -D $DATABASE -e "UPDATE mdl_message SET subject='Neue Mitteilung von anonym' WHERE useridfrom=$UserID;"`;
    `mysql -u $USER -p$PASSWD -h$HOST -D $DATABASE -e "UPDATE mdl_message_read SET subject='Neue Mitteilung von anonym' WHERE useridfrom=$UserID;"`;

    VNAME=`mysql -N -u $USER -p$PASSWD -h$HOST -D $DATABASE -e "SELECT firstname FROM $TABLE WHERE id=$UserID;"`;
    NNAME=`mysql -N -u $USER -p$PASSWD -h$HOST -D $DATABASE -e "SELECT lastname FROM $TABLE WHERE id=$UserID;"`;

     `mysql -u $USER -p$PASSWD -h$HOST -D $DATABASE -e "UPDATE mdl_event SET name='Meeting with your Student, anonym' WHERE name LIKE 'Meeting with your Student,%$VNAME $NNAME%';"`;
done
echo "Fertig!";

i have got this errors: ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'AND deleted =1 AND id=715' at line 1

ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'AND deleted =1 AND id=3478' at line 1.

please help me.i am new in SQL.

many thanks for your help.

Jonnix
  • 4,121
  • 1
  • 30
  • 31
24al
  • 59
  • 3
  • 8
  • remove the comma after `url=''`. Also an `AND` whithout and Argument on left side makes no sence – Jens Jun 13 '16 at 09:24
  • many thanks for your consideration .it helps. – 24al Jun 13 '16 at 09:35
  • Possible duplicate of [How can I fix MySQL error #1064?](http://stackoverflow.com/questions/23515347/how-can-i-fix-mysql-error-1064) – psiyumm Jun 13 '16 at 09:39

1 Answers1

1

You have

WHERE AND deleted =1 AND id=$UserID;

It should be:

WHERE deleted =1 AND id=$UserID;

Without AND between WHERE and deleted

nospor
  • 4,190
  • 1
  • 16
  • 25
  • many thanks for your consideration .i change it but unfortunatly it doesn´t help. – 24al Jun 13 '16 at 09:32
  • Hi,yes.i got again ERROR.i put this line: 'mysql -u$USER -p$PASSWD -h$HOST -D $DATABASE -e "UPDATE $TABLE SET username="'$A$UserID'", email='a@nonym.de', firstname='$A', lastname='$A', icq='', skype='', yahoo='', aim='', msn='', phone1='', phone2='', institution='', department='', address='', city='', country='', url='' WHERE deleted=1 AND id='$UserID';"'; and i got this ERROR:ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'demo@XYZ.de.1456411291' at line 1 – 24al Jun 16 '16 at 10:50
  • This is totally different case and I am pretty sure you ask about that in different topic. By quick view not `username="'$A$UserID'",` but `username='$A$UserID',` – nospor Jun 16 '16 at 10:54
  • i changed username="'$A$UserID'" to username='$A$UserID' but it does not help. – 24al Jun 16 '16 at 10:59
  • Which variable gives text `demo@XYZ.de.1456411291` ? – nospor Jun 16 '16 at 11:01
  • this one:ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'axel.focke@uni.de.1456411265' at line 1 – 24al Jun 16 '16 at 11:06
  • I am asking about **variable** – nospor Jun 16 '16 at 11:10
  • you mean this one:temp: 715 axel.focke@uni.de.1456411265 User 715 axel.focke@uni.de.1456411265 wird anonymisiert... – 24al Jun 16 '16 at 11:36
  • I mean **variable**. In your code you have few variables like `$TABLE` or `$UserID` and more. I am asking which variable keeps text `demo@XYZ.de.1456411291` – nospor Jun 16 '16 at 11:38
  • i am so sorry for bothering you.one of our co-worker has written skript and he ist not more here.i put all the code above and unfortunatly i don't able to find your question because as you see there is no variable for email='a@nonym.de'.please help me again. – 24al Jun 16 '16 at 11:49
  • I didn't say that this variable is for EMAIL. That's why I asked you to check every variable. Can you do that or can you know how to do that? – nospor Jun 16 '16 at 11:51
  • please tell me how can i do that.i don't know. – 24al Jun 16 '16 at 11:53
  • IT seems you run this command in linux console? If yes, then just do `echo $TABLE;` and do on each variable – nospor Jun 16 '16 at 11:55
  • i wrote echo $UserID; 3478 logtest1@uni.de.1441958493 yes i am using Centos. – 24al Jun 16 '16 at 12:04
  • Ok, lets try it in different way. echo whole query: `echo "UPDATE $TABLE SET username="'$A$UserID'", email='a@nonym.de', firstname='$A', lastname='$A', icq='', skype='', yahoo='', aim='', msn='', phone1='', phone2='', institution='', department='', address='', city='', country='', url='' WHERE deleted=1 AND id='$UserID';"'` – nospor Jun 16 '16 at 12:10
  • i did it Line 67: End of file while looking for `'' achieved. Line 71: Syntax error: Unexpected end of file. – 24al Jun 16 '16 at 12:17
  • what file? I must do it in linux console not in file – nospor Jun 16 '16 at 12:18
  • Maybe you should add some comma at the end ; – nospor Jun 16 '16 at 12:28
  • i do it ............deleted=1 AND id='$UserID';"', i put comma end of Line but the same error. – 24al Jun 16 '16 at 12:34
  • It is hard to help while I do not know how you run all of this. – nospor Jun 16 '16 at 13:02
  • i put code in skript.sh and i run it in linux.i wrote also in code DATABASE="XXXXXXX"; DBUSER="XXXXXXX"; DBHOST="localhost"; TABLE="mdl_user"; – 24al Jun 17 '16 at 07:09
  • Here in comments it is hard to see right code. Please go here: http://nospor.pl/kontakt.html - this is my contact form - and put there content of your .sh file and send to me. I will check it in normal editor and send you back what I want to run to check errors. Maybe it will help :) – nospor Jun 17 '16 at 09:08
  • I've sent you mail. Did you receive it? – nospor Jun 17 '16 at 10:13
  • For next generations I am tell you now what was the problem: UserId contained text like this '611 some@email.here' but there should be only ID and that generated other problems – nospor Jun 20 '16 at 11:08