One place for hosting & domains

      Examples

      Database Sharding: Concepts, Examples, and Strategies


      Many software applications use a relational database management system (RDBMS) to store data. As the database grows, it becomes more time-and-storage intensive to store the data. One popular solution to this problem is
      database sharding
      . A sharded database distributes the records in a database’s tables across different databases on different computer systems. This guide explains how database sharding works and discusses some of the advantages and disadvantages of sharding. It also describes some of the main sharding strategies and provides some database sharding examples.

      What is Database Sharding?

      As databases grow larger, they can be scaled in one of two ways. Vertical scaling involves upgrading the server hosting the database with more RAM, CPU ability, or disk space. This allows it to store more data and process a query more quickly and effectively. Horizontal scaling, which is also known as “scaling out”, adds additional servers to distribute the workload.

      Data sharding is a common way of implementing horizontal scaling. Database sharding divides the table records in a database into smaller portions. Each section is a shard, and is stored on a different server. The database can be divided into shards based on different methods. In a simple implementation, the individual tables can be assigned to different shards. More often, the rows in a single table are divided between the shards.

      Vertical partitioning and horizontal partitioning are two different methods of partitioning tables into shards. Vertical partitioning assigns different columns within a table to different servers, but this technique is not widely used. In most cases, horizontal partitioning/sharding is used to implement sharding, and the two terms are often used interchangeably. Horizontal sharding divides the rows within a table amongst the different shards and keeps the individual table rows intact.

      Note

      Vertical partitioning and horizontal partitioning should not be confused with vertical and horizontal scaling.

      The shards are distributed across the different servers in the cluster. Each shard has the same database schema and table definitions. This maintains consistency across the shards. Sharding allocates each row to a shard based on a sharding key. This key is typically an index or primary key from the table. A good example is a user ID column. However, it is possible to generate a sharding key from any field, or from multiple table columns. The selection of the sharding key should be reasonable for the application and effectively distribute the rows among the shards. For example, a country code or zip code is a good choice to distribute the data to geographically dispersed shards. Sharding is particularly advantageous for databases that store large amounts of data in relatively few tables, and have a high volume of reads and writes.

      Each shard can be accessed independently and does not necessarily require access to the other shards. Different tables can use different sharding techniques and not all tables necessarily have to be sharded. As an ideal, sharding strives towards a shared-nothing architecture, in which the shards do not share data and there is no data duplication. In practice, it is often advantageous to replicate certain data to each shard. This avoids the need to access multiple servers for a single query and can result in better performance.

      The following example demonstrates how horizontal sharding works in practice. Before the database is sharded, the example store table is organized in the following way:

      store_ID city state zip_code
      1001 Detroit MI 48201
      1350 Chicago IL 60601
      2101 Cleveland OH 44114
      2250 Pittsburgh PA 15222
      2455 Boston MA 02108
      2459 New York NY 10022

      After sharding, one shard has half the rows from the table.

      store_ID city state zip_code
      1001 Detroit MI 48201
      2101 Cleveland OH 44114
      2455 Boston MA 02108

      The second shard contains the remainder of the rows.

      store_ID city state zip_code
      1350 Chicago IL 60601
      2250 Pittsburgh PA 15222
      2459 New York NY 10022

      Sharding does not necessarily make any backup copies of the data. Each record is still only stored on a single server. Replication is used to copy information to another server, resulting in primary and secondary copies of the data. Replication enhances reliability and robustness at the cost of additional complexity and resources. Sharded databases can be replicated, but the procedure for doing so can be very complex.

      Replication and caching are both potential alternatives to sharding, particular in applications which mainly read data from a database. Replication spreads out the queries to multiple servers, while caching speeds up the requests. See our guide
      How to Configure Source-Replica Replication in MySQL
      to learn more about data replication.

      Pros and Cons of a Sharded Database

      Generally, a horizontal scaling approach is more robust and effective than vertical scaling. Vertical scaling is much easier to implement, because it mainly consists of hardware upgrades. It might be the correct approach to take with a medium-sized database that is slowly reaching its limit. However, it is impossible to scale any system indefinitely, and ongoing growth rapidly becomes unmanageable. The limits of vertical scaling usually lead administrators to seek another alternative.

      Horizontal scaling allows systems to achieve a much higher scaling rate. Additional servers can be added as required, permitting the database system to organically grow and access additional resources. It provides administrators with much more flexibility.

      Database sharding is a horizontal scaling strategy, so it shares the advantages of this approach. However, it also offers several additional benefits, including the following:

      • It improves performance and speeds up data retrieval. Based on the sharding key, the database system immediately knows which shard contains the data. It can quickly route the query to the right server. Because each shard only contains a subset of the rows, it is easier for the database server to find the correct entry.
      • Additional computing capacity can be added with no downtime. Sharding increases the data storage capacity and the total resources available to the database.
      • It can be more cost efficient to run multiple servers than one mega-server.
      • Sharding can simplify upgrades, allowing one server to be upgraded at a time.
      • A sharded approach is more resilient. If one of the servers is offline, the remaining shards are still accessible. Sharding can be combined with high availability techniques for even higher reliability.
      • Many modern database systems provide some tools to assist with sharding, although they do not completely automate the process.

      Unfortunately, sharding also has drawbacks. Some of the downsides include:

      • Sharding greatly increases the complexity of a software development project. Additional logic is required to shard the database and properly direct queries to the correct shard. This increases development time and cost. A more elaborate network mesh is often necessary, which leads to an increase in lab and infrastructure costs.
      • Latency can be higher than with a standard database design.
      • SQL join operations
        affecting multiple shards are more difficult to execute and take longer to complete. Some operations might become too slow to be feasible. However, the right design can facilitate better performance on common queries.
      • Sharding requires a lot of tuning and tweaking as the database grows. This sometimes requires a reconsideration of the entire sharding strategy and database design. Uneven shard distribution can happen even with proper planning, causing the distribution to unexpectedly become lopsided.
      • It is not always obvious how many shards and servers to use, or how to choose the sharding key. Poor sharding keys can adversely affect performance or data distribution. This causes some shards to be overloaded while others are almost empty, leading to hotspots and inefficiencies.
      • It is more challenging to change the database schema after sharding is implemented. It is also difficult to convert the database back to its pre-sharded state.
      • Shard failures can cause cross-shard inconsistencies and other failures.
      • Backup and replication tasks are more difficult with a sharded database.
      • Although most RDBMS applications provide some sharding support, the tools are often not robust or complete. Most systems still do not fully support automatic sharding.

      Database Sharding Strategies: Common Architectures

      Any sharding implementation must first decide on a db sharding strategy. Database designers must consider how many shards to use and how to distribute the data to the various servers. They must decide what queries to optimize, and how to handle joins and bulk data retrieval. A system in which the data frequently changes requires a different architecture than one that mainly handles read requests. Replication, reliability and a maintenance strategy are also important considerations.

      The choice of a sharding architecture is a critical decision, because it affects many of the other considerations. Most sharded databases have one of the following four architectures:

      • Range Sharding.
      • Hashed Sharding.
      • Directory-Based Sharding.
      • Geographic-Based Sharding.

      Range Sharding

      Range sharding examines the value of the sharding key and determines what range it falls into. Each range directly maps to a different shard. The sharding key should ideally be immutable. If the key changes, the shard must be recalculated and the record copied to the new shard. Otherwise, the mapping is destroyed and the location could be lost. Range sharding is also known as dynamic sharding.

      As an example, if the userID field is the sharding key, then records having IDs between 1 to 10000 could be stored in one shard. IDs between 10001 and 20000 map to a second shard, and those between 20001 and 30000 to a third.

      This approach is fairly easy to design and implement, and requires less programming time. The database application only has to compare the value of the sharding key to the predefined ranges using a lookup table. This scheme is also easier to redesign and maintain. Range sharding is a good choice if records with similar keys are frequently viewed together.

      Range sharding works best if there are a large number of possible values that are fairly evenly distributed across the entire range. This design works poorly if most of the key values map to the same shard. Unfortunately, this architecture is prone to poor distribution of rows among the shards. A good design can still lead to an unbalanced distribution. For example, older accounts are more likely to have been deleted over the years, leaving the corresponding shard relatively empty. This leads to inefficiencies in the database. Choosing fairly large ranges can reduce, but not eliminate, this possibility.

      The database sharding examples below demonstrate how range sharding might work using the data from the store database. In this case, the records for stores with store IDs under 2000 are placed in one shard. Stores possessing IDs of 2001 and greater go in the other.

      The first shard contains the following rows:

      store_ID city state zip_code
      1001 Detroit MI 48201
      1350 Chicago IL 60601

      The second shard has the following entries:

      store_ID city state zip_code
      2101 Cleveland OH 44114
      2250 Pittsburgh PA 15222
      2455 Boston MA 02108
      2459 New York NY 10022

      This results in a slightly imbalanced distribution of records. However, as new stores are added, they might be assigned larger store IDs. This leads to a greater imbalance as time goes on.

      To keep the database running efficiently, shards and ranges have to be regularly rebalanced. This might involve splitting the shards apart and reassigning the data, or merging several smaller shards. If the data is not regularly monitored, performance can steadily degrade.

      Hash Sharding (Key-Based)

      Hash-based sharding, also known as key-based or algorithmic sharding, also uses the shard key to determine which shard a record is assigned to. However, instead of mapping the key directly to a shard, it applies a hash function to the shard key. A hash function transforms one or more data points to a new value that lies within a fixed-size range. In this case, the size of the range is equal to the number of shards. The database uses the output from the hash function to allocate the record to a shard. This typically results in a more even distribution of the records to the different shards.

      This method allows multiple fields to be used as a compound shard key. This eliminates clumping and clustering, and is a better approach to use if several records can share the same key. Hash functions vary in complexity. A simple hash function calculates the remainder, or modulus, of the key divided by the number of shards. More complex hashing algorithms apply mathematically advanced equations to multiple inputs. However, it is important to use the same hash function on the same keys for each hashing operation. As with range sharding, the key value should be immutable. If it changes, the hash value must be recalculated and the database entry remapped.

      Hash sharding is more efficient than range sharding because a lookup table is not required. The hash is calculated in real time for each query. However, it is impossible to group related records together, and there is no logical connection between the records on a given shard. This requires most bulk queries to read records from multiple shards. Hash sharding is more advantageous for applications that read or write one record at a time.

      Hash sharding does not guarantee that the shards are destined to remain perfectly balanced. Patterns in the data still might lead to clustering, which can occur purely by chance. Hash sharding complicates the tasks of rebalancing and rebuilding the shards. To add more shards, it is usually necessary to re-merge all the data, recalculate the hashes, and reassign all the records.

      The following database sharding example demonstrates a simple hash sharing operation. It uses the simple hash function store_ID % 3 to assign the records in the store database to one of three shards. The first step is to calculate a hash result for each entry.

      Note

      The hash results are not actually stored inside the database. They are shown in the final column for clarity.

      store_ID city state zip_code hash result
      1001 Detroit MI 48201 2
      1350 Chicago IL 60601 0
      2101 Cleveland OH 44114 1
      2250 Pittsburgh PA 15222 0
      2455 Boston MA 02108 1
      2459 New York NY 10022 2

      Rows having a hash result of 0 map to the first shard.

      store_ID city state zip_code
      1350 Chicago IL 60601
      2250 Pittsburgh PA 15222

      Those that have a hash result of 1 are assigned to shard number two.

      store_ID city state zip_code
      2101 Cleveland OH 44114
      2459 New York NY 10022

      The remainder are stored in the third shard.

      store_ID city state zip_code
      1001 Detroit MI 48201
      2459 New York NY 10022

      In this case, although the data set is quite small, the hash function still distributes the entries evenly. This is not always the case with every database. However, as records are added, the distribution is likely to remain reasonably balanced.

      Directory-Based Sharding

      Directory-based sharding groups related items together on the same shard. This is also known as entity or relationship-based sharding. It typically uses the value contained in a certain field to decide what shard to use. Directory sharding is accomplished through the use of a static lookup table. The table contains a list of mappings between each possible value for the field and its designated shard. Each key can only map to one shard and must appear in the lookup table exactly once. However many keys can potentially be mapped to the same shard.

      As an example, the records in a table of customers can be mapped to shards based on the customer’s home state. The lookup table contains a list of all fifty states, which are the shard keys, and the shard it maps to. This allows for a system design where the records of all customers living in New England are stored on the first shard. Clients in the Mid-Atlantic are located on shard two. Clients residing in the Deep South are mapped to the third shard.

      Directory-based sharding provides a high level of control and flexibility in determining how the data is stored. When intelligently designed, it speeds up common table joins and the bulk retrieval of related data. This architecture is very helpful if the shard key can only be assigned a small number of possible values. Unfortunately, it is highly prone to clustering and imbalanced tables, and the overhead of accessing the lookup table degrades performance. However, the benefits of this architecture often outweighs its drawbacks.

      Directory-based sharding is a good choice for the stores database. The store entries can be distributed to the different shards based on their location. In this design, locations in New England and the mid-Atlantic are stored in the first shard, which serves as the North-East shard. Stores in the Midwest are written to the second shard.

      The first shard contains the entries displayed below.

      store_ID city state zip_code
      2250 Pittsburgh PA 15222
      2455 Boston MA 02108
      2459 New York NY 10022

      The second shard contains the remainder of the data.

      store_ID city state zip_code
      1001 Detroit MI 48201
      1350 Chicago IL 60601
      2101 Cleveland OH 44114

      Although these two shards are perfectly balanced, this is not the main goal of directory sharding. It instead seeks to generate useful and relevant shards of closely-related information, which this example also accomplishes.

      Geographic-Based Sharding

      Geographic-based sharding, or Geo-sharding, is a specific type of directory-based sharding. Data is divided amongst the shards based on the location of the entry, which relates to the location of the server hosting the shard. The sharding key is typically a city, state, region, country, or continent. This groups geographically similar data on the same shard. It works the same way directory-based sharding does.

      A good example of geo-sharding relates to geographically dispersed customer data. The customer’s home state is used as a sharding key. The lookup table maps customers living in states in the same sales region to the same shard. Each shard is located on a server located in the same region as the customer data it contains. This makes it very quick and efficient for a regional sales team to access customer data.

      Is Sharding Right For Your Business?

      Because sharding has both advantages and drawbacks, it is important to consider which type of database benefits the most from sharding. The first part of any sharding strategy is to decide whether to shard at all. To generalize, sharding makes the most sense for a high-volume database that stores a large amount of data in a few simple tables. Sharding is especially compelling if a company expects a large increase in the size of its database. Sharding is also useful for organizations that want to access or co-locate their data on a regional basis. For instance, a large social media company would want its users to access database servers in the same country or on the same continent. This requires the company to shard its data based on user location.

      In other cases, the complexity and difficulty associated with sharding are greater than the benefits. A database with many small to medium-sized tables could use vertical scaling, increasing the storage and computing power on a single server. It could also use alternative strategies such as replication for greater resilience and read-only throughput.

      Conclusion

      This guide answers the question, “What is database sharding?”. Sharding is a method of distributing the data in a database table to several different shards based on the value of a sharding key. Each shard is stored on a different server. Ideally, the records in a sharded database are distributed amongst the shards in an equitable manner. The different shards share the same table definitions and schemas, but each record is only stored on a single shard.

      Sharding allows a database to scale horizontally, taking advantage of the increased storage, memory, and processing power that only multiple servers can offer. It also increases resiliency and performance. Each query only has to search through a portion of the total records, which is much faster. As a drawback, sharding increases the complexity of a database and increases the difficulty of joins and schema changes.

      Sharding can be accomplished using range sharding, hash sharding, or directory-based sharding. Range sharding is the easiest method, but is more likely to result in unequal shards. Hash sharding more effectively distributes the records, but is more difficult to implement. Directory-based sharding groups related items together on the same shard.

      A sharded database can be implemented using multiple Linode servers. Linode allows you to configure a full web application on a powerful Linux operating system running the industry-standard LAMP stack. Choose from a high-performance
      Dedicated CPU
      service, or a flexible and affordable
      Shared CPU
      alternative. Similarly, you can also use
      Linode’s Managed Database service
      to deploy a database cluster without the need to install and maintain the database infrastructure.

      More Information

      You may wish to consult the following resources for additional information
      on this topic. While these are provided in the hope that they will be
      useful, please note that we cannot vouch for the accuracy or timeliness of
      externally hosted materials.



      Source link

      What is SQL Injection? Attack Examples & Prevention Tips


      Security is an important issue for all web applications and databases, especially those using the Structured Query Language (SQL). Although criminals most frequently focus on high-value targets, even small online applications can be victimized. When important information is stolen or an application is compromised, the financial, logistical, and reputation costs can be severe. Criminals and hackers frequently use a technique named SQL Injection (SQLi) to gain unauthorized entry to a remote database. This guide describes a SQL injection attack and explains how it is used. It also discusses how to detect SQLi vulnerabilities and how to defend against them.

      What is a SQL Injection Attack?

      A SQL injection attack is an incursion that alters SQL Queries with the objective of tampering with a SQL database. It is most often used to attack web applications, but can be used on other systems that host a database. This attack uses a code injection strategy to send malicious SQL queries to the database. Often, these commands are based on legitimate information from the website. SQLi attacks are usually launched to achieve the following:

      • View private or restricted information contained in a database, including sensitive personal or financial information.
      • Add, delete, or edit information stored in a database. This could include either application data or metadata including the schema or table definitions.
      • Gain administrative access to a database, possibly creating a back door for long-term future use.
      • Compromise the server by using the database as an access point.
      • Launch a denial-of-service attack or incapacitate the database’s underlying infrastructure.

      Some SQL injection attacks are designed to remain undetected for a long period of time. In this case, the objective is usually to maintain ongoing access and eavesdrop on the database in the future. In some other cases, the hackers want to immediately extract as much information as they can, such as credit card numbers. Their intention is to resell the information or use it for criminal purposes. While the intruders would prefer to go undetected, they do not expect to access the system again. Finally, other attackers only want to inflict damage and take the application offline. They have no need for secrecy.

      No matter the purpose of the attack, it can inflict tremendous consequences upon the victimized organization. A SQL injection attack can cause several of the following negative consequences:

      • Lead to the loss of corporate secrets, confidential information, and other sensitive data.
      • Expose sensitive customer information, including credit/financial information, personal details, or private correspondence.
      • Incur direct financial loss due to theft and claims for compensation from users or third parties.
      • Generate negative publicity and a public relations crisis.
      • Take a web application or other component of a site offline or render it inoperable.
      • Hurt customer confidence and make it difficult for the organization to attract new clients and retain existing ones.

      Any organization can be targeted, even personal websites and small forums. According to the
      Wikipedia SQL Injection page
      , the average web application is attacked around four times per month. New exploits are always being developed, and it is difficult to design a truly bulletproof site. However, many hackers target sites indiscriminately using brute force. A database that has been secured through a few basic techniques is much more secure and difficult to compromise.

      Note

      This guide is intended as an introduction to SQL injections and does not cover every possible type of attack.
      Web security
      is a very complex field, and many possible attacks demand careful consideration. You should consult with web security professionals before launching any application that stores private personal or financial information.

      What is a SQL Query?

      SQL is a simple domain-specific programming language used to communicate with a Relational DataBase Management System (RDBMS). Database developers use SQL commands to send queries from database clients to the RDBMS. These queries contain commands to insert, update, delete, or read data. Queries are also used to administer the database and update the schema, including table definitions.

      During normal operations, web applications incorporate user data into SQL queries and forward them to the RDBMS. For instance, a query might add a new forum user or retrieve information about a category of products. Unfortunately, bad actors can manipulate these queries and cause the application to behave in an unintended or insecure manner.

      Types of SQL Injection Attacks

      Most SQL injection attacks fall into one of three categories. They vary in how direct they are and how difficult they are to execute. The three main categories are:

      • Classic (In-Band)
      • Blind
      • Out-of-Band

      Classic SQL Injection

      The classic method attack, also known as an in-band attack, sends altered commands to the database using the regular communication channel. It uses information learned from the response to gain information about the structure or contents of the database. This type of attack is easy to execute and can quickly yield results. It requires less skill, imagination, and programming ability than the other attack types. Many attackers use these techniques in an automated fashion on random sites, trying to find poorly-designed web applications.

      For this injection attack, the user adds information to the URL or the fields on a web form in an attempt to trick the database. The assailant hopes the database might transmit sensitive information or provide clues about its internal structure. For example, they might try to trick the database into displaying not only the public entries, but every row in the table.

      There are several variations on this method. Error-based SQLi attacks are designed to get the database to transmit error messages that reveal information about its internal schema. Union-based attacks use the SQL UNION command to append an additional query to the command. This can cause a database to display extra data. The information gained through a SQL injection attack is frequently used to craft subsequent attacks. A classic attack often takes an iterative approach. The attacking queries are refined until the database is fully compromised.

      Classic SQL injection attacks are often more successful with older applications that are built with PHP or ASP. This is due to security gaps and the lack of more advanced programming tools.

      Blind SQL Injection

      This approach is often used when classic attack methods do not work. In a blind attack, the attacker sends a manipulated query to the database and analyzes the response. The attack is considered “blind” because the attacker does not receive any direct information from the server. The attacker can analyze details, such as how long it takes the server to reply, to learn more about the database.

      The two most common types of blind SQL injection attacks are the Boolean Attack and the Time-based Attack. In a Boolean attack, the attacker expects a different response if the query is True than if it is False. For example, the results might get updated if the query is valid, but stay the same otherwise. The attacker might also be able to deduce some information based on whether they receive an error page or not.

      A time-based attack extracts information from the database based on how long it takes the server to respond. The attacker can selectively add delays to the query and calculate the response time. They can also construct commands that take longer to process in some situations compared to others. For example, a time-based attack might initiate a complex calculation for each column in a table. Tables that have more columns take longer to process the query. However, if the processing time exceeds the connection timeout value, the response becomes useless.

      Although this type of attack takes more time, forethought, and consideration, it can eventually uncover plenty of information about the database. Therefore, it can be as damaging as a classic attack, even though it is less common.

      Out-of-Band Injection

      Out-of-band attacks are the most complicated and the most difficult to construct. They are less common than the other two types. They do not rely on the behavior of the database. Instead, they receive information through a different channel other than the original web application. For example, they can trigger the database to transmit DNS or HTTP requests to a server under the attacker’s control. This is often referred to as a compounded SQL attack.

      Some out-of-band attacks might only work if certain features are enabled on the database. For example, the UTL_HTTP package must be configured on an Oracle database before it can forward any HTTP requests.

      SQL Injection Attack Examples

      Many SQL injection attacks take advantage of SQL keywords and syntax. The object is to use valid queries to get the database to operate in an undesirable manner. The particular details of these dangerous commands vary between the various RDBMS applications. However, most attacks use a few basic methods. The following SQL injection examples demonstrate some commonly used approaches.

      Note

      Although the core SQL syntax is standardized, the implementation varies between RDBMS applications. The different database applications also offer unique enhancements and features. These might be more or less secure. Some of the following attacks might work on some databases and not others. Consult the database documentation for more details.

      In SQL, the -- symbol means the rest of the command is a comment. If a user adds a comment indicator to a field it might be incorporated into a dynamic command. This could cause other fields to be ignored.

      In the following SQL injection example, a web form might have a field for the username and another for the user password. The backend of the application validates the login using the following command:

      SELECT * FROM forumusers WHERE username="username" AND password = 'password'
      

      If an unprotected dynamic query, a hostile agent could enter the name of another user followed by the sequence '--. The quotation mark closes the field while the -- characters convert the rest of the command into a comment. As a result, the web application sends the following command to the database.

      SELECT * FROM forumusers WHERE username="otheruser"--' AND password = 'password'
      

      When the comment is stripped out, the command evaluates to the following.

      SELECT * FROM forumusers WHERE username="otheruser"
      

      If no other validation or safeguards are in place, the application might permit the attacker to log in as the other user.

      Using the UNION Command

      The UNION command is very powerful. It retrieves the intersection of two distinct queries. This can be used to extract additional results from the database, combining an “innocent” query with one requesting sensitive information.

      As an example, the original command might be constructed in the following manner.

      SELECT name, price, description FROM products where category ='categoryname'
      

      The attacker might then add the phrase 'UNION ALL SELECT username, password FROM forumusers -- to the end of a product name. This results in the following query.

      SELECT name, price, description FROM products where category ='categoryname' UNION ALL SELECT username, password FROM forumusers
      

      This might result in the login details of all the forum users being dumped onto the attacker’s screen along with the product information.

      Using Stacked Queries

      In SQL, the ; symbol is used to separate two queries, which are executed together in the same transaction. This is known as a stacked query. This is often a useful feature, but it can cause problems for web applications. If an attacker adds a ' character to terminate the original field, they can then add ;, followed by a malevolent command.

      For example, the application logic constructs the following command from a user-specified category.

      SELECT name, price, description FROM products where category ='categoryname'
      

      Then the user might end their product selection with '; DROP TABLE forumusers --. This causes the command to execute the following commands:

      SELECT name, price, description FROM products where category ='categoryname' ; DROP TABLE forumusers
      

      If not detected elsewhere in the application, this command would delete all the user accounts, rendering the forum nearly worthless.

      Using the OR Keyword

      Attackers can also use the SQL OR keyword to extract additional information. The phrase +OR+1=1 always evaluates to True, so an attacker can use it to access the entire table. It could also be used on the forum login page, which ordinarily generates the following query.

      SELECT * FROM forumusers WHERE username="username" AND password = 'password'
      

      The phrase +OR+1=1 could be inserted, radically altering the command.

      SELECT * FROM forumusers WHERE username="username" OR 1=1 --' AND password = 'password'
      

      The expression 1=1 evaluates to True. So does username="username" OR True. This results in an unqualified SELECT * statement without any conditionals, which displays the login information for every user.

      SELECT * FROM forumusers
      

      Other Techniques

      Attackers typically iterate through several techniques until they find something that works. They can potentially use a long list of keywords along with numerical and string manipulators. For instance, they can use the SQL CONCAT keyword. They can also use the CHAR keyword to transmit individual characters as their hexadecimal equivalents. This could bypass validation techniques that are scanning for certain invalid characters. Some commands provide information about the database and its schema, although they differ between the various RDBMS applications.

      Several websites provide detailed “cheat sheets” about the most common attacks. One example is
      Netsparker’s SQL Injection Cheat Sheet
      . It compares and contrasts the various RDBMS systems, so it can be used as a MySQL injection cheat sheet, for example. The Open Web Application Security Project (OWASP) also provides a very detailed and useful
      SQL Injection Prevention Cheat Sheet
      .

      How to Detect a SQL Injection Vulnerability

      To ensure a web application is not vulnerable to common web attacks, consider security issues at every stage of the development process.

      • During the design specification process, document how to handle security threats.
      • At the implementation stage, build common classes or functions to sanitize input and detect suspicious data. Every client should call these routines to ensure every case is covered.
      • Develop a strategy for input validation, also known as sanitization, to detect malicious input. All user-provided data should be verified to ensure it is legitimate. At the same time, valid input must still be allowed. See the section on
        Preventing a SQL Injection Attack
        for more information.
      • Use established quality assurance techniques and tools to ensure common SQLi attacks are blocked. Build automated test and regression scripts to validate fixes and ensure security holes are not introduced.
      • Stay informed about new security issues and emerging threats. Keep the web server and RDBMS updated to the most recent release using the latest security updates.

      Applications handling financial information are at an even higher risk of attack, and should consider some additional measures. This increases the development and operational costs but provides an extra level of protection.

      • Consider hiring a security firm or consultant. An expert can review the design documents beforehand and run a security audit on the final product. Some firms provide commercial web vulnerability scanners. These scanners can be run anytime during the development process.
      • Employ a web application firewall (WAF) to detect threats. These applications continually update their list of attack signatures and filter input coming IP addresses with bad reputations. Because these applications are continually updated, they provide a reliable level of ongoing security.

      The OWASP has a good
      security code review guide
      which covers SQL injection attacks along with other web security issues.

      Preventing a SQL Injection Attack

      Several basic coding principles can greatly enhance database security. Most attackers are hoping to find easy targets. If their standard playbook does not work, they are likely to move on to another site. Many of the most obvious safeguards can be used together for increased effectiveness. To reduce the chances of a SQL injection attack, follow the steps below.

      • Use parameterized queries: This technique uses prepared SQL statements to construct the query beforehand. Variables initially take the place of the actual parameters. The actual user-supplied values replace the placeholders later on. This draws a distinction between code and data, and renders many attack techniques much less useful. For example, an attacker cannot comment out the remainder of the query using the -- sequence. The double dash would be included as part of the username field. The database would attempt to locate a user field ending with '-- and would not find it.
      • Validate all data: Before accepting any data, verify it is actually valid. This includes rejecting any input using certain characters or certain keywords. Table and column names can be mapped to their actual internal names, which should not be exposed to the customers. Choice control can be used to limit certain selections. For example, a form’s design can force a user to select their birth year from a drop-down list. This means any input in this field is guaranteed to be valid.
      • Use stored procedures: This is an alternative to parameterized queries with the same goal. Stored procedures are saved inside the database, allowing the application to use them at any time. Typically, the procedures automatically parameterize the code. As an added precaution, only a user who has execute privileges can run these procedures. Unfortunately, there might be cases where this technique is not completely foolproof. Consult the user documentation for the RDBMS for more information.
      • Use non-standard names for tables and columns: Many attackers look for standard tables such as customers or fields including username and password. Adding a prefix or suffix to each string or column provides additional protection at the cost of a bit of extra complexity and longer strings for each name.
      • Escape the input fields: This technique is not considered effective on its own, but provides another layer of protection as part of a total security strategy. Every RDBMS has a method of escaping user-supplied data. This involves recalculating the input so it is treated as pure text, rather than keywords or application-specific symbols. Some applications convert the input characters into their hex equivalents. The PHP programming language, which is often used in conjunction with SQL, also provides tools for escaping SQL queries.
      • Restrict the access privileges of the database user: Determine the level of access every account requires and configure the user roles accordingly. This limits the damage any individual user can inflict. A similar optimization is to limit the system privileges of the database owner. Even if a user gains access to an administrative account, they cannot use it to gain further access to the server. SQL views can also be used to further limit access. Our guide
        SQL Database Security: User Management
        discusses how to develop an access management strategy for an RDBMS.

      Conclusion

      A SQL injection attack is a type of security threat where attackers manipulate the data in web forms or in URLs. The main purpose of this attack is to get the database to behave in an undesirable or insecure manner. This might result in the database displaying confidential data or allowing an unauthorized user to modify, add, or delete data. An injection attack can cause a severe loss of reputation and operational or logistical consequences for the victimized business.

      The three main types of SQL injection attacks are classic, blind, and out-of-band. The classic method is the most common. The attacker directly assaults the database, submitting malevolent data as part of a query. For example, adding the -- sequence causes many RDBMS applications to treat the rest of the command as a comment. This might cause important parts of the query to be dropped and allow the attacker to log in as an administrator or another user.

      Database operators can protect themselves by considering security at every stage of the development process, hiring a security auditor, or deploying a web application firewall. Several coding defenses including parameterized queries, input validation, and stored procedures can thwart most common attacks. If you are considering deploying a web application that uses a database, consult OWASP’s
      cheat sheet
      and
      security code review guide
      .

      More Information

      You may wish to consult the following resources for additional information
      on this topic. While these are provided in the hope that they will be
      useful, please note that we cannot vouch for the accuracy or timeliness of
      externally hosted materials.



      Source link

      7 Great Web Accessibility Examples to Inspire You


      Here at DreamHost, we believe everyone should be able to use any website on the internet, regardless of any disability they may have. However, while we care about web accessibility, we also understand that designing a website that’s both accessible and visually attractive can be challenging.

      The good news is that accessible websites don’t have to be ugly. On the contrary, some stunning websites out there are designed with accessibility in mind — which we could all learn a thing or two from.

      In this post, we’ll start by showing you what strong web accessibility looks like. Then we’ll show you seven of the best web accessibility examples on the internet and see what we can learn from them. Let’s get started!

      Create a Website for All

      We make sure your website is fast and secure so you can focus on the important stuff.

      What Great Web Accessibility Looks Like

      According to The World Bank, over 15% of the global population has some form of disability. These can include:

      • Visual impairments: Some users have visual impairments that inhibit their ability to see clearly or perceive color contrasts
      • Hearing impairments: This includes deafness and partial hearing loss.
      • Physical disabilities: Some people have mobility impairments that can impact their dexterity and ability to make precise movements, possibly making using a mouse difficult.
      • Cognitive disabilities: Conditions like dyslexia and dementia can affect a person’s cognitive abilities.

      It’s important to keep all of these different challenges at the forefront of your mind when creating your website to ensure there are no barriers to disabled users. To help web designers with this, W3C has developed a set of Web Content Accessibility Guidelines (WCAG).

      Solid web accessibility means adhering to these guidelines and carefully following the four guiding principles of web content accessibility. These guiding principles state that all websites should be:

      1. Perceivable
      2. Operable
      3. Understandable
      4. Robust

      Ensuring that your website is “operable” might mean implementing keyboard-friendly navigation for people who cannot use a mouse. “Perceivable” might mean making sure to use high-contrast colors for people with visual impairments.

      We’ve already outlined 10 practical ways to implement the web accessibility guidelines and make your website more accessible (including advice on accessibility testing and UI components). Now we’re going to look at some examples of websites that are already doing it right.

      7 Great Web Accessibility Examples to Inspire You

      Below, we’ve listed some of our favorite web accessibility examples. These seven websites set the bar when it comes to accessibility.

      1. Scope

      The Scope home page.

      Scope is a disability equality charity based in England and Wales dedicated to creating a fairer, more equal society. As a champion of disability equality, you’d expect that this organization’s website would be as accessible as possible — and it is.

      Not only does it fully adhere to WCAG 2.0 and WCAG 2.1 guidelines, but the site is even customizable for individual users. For example, users can change the site’s colors, increase the text size, or even turn on text narration to have the content read aloud.

      If you look at the top-left section of the home page, you’ll see an Accessibility tab. Click on this, and the site will bring you to its accessibility page, which includes instructions on how to adapt the experience to your needs, links to assistive technologies, and a list of known accessibility issues that are being worked on.

      Scope uses short sentences and large, clean fonts throughout the site for maximum readability. Plus, the site is fully compatible with screen reader software.

      Despite already being a fantastic example of website accessibility, the team at Scope continues to make improvements. Every three months, they test the website for accessibility and make updates where necessary.

      2. Paralympic.org

      The IPC home page.

      Paralympic.org is the official website of the International Paralympic Committee (IPC). The IPC is a powerful advocate of social inclusion, and its website is a testament to that.

      It features keyboard-friendly tab navigation and an instant “scroll-to-top” button to make it easy to move around the page. Images and videos are large and highly visible, and there’s plenty of white space to make visual elements stand out.

      If you go to the home page, you’ll notice a text size adjuster in the top-right corner of the screen. This is easily visible and allows users with visual impairments to quickly customize the size of the text to meet their needs.

      3. KidzWish

      The KidzWish home page.

      KidzWish is an organization that provides therapy, support services, and an annual Christmas party for children who are disadvantaged or have a disability. It caters to many people with different disabilities, so naturally, it needed to build a website that was as accessible as possible.

      It definitely achieved that goal. The KidzWish website is wonderfully designed, with a logical structure, keyboard-friendly navigation, high-contrast colors, and large text. Plus, it’s easy to navigate with prominent, clickable elements.

      The design is also very child-friendly. It boasts a bright, bold color scheme and tons of fun graphics.

      4. SSE Energy

      The SSE Energy home page.

      SSE Energy is a UK-based energy company. Its website features information about tariffs and bundles and includes a main login portal for its customers to service their accounts.

      The company has done a wonderful job of making the site accessible to all by using large readable text and a clear interface. It also incorporates keyboard navigation to make it easy to get around the site.

      The designers went above and beyond to ensure that the site is accessible to visually- and hearing-impaired users. There are SignVideo services for British Sign Language users, and the color contrast meets WCAG guidelines.

      Customers can also request bills in Braille and larger formats. In addition to all of this, the site is compatible with assistive technology.

      5. BBC iPlayer

      The BBC iPlayer home page.

      BBC iPlayer is the BBC’s online streaming service. Its website is where users go to watch programs online. It’s also another fantastic web accessibility example that we can all learn from.

      First, the website is both very easy to navigate and compatible with assistive technology. You can move around the page by clicking on the Tab button. Navigating over the iPlayer logo brings up an option for Accessibility help, which links to a resource page with a lot of useful information for users with disabilities.

      The content is logically laid out, and all buttons use a clear visual design with high contrast colors. There are also keyboard and mouse-accessible tooltips that provide extra information for users and descriptive alt text for all images.

      The video content is also accessible. All shows on BBC iPlayer feature subtitles. There are also audio-described and signed content categories.

      6. NSW Government

      The NSW Government home page.

      The NSW Government website is the government hub for the New South Wales area of Australia. It’s perfectly designed to make it easy for residents of all backgrounds and abilities to use.

      This site features tab navigation, making it simple to navigate pages using a keyboard or screen reader. Thanks to large fonts and contrasting colors, it’s also extremely readable and is compatible with assistive technology.

      7. GOV.UK

      The GOV.UK home page.

      GOV.UK is the central hub for all U.K. government web pages. It can be used to access everything from information about benefits and disability aid to visa and immigration support.

      The U.K. Government has done an amazing job of making its site accessible for everyone who needs it. The site features keyboard navigation and ARIA attributes, making it easy to find pages and navigate the site. It also is adapted to support 300% zoom for visually impaired users.

      DreamHost Takes Inclusivity Seriously

      We regularly report on diversity, accessibility, and representation in the tech industry. Subscribe to our monthly newsletter so you never miss an article.

      Make an Accessibility Statement

      Making sure your website is as accessible as possible is both a moral and a professional obligation. It might seem like a challenge, but it’s worth it. You can simply follow in the footsteps of the web accessibility examples above to create an inclusive website that all users can enjoy.

      Ready to build your accessible website? Let us take care of the technical side for you, so you can devote more of your time and energy to what matters: the design. Sign up for our Shared Unlimited Hosting Plan and get unlimited, secure hosting for all of your websites!



      Source link