Teachnique
      CourseRoadmaps
      Login

      HomeIntroductionFeaturesVersionsVariablesInstallationAdministrationPHP SyntaxNode.js SyntaxJava SyntaxPython SyntaxConnectionWorkbench

      Create DatabaseDrop DatabaseSelect DatabaseShow DatabaseCopy DatabaseDatabase ExportDatabase ImportDatabase Info

      Create UsersDrop UsersShow UsersChange PasswordGrant PrivilegesShow PrivilegesRevoke PrivilegesLock User AccountUnlock User Account

      Create TablesShow TablesAlter TablesRename TablesClone TablesTruncate TablesTemporary TablesRepair TablesDescribe TablesAdd/Delete ColumnsShow ColumnsRename ColumnsTable LockingDrop TablesDerived Tables

      QueriesConstraintsInsert QuerySelect QueryUpdate QueryDelete QueryReplace QueryInsert IgnoreInsert on Duplicate Key UpdateInsert Into Select

      Create ViewsUpdate ViewsDrop ViewsRename Views

      IndexesCreate IndexDrop IndexShow IndexesUnique IndexClustered IndexNon-Clustered Index

      Where ClauseLimit ClauseDistinct ClauseOrder By ClauseGroup By ClauseHaving ClauseAND OperatorOR OperatorLike OperatorIN OperatorANY OperatorEXISTS OperatorNOT OperatorNOT EQUAL OperatorIS NULL OperatorIS NOT NULL OperatorBetween OperatorUNION OperatorUNION vs UNION ALLMINUS OperatorINTERSECT OperatorINTERVAL Operator

      Using JoinsInner JoinLeft JoinRight JoinCross JoinFull JoinSelf JoinDelete JoinUpdate JoinUnion vs Join

      Unique KeyPrimary KeyForeign KeyComposite KeyAlternate Key

      TriggersCreate TriggerShow TriggerDrop TriggerBefore Insert TriggerAfter Insert TriggerBefore Update TriggerAfter Update TriggerBefore Delete TriggerAfter Delete Trigger

      Data TypesVARCHARBOOLEANENUMDECIMALINTFLOATBITTINYINTBLOBSET

      Regular ExpressionsRLIKE OperatorNOT LIKE OperatorNOT REGEXP Operatorregexp_instr() Functionregexp_like() Functionregexp_replace() Functionregexp_substr() Function

      Fulltext SearchNatural Language Fulltext SearchBoolean Fulltext SearchQuery Expansion Fulltext Searchngram Fulltext Parser

      Date and Time FunctionsArithmetic OperatorsNumeric FunctionsString FunctionsAggregate Functions

      NULL ValuesTransactionsUsing SequencesHandling DuplicatesSQL InjectionSubQueryCommentsCheck ConstraintsStorage EnginesExport Table into CSV FileImport CSV File into DatabaseUUIDCommon Table ExpressionsOn Delete CascadeUpsertHorizontal PartitioningVertical PartitioningCursorStored FunctionsSignalResignalCharacter SetCollationWildcardsAliasROLLUPToday DateLiteralsStored ProcedureExplainJSONStandard DeviationFind Duplicate RecordsDelete Duplicate RecordsSelect Random RecordsShow ProcesslistChange Column TypeReset Auto-IncrementCoalesce() Function

      Useful FunctionsStatements ReferenceQuick GuideUseful ResourcesDiscussion

      Feedback

      Submit request if you have any questions.

      Course
      SQL Injection

      MySQL Tutorial

      This SQL tutorial is structured for beginners to guide them from the foundational concepts to advanced data manipulation and querying techniques in SQL. By the end of this tutorial, you will have developed a robust understanding of SQL, equipping you with the knowledge to efficiently manage and analyze data across various database systems. This guide sets the stage for your growth into a skilled data professional, ready to tackle complex data challenges and contribute to the field of data analysis and database management.

      SQL Injection

      The SQL Injection in MySQL is a harmful approach where an attacker inserts or "injects" harmful SQL code into a database query. This can be done through user inputs such as forms, URL parameters, or cookies. The attacker takes advantage of weaknesses in the software to steal information from the database.

      How SQL Injection Works

      Imagine you have a web application with a login page. When a user enters their username and password, the application checks these credentials against a MySQL database. The SQL query might look like as given below 
      SELECT * FROM users 
      WHERE username = 'user' AND password = 'password';
      In a secure application, the 'user' and 'password' would be the actual values entered by the user. However, in an SQL Injection attack, an attacker can manipulate the input fields to inject malicious SQL code.
      For example, they might enter the following as the username 
      ' OR '1' = '1
      Now, the SQL query becomes 
      SELECT * FROM users 
      WHERE username = '' OR '1' = '1' AND password = 'password';
      Because '1' always equals '1', this condition is always true, and the attacker gains unauthorized access to the application. In this way, they trick the application into granting access without a valid password.

      Preventing SQL Injection

      To prevent SQL injection, it is important to handle escape characters properly when using scripting languages like PERL and PHP. When working with PHP and MySQL, you can use the mysql_real_escape_string() function to escape input characters that have special meaning in MySQL. Following is an example of how to do this 
      if (get_magic_quotes_gpc()) {
         $name = stripslashes($name);
      }
      // escape input characters
      $name = mysql_real_escape_string($name);
      
      // Perform the MySQL query with the escaped 'name'
      mysqli_query("SELECT * FROM CUSTOMERS WHERE name='{$name}'");

      The LIKE Quandary

      Now, let us address the issue with the LIKE clause. When dealing with user-provided data that may include '%' and '_' characters, it is important to create a custom escaping mechanism to treat them as literals. You can achieve this by combining "mysql_real_escape_string()" function with "addcslashes()" function, which allows you to specify a character range to escape. Following is an example of how you can do it 
      // Escape and convert '%' and '_' in the user-provided string
      $sub = addcslashes(mysql_real_escape_string("%str"), "%_");
      
      // $sub will be equal to \%str\_
      
      // Use the escaped string in the LIKE query
      mysqli_query("SELECT * FROM messages 
         WHERE subject LIKE '{$sub}%'");
      In this way, you ensure that the '%' and '_' characters in the user input are treated as literal characters in the SQL query, preventing SQL injection and maintaining the integrity of your database operations.